Savepoints on Multi-statement Transactions
In a multi-statement transaction, use ROLLBACK together with the SAVEPOINT statement to perform a partial transaction rollback.
The SAVEPOINT statement establishes a marker in the transaction. If a rollback is subsequently issued, specify that the rollback only go back to the savepoint. All changes made prior to the savepoint are left in place; those made after the savepoint are undone. SAVEPOINT does not commit changes or release any locks; it simply establishes stopping points for use in partial rollbacks.
For example:
...
INSERT INTO emp_table VALUES (ename, edept);
UPDATE.....
SAVEPOINT first;
INSERT.....
DELETE.....
if error on delete
ROLLBACK TO first;
else if other errors
ROLLBACK;
...
COMMIT;
If an error occurs on the DELETE statement, the ROLLBACK TO FIRST statement directs the DBMS to back out all changes made after the savepoint was first created, in this case, only the changes made by the second INSERT statement. Processing resumes with the first statement that follows the ROLLBACK TO FIRST statement; the transaction is not terminated.
If an error occurs that makes it necessary to abort the entire transaction, the ROLLBACK statement that does not specify a savepoint causes the DBMS to back out the entire transaction. Depending on the design of the application, the program can either restart the transaction or continue with the next transaction.
An unlimited number of savepoints can be placed in a transaction. Rollback to the same savepoint is allowed any number of times within a transaction.
For a complete description of these statements, see the chapter "SQL Statements.”