Re: strategies for keeping an audit trail of UPDATEs

What you describe is what we do.  Full history of all actions in the
data tables are stored elsewhere via a trigger on INSERT, UPDATE /
DELETE and a generic function written in C (to get the transaction ID
they were a part of for postdated rollbacks or transactions where
applicable -- unmodified since).
--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
----- Original Message -----
From: "Louis-David Mitterrand" <cunctator@apartia.ch>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, February 20, 2001 12:27 PM
Subject: [GENERAL] strategies for keeping an audit trail of UPDATEs


> Hello,
>
> In our app we must keep a trace of all changes (UPDATEs) done to an
> important_table, so that it's possible to get a snapshot of a given
> record at a given date.
>
> The implementation strategy we are thinking about:
>
> 1. create an important_table_archive which inherits from
> important_table,
>
> 2. create a trigger ON UPDATE of important_table which automatically
> creates a record in important_table_archive containing only the
UPDATEd
> fields on the original record along with the modification date and
> author and the primary key,
>
> Is this a viable strategy for that kind of requirement? Is there a
> better, more orthodox one?
>
> Thanks in advance,
>
> --
>     PANOPE: Déjà même Hippolyte est tout prêt à partir ;
>             Et l'on craint, s'il paraît dans ce nouvel orage,
>             Qu'il n'entraîne après lui tout un peuple volage.
>                                           (Phèdre, J-B Racine, acte
1, scène 4)
>