Received: from smtp018.mail.yahoo.com (smtp018.mail.yahoo.com [216.136.174.115]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f239UFx87449 for ; Sat, 3 Mar 2001 04:30:16 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from a61b0.pppool.de (HELO jupiter.jw.home) (213.6.97.176) by smtp.mail.vip.sc5.yahoo.com with SMTP; 3 Mar 2001 09:30:12 -0000 X-Apparently-From: Received: (from janwieck@localhost) by jupiter.jw.home (8.9.3/8.9.3) id KAA03563; Fri, 2 Mar 2001 10:34:02 -0500 From: Jan Wieck Message-Id: <200103021534.KAA03563@jupiter.jw.home> Subject: Re: Help creating rules/triggers/functions In-Reply-To: <200102271643.QAA02463@mjtsa.com> from Blaise Carrupt at "Feb 27, 2001 04:43:25 pm" To: Blaise Carrupt Date: Fri, 2 Mar 2001 10:34:02 -0500 (EST) CC: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/34 X-Sequence-Number: 1679 Blaise Carrupt wrote: > Hi all ! > > I use PostgreSQL 7.0.2 on a HP-UX system. > > I would like to create a simple function and a simple trigger (or rule) that > deny a delete from a table if the row is referenced in another table. > > I though it should look like this (from my Ingres experience... :) : > > create function A_del(int4 i_id) > BEGIN > SELECT id > FROM b > where a_id = :i_id; > > if rowcount > 0 then > RAISE EXCEPTION "not allowed !" > end if; > END > CREATE FUNCTION A_del () RETURNS opaque AS ' DECLARE nrefs integer; BEGIN nrefs := count(*) FROM b WHERE a_id = OLD.i_id; IF nrefs > 0 THEN RAISE EXCEPTION ''a_id % still referenced from b'', OLD.i_id; END IF; RETURN OLD; END;' LANGUAGE 'plpgsql'; > > create trigger before delete from A for each row execute procedure A_del(old.id) CREATE TRIGGER A_del BEFORE DELETE ON A FOR EACH ROW EXECUTE PROCEDURE A_del(); > > > But it seems to be much more complicated with Postgres (create a C function > using CurrentTriggerData,...). May I have missed something or is it really much > more complicated ? Alternatively (IMHO preferred) you could use a referential integrity constraint in table B, which would also cover UPDATE on A and check values inserted/updated into/in B. CREATE TABLE B ( ... FOREIGN KEY (i_id) REFERENCES A (a_id) Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com