Re: After delete trigger problem

On Nov 7, 2008, at 10:57 AM, Teemu Juntunen wrote:

> Hello,
>
> I have a child table with
>
> CONSTRAINT fkey FOREIGN KEY (x)  REFERENCES master (x) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE CASCADE.
>
> and
>
> CREATE TRIGGER td_y  AFTER DELETE ON chlid  FOR EACH ROW EXECUTE
> PROCEDURE fn_td_y();
> and this trigger refers to the master table...
>
> CREATE OR REPLACE FUNCTION fn_td_y() RETURNS trigger AS
> $BODY$
> DECLARE
>   fi integer;
> BEGIN
>     SELECT i INTO fi FROM master  WHERE x = old.x;
> ...
>
> It seems that SELECT results to null, so the master has already
> deleted the row. Is this intended and how can I solve this?

Yes,, that is intended.  An AFTER DELETE statement runs after the
triggering statement has completed and your FOREIGN KEY constraint is
set to ON DELETE CASCADE so by the time the statement completes and
the trigger fires the DELETE has already CASCADEd to the master
table.  As far as how to get around it we'd need to know a little more
about what the trigger is actually supposed to do.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k