Re: Duplicating a table with a trigger

On Tue, Sep 3, 2013 at 1:11 PM, Wayne Oliver <wayn0.ml@gmail.com> wrote:
> Hi All,
>
> I was hoping somebody here could point me in the right direction.
> I am trying to duplicate changes to a specific table to a copy of that table.
>
> Does that make sense?


It depends on your application aim.
I would go for a replication solution, since this seems to me your
case and probably you will end up requiring to replicate more than one
table.
Another solution could be to do a dump/restore of the table using a
cron job or alike.

If you want to do it via trigger the most complex case is the update
one. Something like this is the base (not tested):


CREATE OR REPLACE FUNCTION duplicate_rows()
RETURNS trigger AS
$BODY$
DECLARE

BEGIN


  -- if executing for a single column then compute the path
  IF TG_OP = 'UPDATE'  THEN
     UPDATE table_copy SET field1 = NEW.field1, field2 = NEW.field2, ...
    WHERE pk = NEW.pk;
     ELSE IF TG_OP = 'INSERT' THEN
       INSERT INTO table_copy
       SELECT * FROM NEW;
     END IF;

     RETURN NEW;

  END IF;