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?

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;  
/
  • Hello, thank you for the answer :D Above code works for me without error, but it doesn't solve my problem... As questioned above "Then I need the currently updated value on table A for other table to inspect", "Other table" here means Table B to inspect... So i need the currently new value at Table A to be inspected by trigger on Table B, I used "Select val into v from A where id = :new.id", but it always returns old value..., how to get the current value...? – Raqasyinov Mar 28 '16 at 2:50

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.