Re: After delete trigger problem

"Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes:
> -- Trigger at the child table
> CREATE TRIGGER "AFTER_DELETE_CHILD"
>   AFTER DELETE
>   ON child
>   FOR EACH ROW
>   EXECUTE PROCEDURE fn_checkmaster();

> -- This example leads to an exception
> INSERT INTO master (foo) VALUES (1);
> INSERT INTO child (foo,hoo) VALUES (1,1);
> DELETE FROM master WHERE foo=1;

Oh, I see the problem: the trigger's on the wrong table.  What you've
got here is:

* delete a master row

* after that, the FK trigger on the master fires and issues a DELETE
against affected rows of the child table

* this deletes a child row

* after that, your trigger fires

Basically there's no way for a trigger on the child to see the master
row still there, because it's already gone before any action is taken
against the child.  Even a BEFORE DELETE trigger would run too late.

You might be able to do something with a delete trigger on the
master table ...

            regards, tom lane