pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / 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>
  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-08 03:01  Ian Barwick <barwick@gmx.net>
  parent: kiyo taka <hihajime@hotmail.com>
  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