Re: fail-safe sql update triggers

> -----Original Message-----
> From: Michael Monnerie [mailto:michael(dot)monnerie(at)is(dot)it-management(dot)at]
> Sent: Friday, September 03, 2010 9:03 AM
> To: pgsql-admin(at)postgresql(dot)org
> Subject: fail-safe sql update triggers
>
> I want to log all activity from a table to a old_table.
> Creating an ON INSERT trigger is simple, it just needs to
>
> INSERT INTO old_filter SELECT NEW.*;
>
> in a procedure that is called via the trigger. But what about
> updates? There is no simple
>
> UPDATE old_filter SET NEW.* WHERE id=NEW.id;
>
> so I would need to declare each column like
>
> UPDATE old_filter SET field1=NEW.field1,f2=NEW.f2,.... where
> id=NEW.id;
>
> But that is error prone, because when the "filter" table is
> changed to have a new column, the UPDATE statement would not
> contain it. Is there a fail-proof shortcut?
>
>
> mit freundlichen Grüßen
> Michael Monnerie, Ing. BSc
>

Michael,

You are seeing only half of the problem.
When you modify your "filter" table (i.e. add a column), not only UPDATE statement in your trigger function should be modified to reflect the change in the original "filter" table, but also your history table "old_filter" should be modified as well: new column should be added.

So, in short there is no "easy" way around.
When "source" table is modified, "destination" table and trigger function should be also modified.

Regards,
Igor Neyman