Re: After delete trigger problem

From: Erik Jones <ejones(at)engineyard(dot)com>
To: Teemu Juntunen <teemu(dot)juntunen(at)e-ngine(dot)fi>
Cc: "PostgreSQL" <pgsql-general(at)postgresql(dot)org>
Subject: Re: After delete trigger problem
Date: 2008-11-07 19:24:58
Message-ID: 2BB99055-EF16-47B7-9744-B5DFE07AAFDB@engineyard.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Erik Jones 2008-11-07 19:26:32 Re: After delete trigger problem
Previous Message Tom Lane 2008-11-07 19:23:22 Re: After delete trigger problem