Thread: 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)

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)
>