Received: from post.webmailer.de (natpost.webmailer.de [192.67.198.65]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB83xAm31976 for ; Fri, 7 Dec 2001 22:59:10 -0500 (EST) (envelope-from barwick@gmx.net) Received: from there (pD9EB1434.dip.t-dialin.net [217.235.20.52]) by post.webmailer.de (8.9.3/8.8.7) with SMTP id EAA28586; Sat, 8 Dec 2001 04:59:12 +0100 (MET) Message-Id: <200112080359.EAA28586@post.webmailer.de> Content-Type: text/plain; charset="iso-2022-jp" From: Ian Barwick To: "kiyo taka" , pgsql-sql@postgresql.org Subject: Re: plpgsql before insert update delete Date: Sat, 8 Dec 2001 04:01:00 +0100 X-Mailer: KMail [version 1.3.1] References: In-Reply-To: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200112/103 X-Sequence-Number: 5926 On Wednesday 05 December 2001 06:47, kiyo taka wrote: > hi all. > > i'd like to noe how to create trigger which will insert tablez old data > into an archive table before any(insert,update,delete) operation is > executed. > > i've tried "if inserting ...." but it didnt work. > is there any function that will do it? Assuming an example table thus: CREATE TABLE mytable(id INTEGER, whatever VARCHAR(16)); and an identical archive table: CREATE TABLE mytable_archive(id INTEGER, whatever VARCHAR(16)); you can create the following function and trigger for that table: DROP FUNCTION mytable_archive_proc(); CREATE FUNCTION mytable_archive_proc() RETURNS opaque AS ' BEGIN IF TG_OP = ''DELETE'' THEN INSERT INTO mytable_archive VALUES(old.id, old.whatever); RETURN old; ELSE INSERT INTO mytable_archive VALUES(new.id, new.whatever); END IF; RETURN new; END;' LANGUAGE 'plpgsql'; DROP TRIGGER mytable_archive_trigger ON mytable; CREATE TRIGGER mytable_archive_trigger BEFORE INSERT OR UPDATE OR DELETE ON mytable FOR EACH ROW EXECUTE PROCEDURE mytable_archive_proc(); Which should write all changes in "mytable" to "mytable_archive". > any help will be greatly appreciated. > > thanx. Dou itashimashite Ian Barwick