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@xxxxxxxxxxxxx>
To: "Teemu Juntunen" <teemu.juntunen@xxxxxxxxxx>
Cc: "PostgreSQL" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Saturday, November 08, 2008 7:01 AM
Subject: Re: After delete trigger problem
"Teemu Juntunen" <teemu.juntunen@xxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
