AmZone

Web Hosting by Netfirms | Free Domain Names by Netfirms

AmZone

A m Z o n e

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'

Press the Back button of you Browser to go to previous page
Home