Defines a new savepoint in the current transaction.
Syntax
SAVEPOINT savepoint_nameDescription
The SAVEPOINT command creates a new savepoint in the current transaction.
A savepoint is a special mark in a transaction. It allows all commands that are executed after it is created to be rolled back. If the commands are rolled back, the transaction state is restored to what it was at the time of the savepoint.
Parameters
| Parameter | Description |
|---|
| Parameter | Description |
|---|---|
| savepoint_name | The name that you want to specify for the savepoint. |
Description
You can run the ROLLBACK TO SAVEPOINT command to roll back to a savepoint.
Savepoints can be created only in a transaction block. You can define multiple savepoints in a transaction.
If another savepoint with the same name as a previous savepoint is created, the previous savepoint is retained. However, only the more recent savepoint is used during a rollback.
The SAVEPOINT command is not supported within SPL programs.
Examples
The following example shows how to create a savepoint and then undo all commands that are executed after the savepoint is created.
\set AUTOCOMMIT off
INSERT INTO dept VALUES (50, 'HR', 'NEW YORK');
SAVEPOINT depts;
INSERT INTO emp (empno, ename, deptno) VALUES (9001, 'JONES', 50);
INSERT INTO emp (empno, ename, deptno) VALUES (9002, 'ALICE', 50);
SAVEPOINT emps;
INSERT INTO jobhist VALUES (9001,'17-SEP-07',NULL,'CLERK',800,NULL,50,'New Hire');
INSERT INTO jobhist VALUES (9002,'20-SEP-07',NULL,'CLERK',700,NULL,50,'New Hire');
ROLLBACK TO depts;
COMMIT;The preceding transaction submits a row to the dept table, but the contents inserted into the emp and joblist tables are rolled back.
