Thread: After delete trigger problem
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
$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?
Regards,
Teemu Juntunen
"Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes:
> CREATE TRIGGER td_y AFTER DELETE ON chlid FOR EACH ROW EXECUTE PROCEDURE fn_td_y();
> It seems that SELECT results to null, so the master has already deleted the row. Is this intended and how can I solve
this?
Your trigger is firing after the RI triggers. If you want it to fire
before, give it a name that is before them (in ASCII order).
regards, tom lane
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
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
Hi, thanks for answers! I tried to rename the function ALTER TRIGGER td_y ON child RENAME TO "BTD_Y"; with no help. Also according to the manual BEFORE DELETE trigger should launch before casading delete, so I changed the trigger CREATE TRIGGER "BTD_Y" BEFORE DELETE ON chlid FOR EACH ROW EXECUTE PROCEDURE fn_td_y(); with no help. Any other ideas? Regards, Teemu Juntunen ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Teemu Juntunen" <teemu.juntunen@e-ngine.fi> Cc: "PostgreSQL" <pgsql-general@postgresql.org> Sent: Friday, November 07, 2008 9:23 PM Subject: Re: [GENERAL] After delete trigger problem > "Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes: >> CREATE TRIGGER td_y AFTER DELETE ON chlid FOR EACH ROW EXECUTE >> PROCEDURE fn_td_y(); > >> It seems that SELECT results to null, so the master has already deleted >> the row. Is this intended and how can I solve this? > > Your trigger is firing after the RI triggers. If you want it to fire > before, give it a name that is before them (in ASCII order). > > regards, tom lane
"Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes:
> Also according to the manual BEFORE DELETE trigger should launch before
> casading delete, so I changed the trigger
> CREATE TRIGGER "BTD_Y" BEFORE DELETE ON chlid FOR EACH ROW EXECUTE
> PROCEDURE fn_td_y();
> with no help.
In that case your problem is not about whether you are firing before the
RI action happens; you've got some other bug instead. It's hard to see
what from the limited details you provided, though. Can you put
together a complete example?
regards, tom lane
Hi,
here is a complete example. With my Windows PostgreSQL 8.3.3 installation
this example leads to exception, because master has been deleted before the
child.
Teemu
--DROP TABLE master;
--DROP TABLE child;
--DROP FUNCTION fn_checkmaster()
-- The master table
CREATE TABLE master
(
foo smallint NOT NULL DEFAULT 0,
CONSTRAINT master_pkey PRIMARY KEY (foo)
)
WITH (OIDS=FALSE);
ALTER TABLE master OWNER TO postgres;
-- A child table to the master
CREATE TABLE child
(
foo smallint NOT NULL DEFAULT 0,
hoo smallint NOT NULL DEFAULT 0,
CONSTRAINT child_pkey PRIMARY KEY (foo,hoo),
CONSTRAINT child_foo_fkey FOREIGN KEY (foo)
REFERENCES master (foo) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
ALTER TABLE tilitysraha OWNER TO postgres;
-- Function which checks the master table
CREATE OR REPLACE FUNCTION fn_checkmaster() RETURNS trigger AS
$BODY$
DECLARE
fcount integer;
BEGIN
-- Want to check something from the master table
SELECT count(*) INTO fcount FROM master WHERE master.foo = old.foo;
-- Nothing found
IF fcount = 0 THEN
RAISE EXCEPTION 'Master not found anymore!';
END IF;
RETURN old;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION fn_checkmaster() OWNER TO postgres;
-- 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;
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Teemu Juntunen" <teemu.juntunen@e-ngine.fi>
Cc: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Saturday, November 08, 2008 7:01 AM
Subject: Re: [GENERAL] After delete trigger problem
> "Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes:
>> Also according to the manual BEFORE DELETE trigger should launch before
>> casading delete, so I changed the trigger
>
>> CREATE TRIGGER "BTD_Y" BEFORE DELETE ON chlid FOR EACH ROW EXECUTE
>> PROCEDURE fn_td_y();
>
>> with no help.
>
> In that case your problem is not about whether you are firing before the
> RI action happens; you've got some other bug instead. It's hard to see
> what from the limited details you provided, though. Can you put
> together a complete example?
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
"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