Committing in Database triggers
Is it possible to commit changes in a database trigger?
In other words, can the commit statement be used in database triggers?
Yes, it can be as an autonomous transaction (refer oracle article 40).
1. Example for Autonomous transaction triggers.
E.g.:
SQL> create or replace trigger am10_bef_trg
2 before insert on am10
3 for each row
4 declare
5 l_chr varchar2(1);
6 pragma autonomous_transaction;
7 begin
8 insert into am11 values(:new.col1, :new.col2);
9 commit;
10 end;
11 /
Trigger created.
SQL>insert into am10 values(11, 'bingo!!!');
1 row created.
SQL>select * from am10;
COL1 COL2
__________ ____________________
1 ok check
2 amar
3 bingo
7 check ok
3 bingo
7 amar
11 bingo!!!
7 rows selected.
SQL>select * from am11;
COL1 COL2
__________ __________
11 bingo!!!
SQL>rollback;
Rollback complete.
SQL>select * from am10;
COL1 COL2
__________ ____________________
1 ok check
2 amar
3 bingo
7 check ok
3 bingo
7 amar
6 rows selected.
SQL>select * from am11;
COL1 COL2
__________ __________
11 bingo!!!
2. It is mandatory to commit changes when autonomous transaction is being used.
E.g.:
SQL> create or replace trigger am10_bef_trg
2 before insert on am10
3 for each row
4 declare
5 l_chr varchar2(1);
6 pragma autonomous_transaction;
7 begin
8 insert into am11 values(:new.col1, :new.col2);
9 end;
10 /
Trigger created.
SQL> insert into am10 values(11, 'bingo!!!');
insert into am10 values(11, 'bingo!!!')
*
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "RAPID.AM10_BEF_TRG", line 6
ORA-04088: error during execution of trigger 'RAPID.AM10_BEF_TRG'
SQL>select * from am10;
COL1 COL2
__________ ____________________
1 ok check
2 amar
3 bingo
7 check ok
3 bingo
7 amar
6 rows selected.
SQL>select * from am11;
no rows selected
3. Without autonomous transaction, the trigger will be created with
commit statement also, but at execution time, it will fail.
E.g.:
SQL> create or replace trigger am10_bef_trg
2 before insert on am10
3 for each row
4 declare
5 l_chr varchar2(1);
6 -- pragma autonomous_transaction;
7 begin
8 insert into am11 values(:new.col1, :new.col2);
9 commit;
10 end;
11 /
Trigger created.
SQL>insert into am10 values(20, 'shit');
insert into am10 values(20, 'shit')
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "RAPID.AM10_BEF_TRG", line 6
ORA-04088: error during execution of trigger 'RAPID.AM10_BEF_TRG'
Best viewed in medium text size. Please refresh this page (F5) to view the latest information. This page was created on 16-dec-2001 and last updated on 16-dec-2001.
please forward all queries to amar_padhi@fastmail.fm