Skip to Main Content
  • Questions
  • Synchronising BEFORE and AFTER UPDATE triggers

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rob.

Asked: November 21, 2016 - 4:16 pm UTC

Last updated: November 21, 2016 - 5:20 pm UTC

Version: SQL Developer Version 3.2.20.09

Viewed 1000+ times

You Asked

Can a BEFORE UPDATE trigger on table A wait in a loop on a update to table B (by a Java batch program which also wants to read table A) before exiting and causing the AFTER UPDATE trigger on table A to commence?

and Chris said...

I'm not sure I understand what you're trying to do here...

If you issue an update to B in the before trigger on A, then if there's uncommitted update on B it'll be blocked:

create table t1
  ( id number ,name varchar2 ( 200 )
  ) ;
create table t2
  ( id number,name varchar2 ( 200 )
  ) ;
  
insert into t1 values (1, 'old');
insert into t2 values (1, 'old');
commit;
  
create or replace trigger bifer
before update on t1
for each row
begin
  dbms_output.put_line('before ' || to_char(sysdate, 'hh24:mi:ss'));
  update t2
  set    name = 'trigger new'
  where  t2.id = :new.id;
end;
/

create or replace trigger aifer
after update on t1
for each row
begin
  dbms_output.put_line('after ' || to_char(sysdate, 'hh24:mi:ss'));
end;
/


Then in session 1 you update B (T2):

SQL> update t2 set name = 'new';

1 row updated.


And in session 2 you update A (T1):

SQL> update t1 set name = 'new';


This will hang until you commit back in session 1:

SQL> commit;

Commit complete.


Back in session 2:

before 09:18:05
after 09:18:17

1 row updated.


So the answer is yes. But if you're using this as a way to control firing of before and after triggers it's a bad idea.

What exactly are you trying to do?

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library