Re: After delete trigger problem

On Nov 7, 2008, at 11:24 AM, Erik Jones wrote:

>
> 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.

Ah, nevermind this. Tom has just reponded in another reply and
educated me to the fact that fkeys fire in sequence with triggers, I
thought they were separate.

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