Simulating Rollback to SAVEPOINT Behavior in the Database Trigger

Simulating Rollback to SAVEPOINT Behavior in the Database Trigger

Submitted by:

Zlatko Sirotic
Istra informaticki inzenjering d.o.o. Pula
Croatia

Sometimes we need a transaction to be successful even if some parts of it fail.

In that case, we usually use SAVEPOINT / ROLLBACK TO SAVEPOINT statements.

But we can't use SAVEPOINT / ROLLBACK TO SAVEPOINT statements in the database trigger because of the following errors:

ORA-04092: cannot SET SAVEPOINT in a trigger

ORA-04092: cannot ROLLBACK in a trigger

It is possible to simulate SAVEPOINT / ROLLBACK TO SAVEPOINT statements in the database trigger. We'll demonstrate it using a simple example.

First, suppose we need a third part transaction:

· 1 row of DEPT insertion

· 2 rows of EMP insertion, with job = MANAGER and which go to previously inserted DEPT

· 2 rows of EMP insertion, with job = PROGRAMER and which go to previously inserted DEPT

(Note the misspelling of PROGRAMER instead of PROGRAMMER because the job column has only 9 characters.)

Then suppose we need a successful transaction even if third part fails, but only canceling what the third part did (only one EMP insertion). Therefore the transaction is only correct when at the end of the transaction we have:

a) 1 row of DEPT, 2 rows of EMP = MANAGER, 2 rows of EMP = PROGRAMER

b) 1 row of DEPT, 2 rows of EMP = MANAGER

1. As an example, make a package which doesn't work well:

CREATE OR REPLACE PACKAGE example_pkg IS

PROCEDURE insert_emps_for_dept (p_deptno dept.deptno%TYPE);

END;

CREATE OR REPLACE PACKAGE BODY example_pkg IS

PROCEDURE insert_managers (p_deptno dept.deptno%TYPE) IS

BEGIN

INSERT INTO emp (empno, ename, job, mgr, sal, deptno)

VALUES (1, 'EMP 1', 'MANAGER', NULL, 5000, p_deptno);

INSERT INTO emp (empno, ename, job, mgr, sal, deptno)

VALUES (2, 'EMP 2', 'MANAGER', 1, 4000, p_deptno);

END;

PROCEDURE insert_programmers (p_deptno dept.deptno%TYPE) IS

BEGIN

INSERT INTO emp (empno, ename, job, mgr, sal, deptno)

VALUES (3, 'EMP 3', 'PROGRAMER', 1, 1000, p_deptno);

RAISE_APPLICATION_ERROR

(-20001, 'SIMULATED ERROR IN MIDLE OF 3.PART OF A TRANSACTION');

INSERT INTO emp (empno, ename, job, mgr, sal, deptno)

VALUES (4, 'EMP 4', 'PROGRAMER', 1, 1000, p_deptno);

END;

PROCEDURE insert_emps_for_dept (p_deptno dept.deptno%TYPE) IS

BEGIN

-- 2. part of a transaction

insert_managers (p_deptno);

-- 3. part of a transaction

BEGIN

insert_programmers (p_deptno);

EXCEPTION

WHEN OTHERS THEN NULL;

END;

2. Now call the procedure from an unnamed PL/SQL block (without database trigger):

BEGIN

INSERT INTO dept (deptno, dname) VALUES (1, 'DEPT 1');

example_pkg.insert_emps_for_dept (1);

END;

The result using a SELECT query:

SELECT emp.empno, emp.ename, dept.deptno, dept.dname

FROM emp, dept

WHERE empno BETWEEN 1 AND 4

AND emp.deptno = dept.deptno

ORDER BY empno

EMPNO ENAME DEPTNO DNAME

---------- --------------- ---------- --------------

1 EMP 1 1 DEPT 1

2 EMP 2 1 DEPT 1

3 EMP 3 1 DEPT 1

Of course the transaction wasn't correct because EMP 3 remained inserted.

3. Execute ROLLBACK and change insert_emps_for_dept procedure adding the following:

SAVEPOINT / ROLLBACK TO SAVEPOINT statements:

PROCEDURE insert_emps_for_dept (p_deptno dept.deptno%TYPE) IS

BEGIN

-- 2. part of a transaction

insert_managers (p_deptno);

-- 3. part of a transaction

BEGIN

SAVEPOINT before_insert_programmers;

insert_programmers (p_deptno);

EXCEPTION

WHEN OTHERS THEN ROLLBACK TO before_insert_programmers;

END;

If previous procedure is executed, the results will be as follows:

EMPNO ENAME DEPTNO DNAME

---------- --------------- ---------- --------------

1 EMP 1 1 DEPT 1

2 EMP 2 1 DEPT 1

4. Execute ROLLBACK again and create a database trigger:

CREATE OR REPLACE TRIGGER air_dept

AFTER INSERT ON dept

FOR EACH ROW

BEGIN

example_pkg.insert_emps_for_dept (:NEW.deptno);

END;

5. Next, try to execute the following command:

INSERT INTO dept (deptno, dname) VALUES (1, 'DEPT 1')

Of course, the following error shows up:

ERROR at line 1:

ORA-04092: cannot ROLLBACK in a trigger

ORA-06512: at "SCOTT.EXAMPLE_PKG", line 33

ORA-04092: cannot SET SAVEPOINT in a trigger

ORA-06512: at "SCOTT.AIR_DEPT", line 2

ORA-04088: error during execution of trigger 'SCOTT.AIR_DEPT'

6. Execute ROLLBACK again using a trick. It is based on the fact that if we call a remote procedure (via database link) and it shows up an unhandled exception, its DML effects will be cancelled (in contrast to a local procedure). We don't need a remote procedure, but we'll execute a “quasi-remote” procedure using a local database link as follows:

CREATE DATABASE LINK local_db_link

CONNECT TO scott IDENTIFIED BY tiger using 'local_alias' -- alias for your local database

7. Change the insert_emps_for_dept procedure again so that it calls the insert_programmers

procedure via database link. However we have to change a package specification as follows:

CREATE OR REPLACE PACKAGE example_pkg IS

PROCEDURE insert_emps_for_dept (p_deptno dept.deptno%TYPE);

-- must be in specification (database link)

PROCEDURE insert_programmers (p_deptno dept.deptno%TYPE);

END;

CREATE OR REPLACE PACKAGE BODY example_pkg IS

...

PROCEDURE insert_emps_for_dept (p_deptno dept.deptno%TYPE) IS

BEGIN

-- 2. part of a transaction

insert_managers (p_deptno);

-- 3. part of a transaction

BEGIN

example_pkg.insert_programmers@local_db_link (p_deptno);

EXCEPTION

WHEN OTHERS THEN NULL;

END;

8. If the INSERT command shown here is executed again:

INSERT INTO dept (deptno, dname) VALUES (1, 'DEPT 1')

using a SELECT query the results are as follows.

EMPNO ENAME DEPTNO DNAME

---------- --------------- ---------- --------------

1 EMP 1 1 DEPT 1

2 EMP 2 1 DEPT 1

Therefore, the result was successful, as if we've used SAVEPOINT / ROLLBACK TO SAVEPOINT statements.

There are at least two different ways that we could have accomplished this:

a) Create the INSERT_PROGRAMMERS procedure as an AUTONOMOUS_TRANSACTION.

The disadvantage of this approach is, that (in rare cases) the autonomous transaction

might be successful while the main one fails, so the result might be two rows of EMP = PROGRAMER without one row of DEPT and two rows of EMP = MANAGER. Of course, something like this wouldn't work if the EMP table has a FK on the deptno column).

b) Add the exception handler in the insert_programmers procedure to try to cancel the partial result:

PROCEDURE insert_programmers (p_deptno dept.deptno%TYPE) IS

BEGIN

INSERT INTO emp (empno, ename, job, mgr, sal, deptno)

VALUES (3, 'EMP 3', 'PROGRAMER', 1, 1000, p_deptno);

RAISE_APPLICATION_ERROR

(-20001, 'SIMULATED ERROR IN MIDLE OF 3.PART OF A TRANSACTION');

INSERT INTO emp (empno, ename, job, mgr, sal, deptno)

VALUES (4, 'EMP 4', 'PROGRAMER', 1, 1000, p_deptno);

EXCEPTION

WHEN OTHERS THEN

DELETE emp WHERE empno = 3; -- we try to cancel the partly result

END;

It is also possible also (but very rare) that this cancel event will fail. However, such a cancel event could be very complicated in a real world situation.