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