"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