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

Re: fail-safe sql update triggers

From: "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: fail-safe sql update triggers
Date: 2010-09-03 17:50:54
Message-ID: 04A6DB42D2BA534FAC77B90562A6A03D015542F9@server.rad-con.local (view raw or flat)
Thread:
Lists: pgsql-admin
 
I thought I sent this earlier, but it's not in my Sent box, so I'll try
again.

Your solution maintains an exact copy of two tables.  Whenever a record
is updated in the first, it is updated in the second, and you have lost
information about the previous value.

Whenever I do anything like this, I have three triggers on the source
table, one each for insert, update and delete.  The history table has
the same columns as the source table, plus two more, one named "action"
and the other named "event_time".  The event_time field defaults to the
current time.  The bodies of the three trigger functions are:

insert into history select new.*, 'Insert'
insert into history select new.*, 'Update'
insert into history select old.*, 'Delete'

That way, I can track everything that happened in my source table.
Typically, there's something else, like maybe another trigger, that
deletes old records from the history table.

I hope this helps!

RobR

In response to

Responses

pgsql-admin by date

Next:From: A JDate: 2010-09-03 22:57:18
Subject: Re: Confused by 'timing' results
Previous:From: Kevin GrittnerDate: 2010-09-03 16:23:23
Subject: Re: fail-safe sql update triggers

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