Re: After delete trigger problem

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(at)sss(dot)pgh(dot)pa(dot)us>
To: "Teemu Juntunen" <teemu(dot)juntunen(at)e-ngine(dot)fi>
Cc: "PostgreSQL" <pgsql-general(at)postgresql(dot)org>
Sent: Saturday, November 08, 2008 7:01 AM
Subject: Re: [GENERAL] After delete trigger problem

> "Teemu Juntunen" <teemu(dot)juntunen(at)e-ngine(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general