Mutating tables issue and Autonomous Transactions
499236Sep 21 2007 — edited Sep 24 2007can we say that making a trigger body as an autonomous transaction will help us escape from the mutating table issue?
I have tested this with an example as follows...
SQL>create table t3 (a number)
/
SQL> create table t4(c number);
Table created.
SQL> create or replace
2 trigger air_t3
3 after insert on t3
4 for each row
5 declare
6 pragma autonomous_transaction;
7 count_v number := 0;
8 begin
9 select count(*) into count_v from t3;
10 dbms_output.put_line('t3 rowcount = '||count_v);
11 dbms_output.put_line(':new.a '||:new.a);
12 insert into t4 values(:new.a);
13 commit;
14 end;
15 /
Trigger created.
SQL> insert into t3 values(1);
t3 rowcount = 0
:new.a 1
1 row created.
SQL> roll;
Rollback complete.
SQL> select * from t3;
A
----------
1
SQL> select * from t4;
C
----------
1
SQL>
As per this example seems like we have bypassed the mutating table issue. Is that good to say this is a solution for mutating table issue?
Request u to mention If I have to include some more testing in this analysis.
Thanks in advance