Обсуждение: Duplicating a table with a trigger

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?

basically I have

my_awesome_table

and

my_awesome_table_copy

Every time there is an insert or an update on my_awesome_table I would like to run exactly the same update or insert on
my_awesome_table_copy.
I was thinking a trigger but the how to there after has got me a little confused.

if anyone could help it would be greatly appreciated.

Thanks
Wayn0

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;