Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Trigger synchronization

Trigger synchronization

From: Cristian Mariz <MarizCristian_at_valsugana.com>
Date: 1997/04/29
Message-ID: <33663270.5E0C2A3C@valsugana.com>#1/1

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.it
Received on Tue Apr 29 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US