Re: After delete trigger problem
От | Teemu Juntunen |
---|---|
Тема | Re: After delete trigger problem |
Дата | |
Msg-id | 9C18640B515D487EBC2A4F62272782F4@eng02 обсуждение исходный текст |
Ответ на | After delete trigger problem ("Teemu Juntunen" <teemu.juntunen@e-ngine.fi>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: