[SQL] Trigger (on mutating tables) updates older data

I tried to update one table from another by row trigger and there is a problem with it.

Trigger works after I add PRAGMA AUTONOMOUS_TRANSACTION.. but works in a strange way.

Oracle version 12c. I have a similar (simplified) code :



create table w_mutating2 (id number);

insert into w_mutating1 values (NULL); commit;

create or replace trigger w_mutate_trg1
before insert on w_mutating2
for each row
declare
pragma autonomous_transaction;

begin
update w_mutating1 set num = (select max(id) from w_mutating2);
commit;

end;
/

insert into w_mutating2 values (2);
commit;

select * from w_mutating1;
select * from w_mutating2;


After that my table w_mutating1 is with NULL value. So I execute it once again

insert into w_mutating2 values (5);
commit;

select * from w_mutating1;
select * from w_mutating2;


my table w_mutating have value => 2 So my trigger works.. but one operation too late In my complex example I use function in similar trigger. When I execute function with parameters from command line it works. Data is ok. But when I execute it from trigger (with PRAGMA autonomous_transaction) it update my data as above.

Any ideas how to solve it?

Continue reading...