Use the SAVEPOINT statement to set a savepoint within a transaction.
This statement can be imbedded in an application program (including a procedure) or issued interactively. It is an executable statement that can be dynamically prepared.
None required.
>>-SAVEPOINT--savepoint-name--+--------+------------------------> '-UNIQUE-' .-ON ROLLBACK RETAIN LOCKS-. >--ON ROLLBACK RETAIN CURSORS--+--------------------------+----><
CREATE TABLE DEPARTMENT (
DEPTNO CHAR(6),
DEPTNAME VARCHAR(20),
MGRNO INTEGER)
INSERT INTO DEPARTMENT VALUES ('A20', 'MARKETING', 301)
SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS
INSERT INTO DEPARTMENT VALUES ('B30', 'FINANCE', 520)
SAVEPOINT SAVEPOINT2 ON ROLLBACK RETAIN CURSORS
INSERT INTO DEPARTMENT VALUES ('C40', 'IT SUPPORT', 430)
SAVEPOINT SAVEPOINT3 ON ROLLBACK RETAIN CURSORS
INSERT INTO DEPARTMENT VALUES ('R50', 'RESEARCH', 150)
At
this point, the DEPARTMENT table exists with rows A20, B30, C40, and
R50. If you now issue: ROLLBACK TO SAVEPOINT SAVEPOINT3
row
R50 is no longer in the DEPARTMENT table. If you then issue: ROLLBACK TO SAVEPOINT SAVEPOINT1
the
DEPARTMENT table still exists, but the rows inserted since SAVEPOINT1
was established (B30 and C40) are no longer in the table.