Обсуждение: plpgsql before insert update delete

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.

_________________________________________________________________
かわいい & 使えるブラウザで、インターネット生活もっと楽しくなる！
http://explorer.msn.co.jp/

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_archiveVALUES(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