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