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

> 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