pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
plpgsql before insert update delete 2+ messages / 2 participants [nested] [flat]
* plpgsql before insert update delete @ 2001-12-05 05:47 kiyo taka <hihajime@hotmail.com> 2001-12-08 03:01 ` Re: plpgsql before insert update delete Ian Barwick <barwick@gmx.net> 0 siblings, 1 reply; 2+ messages in thread From: kiyo taka @ 2001-12-05 05:47 UTC (permalink / raw) To: pgsql-sql 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? any help will be greatly appreciated. thanx. _________________________________________________________________ $B$+$o$$$$(B & $B;H$($k%V%i%&%6$G!"%$%s%?!<%M%C%H@83h$b$C$H3Z$7$/$J$k!*(B http://explorer.msn.co.jp/ ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: plpgsql before insert update delete 2001-12-05 05:47 plpgsql before insert update delete kiyo taka <hihajime@hotmail.com> @ 2001-12-08 03:01 ` Ian Barwick <barwick@gmx.net> 0 siblings, 0 replies; 2+ messages in thread From: Ian Barwick @ 2001-12-08 03:01 UTC (permalink / raw) To: kiyo taka <hihajime@hotmail.com>; pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-12-08 03:01 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2001-12-05 05:47 plpgsql before insert update delete kiyo taka <hihajime@hotmail.com> 2001-12-08 03:01 ` Ian Barwick <barwick@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox