How to accomplish on commit trigger in Oracle
If you have any use case for on commit trigger, it is almost always due to a given application using database improperly, fixing application design can take some time, especially if it is third party vendor application. In the spirit of knowing and keeping all possible lever's at DBA's disposal, one can use below approach to define on commit trigger for enforcing any data integrity checks at transaction level. This post is not to encourage anybody to use triggers, one should avoid them as much as possible.
The trick here is to leverage userenv('commitscn') feature.
You can read more about on commit trigger research at http://technology.amis.nl/2006/08/07/the-hunt-for-the-oracle-database-on-commit-trigger/
Jonathan Lewis has great information about commitscn at http://www.jlcomp.demon.co.uk/commit.html
Here is the test case:
01:38:59 SQL> create table tr1(a number not null, b number, primary key(a));
Table created.
01:38:59 SQL> create table tr2(a number not null, b number);
Table created.
01:38:59 SQL> create table tr3(a number not null, b number, primary key(a));
Table created.
01:38:59 SQL>
01:38:59 SQL> create or replace trigger tr1_tr before insert on tr1
01:38:59 2 for each row
01:38:59 3 begin
01:38:59 4 insert into tr2 values (1,userenv('commitscn'));
01:38:59 5 end;
01:38:59 6 /
Trigger created.
01:38:59 SQL>
01:38:59 SQL> create or replace trigger tr2_tr before update on tr2
01:38:59 2 for each row
01:38:59 3 begin
01:38:59 4 insert into tr3 values (0,0);
01:38:59 5 end;
01:38:59 6 /
Trigger created.
01:38:59 SQL>
01:38:59 SQL> insert into tr1 values (1,1);
1 row created.
01:38:59 SQL> --insert into tr2 values (1, userenv('commitscn'));
01:38:59 SQL> select * from tr1;
A B
-------------------- --------------------
1 1
01:38:59 SQL> select * from tr2;
A B
-------------------- --------------------
1 40208814
01:38:59 SQL> select * from tr3;
no rows selected
01:38:59 SQL> commit;
Commit complete.
01:38:59 SQL> select * from tr1;
A B
-------------------- --------------------
1 1
01:38:59 SQL> select * from tr2;
A B
-------------------- --------------------
1 40208816
01:38:59 SQL> select * from tr3;
A B
-------------------- --------------------
0 0
01:38:59 SQL>
01:38:59 SQL>
01:38:59 SQL> insert into tr1 values (2,2);
1 row created.
01:38:59 SQL> --insert into tr2 values (2, userenv('commitscn'));
01:38:59 SQL> select * from tr1;
A B
-------------------- --------------------
1 1
2 2
01:38:59 SQL> select * from tr2;
A B
-------------------- --------------------
1 40208816
1 40208819
01:38:59 SQL> select * from tr3;
A B
-------------------- --------------------
0 0
01:38:59 SQL> commit;
commit
*
ERROR at line 1:
ORA-00001: unique constraint (TEST123.SYS_C003852) violated
ORA-06512: at "TEST123.TR2_TR", line 2
ORA-04088: error during execution of trigger 'TEST123.TR2_TR'
01:38:59 SQL> select * from tr1;
A B
-------------------- --------------------
1 1
01:38:59 SQL> select * from tr2;
A B
-------------------- --------------------
1 40208816
01:38:59 SQL> select * from tr3;
A B
-------------------- --------------------
0 0
TR1 can be real application table, I introduced TR2 and TR3 tables to leverage commitscn feature, and all of this can be done without changing application code. By inserting one row into TR2 table with commitscn value, Oracle would automatically update commitscn value it inserted in TR2 table at commit time. Since we have before update trigger created on TR2 table, it will be fired at commit time.
Any error during TR2 table based trigger execution will automatically cause the entire transaction to be rolled back (as seen above).
posted by Saibabu Devabhaktuni @ 1:42 AM
4 comments
