Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Trigger synchronization
Hi all,
Could you help me with Oracle triggers?
I have to synchronize triggers to make them execute sequentally rather
than
interleaved. I mean, if one trigger causes another to be fired, the
second
one should wait for the first to terminate the execution (while Oracle
implements interruptable triggers by default).
I thought to do this with a very simple semaphore structure: each
trigger waits
on the semaphore after begin and releases before end.
The problem is that i HAVEN'T FOUND anything that implements semaphores
in
Oracle,except from the DBMS_LOCK package, which doesn't work with
triggers due
to a strange compilation error when you try to use it in a trigger body.
I also tried with the LOCK TABLE statement on a dummy table to implement
the
semaphore, but the manual says that locks act for users rather than for
transactions.
So I tried to create 2 triggers from 2 different users. Here is the
code:
(the two triggers are created by 2 users, bda09 and bda10; tables
condlock and
PROVACONDIVISA are created by bda09, but bda10 has all privileges
granted on
tables. Furthermore, the 2 users have the CREATE ANY TRIGGER privilege)
create or replace trigger BDA09T after insert on PROVACONDIVISA begin
lock table condlock in exclusive mode; insert into condlock values('michia09'); dbms_output.put_line('Here BDA09 ....'); update provacondivisa set Nome='Michele'; dbms_output.put_line('bye BDA09 ....'); delete from bda09.condlock where lck='michia09';
end;
/
create or replace trigger BDA10T after update on BDA09.PROVACONDIVISA begin
lock table bda09.condlock in exclusive mode; insert into bda09.condlock values('michia10'); dbms_output.put_line('Here BDA10 ....'); dbms_output.put_line('bye BDA010 ....'); delete from bda09.condlock where lck='michia10';end;
the output after an insert is
here bda09
here bda10
bye bda10
bye bda09
I tried all the ways, but i cannot get to the solution. Could anyone help me ?
Bye and thenk you all
Michele barana_at_arena.sci.univr.itReceived on Tue Apr 29 1997 - 00:00:00 CDT