SAVEPOINT v15

Name

SAVEPOINT— Define a new savepoint in the current transaction.

Synopsis

SAVEPOINT <savepoint_name>

Description

SAVEPOINT establishes a new savepoint in the current transaction.

A savepoint is a mark inside a transaction that allows all commands that are executed after it to be rolled back. This restores the transaction state to what it was at the savepoint.

Parameters

savepoint_name

The name ofo the savepoint.

Notes

Use ROLLBACK TO SAVEPOINT to roll back to a savepoint.

You can establish savepoints only when inside a transaction block. You can define multiple savepoints in a transaction.

When another savepoint is established with the same name as a previous savepoint, the old savepoint is kept. However, only the more recent one is used when rolling back.

SAVEPOINT isn't supported in SPL programs.

Examples

Establish a savepoint and then undo the effects of all commands executed after it:

\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;

This transaction commits a row into the dept table. The inserts into the emp and jobhist tables are rolled back.