Re: After delete trigger problem

"Teemu Juntunen" <teemu(dot)juntunen(at)e-ngine(dot)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