Thread

1. Shouldn't ON UPDATE/DELETE triggers be BEFORE triggers?

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-02T16:23:50Z

   While thinking over Jeremy Radlow's recent problem report in
   pgsql-general, it occurs to me that it's probably wrong to implement
   referential integrity actions like ON CASCADE DELETE in AFTER triggers.
   Seems to me that this breaks the fundamental rule of referential
   integrity: if B references A then there must always be a matching A
   row for every B row.  Therefore, if we delete a row from A we should
   delete the matching B row(s) before, not after, we delete from A.
   Otherwise the remainder of the transaction sees an illegal state of
   the database.
   
   Comments?  How about ON UPDATE actions?
   
   			regards, tom lane
   
   

2. Re: Shouldn't ON UPDATE/DELETE triggers be BEFORE triggers?

   Stephan Szabo <acroyear_07030@yahoo.com> — 2001-04-02T21:11:15Z

   > While thinking over Jeremy Radlow's recent problem report in
   > pgsql-general, it occurs to me that it's probably wrong to implement
   > referential integrity actions like ON CASCADE DELETE in AFTER triggers.
   > Seems to me that this breaks the fundamental rule of referential
   > integrity: if B references A then there must always be a matching A
   > row for every B row.  Therefore, if we delete a row from A we should
   > delete the matching B row(s) before, not after, we delete from A.
   > Otherwise the remainder of the transaction sees an illegal state of
   > the database.
   If we're right in how we read the spec, then this isn't an illegal
   state except for non-deferred constraints and then only for the
   period between the delete and the after trigger running.  Note:
   I think we may be misinterpreting the spec here (more below),
   but if our interpretation, deferred actions occur at end of transaction,
   is correct, then the "invalid" state is valid to see for the rest of
   transaction in that case.
   
    > Comments?  How about ON UPDATE actions?
   
   However, I think that the intention was to have actions (obviously
   other than NO ACTION) occur immediately even on deferred
   constraints.  I say this because the sections on figuring matching
   and uniquely matching rows makes little sense if the action could be 
   deferred and IIRC it says things like "if a row is marked for removal"
   rather than "at the time of checking a constraint, if a row was marked
   for removal."
   
   When I tried this in Oracle a while ago, this was also what they
   did.  A deferred constraint with a cascade would kill the
   referencing rows after the delete so you wouldn't see them for the
   rest of the transaction.
   
   
   
   _________________________________________________________
   Do You Yahoo!?
   Get your free @yahoo.com address at http://mail.yahoo.com