How to commit transaction on an after update event trigger?

Question

I'm new here in dba.stackexchange.com, so if I were to make any mistake, my apologies in advance.

I kinda confused on how trigger work. So I have a trigger called updcredaccess on table A that updates table B on after update event. Then I need the currently updated value on table A for other table to inspect, so I used autonomous transaction trigger. But then when I update, the value on table A doesn't seems to be updated with the current value.

Here is my table

Table A

id varchar(10)

val varchar(10)


Table B

id varchar(10)

val varchar(10)

and here is my trigger Table A trigger

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
select val into cek from A where id = :new.id;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

And for table B Table B trigger

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
select val into v from B where id = :new.id;  
-- Some other pl/sql code --  
end;  
/

I then input 2 records like

 insert all into A values ('a', 'false') into A values ('b', 'false') select * from dual;

Then i tried this code

 update A set val = 'true'

The query works fine. But I was so confused because at the log console it says

Cek cek: false (The output log at trigger A)

The value still hasn't changed.

So, what I need is the current new val value at the 2nd trigger, so It can be processed. Can anyone help?

Answer 1

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/