Skip site navigation (1) Skip section navigation (2)

Re: fail-safe sql update triggers

From: "Igor Neyman" <ineyman(at)perceptron(dot)com>
To: "Michael Monnerie" <michael(dot)monnerie(at)is(dot)it-management(dot)at>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: fail-safe sql update triggers
Date: 2010-09-07 12:55:27
Message-ID: F4C27E77F7A33E4CA98C19A9DC6722A20671BA9C@EXCHANGE.corp.perceptron.com (view raw or flat)
Thread:
Lists: pgsql-admin
> -----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

In response to

pgsql-admin by date

Next:From: felDate: 2010-09-07 13:03:44
Subject: [NOVICE] - SAN/NAS/DAS - Need advises
Previous:From: LewDate: 2010-09-07 00:22:22
Subject: Re: problem with foreign key

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group