Re: Triggers v Referential keys

On 31 ago, 14:13, Helma <helma.vi..._at_hotmail.com> wrote:
> > "Can Referential Keys be totaly substituted by Triggers?"
> > No. (isolation level / read commited...)
>
> Can you explain this some more? Trying to use a trigger instead of a
> referential key isn't possible because of isolation level / read
> committed?
>
> H.

A little example about mimicking Table_2 FK to Table_1 by issuing a 'before or update' trigger with something like:

begin

   select * from table 1 where table1_pk = :new.table2_fk; where no_data_found then
 blah, blah, blah...
end;

OK. Now:

session 1: begin transaction.

session 2: begin transaction.

session 2: delete row from table_1 where value_pk = 'BLAH';

session 1: insert into table_2 where value_fk = 'BLAH' => executes trigger

           in which session 1 still sees value_pk = 'BLAH'
           so row with value_pk = 'BLAH' is inserted.

session 2: commits transaction => row with value_pk = 'BLAH' does not exist anymore;

session 1: commits transaction => INCONSISTENCY.

Of course, you could 'fix' this particular case blocking the rows manually with SELECT FOR UPDATE in the trigger and other things like that... but I shiver only thinking about it...

Cheers.

Carlos. Received on Fri Aug 31 2007 - 07:28:32 CDT