SAVEPOINT v11
Name
SAVEPOINT -- define a new savepoint within the current transaction.
Synopsis
SAVEPOINT <savepoint_name>
Description
SAVEPOINT establishes a new savepoint within the current transaction.
A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.
Parameters
savepoint_name
The name to be given to the savepoint.
Notes
Use ROLLBACK TO SAVEPOINT to roll back to a savepoint.
Savepoints can only be established when inside a transaction block. There can be multiple savepoints defined within a transaction.
When another savepoint is established with the same name as a previous savepoint, the old savepoint is kept, though only the more recent one will be used when rolling back.
SAVEPOINT is not supported within SPL programs.
Examples
To establish a savepoint and later undo the effects of all commands executed after it was established:
\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 above transaction will commit a row into the dept table, but the inserts into the emp and jobhist tables are rolled back.
See Also