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
