Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Mutating tables issue and Autonomous Transactions

499236Sep 21 2007 — edited Sep 24 2007
can 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 22 2007
Added on Sep 21 2007
5 comments
600 views