1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussão em 'Outras Linguagens' iniciado por Stack, Agosto 31, 2021.

  1. Stack

    Stack Membro Participativo

    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...

Compartilhe esta Página