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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
