Re: BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key

Yes. In my case I want the history gone too. Keeping the history wouldn't hurt, but it's just a preference.

If I did keep the history I definitely don't want that last UPDATE where E is set null... but that's easily circumvented via the trigger.

Thanks for the pointer to look for the other path!

Thanks,

Steve

---------------------------------------------------------------------------------------
Stephen Cuppett
SAS® Certified Advanced Programmer for SAS9®
Tel: +1 919 531 0659 ▪ Stephen(dot)Cuppett(at)sas(dot)com
www.sas.com

SAS® … THE POWER TO KNOW®

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, September 03, 2008 1:09 PM
To: Stephen Cuppett
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #4396: Trigger event fired "UPDATE" when "DELETE" happening via foreign key

Stephen Cuppett <Stephen(dot)Cuppett(at)sas(dot)com> writes:
> When A is deleted, B is deleted, E is set null. Then, D is deleted and E is deleted.

> FOR EVERY ROW AFTER UPDATE is run on E for rows that no longer exist.

> Not sure if that's a bug or not... not sure it would be undesirable under conditions for that not to run for those tuples that are already gone.

Well, not firing the trigger would be pretty bad in some cases too. For
instance if you're trying to log updates in a history table, you'd not
want to miss updates just because they were immediately obsoleted.

Looking again at your example, that's exactly what your trigger is
doing, isn't it? Why in the world has your history table got an FK
back to the live tables? That presumes that you'll never delete any
objects ... or that you're willing to lose all history about them
as soon as they're deleted, which seems a pretty odd choice.

regards, tom lane