Re: Database Design: Maintain Audit Trail of Changes

Hi Rich,

if it's a strict legal requirement you may want to enforce it with a
trigger system, so that each time a record is inserted/updated/deleted
you create an exact copy of it in a historical table, that has the
original record plus data about who performed the operation, when,
from which IP, maybe a comment field, etc. So your actual table
remains limited in size and it's performing well, while the size
problem is local to the audit logs.

You also want to use triggers to disable updates and deletes on this
historical table, for a matter of additional security (you might end
up needing a procedure to trim it, however, if it grows out of
affordable bounds).

Cheers
Bèrto

On 3 January 2013 16:52, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
> On Thu, 3 Jan 2013, Adrian Klaver wrote:
>
>> As a matter of course I include fields to record the timestamp and user
>> for insert of records and last update of record on my tables.
>
>
> Adrian,
>
> This is a useful addition to the application.
>
>
>> For a relatively simple solution see this blog post I put up this summer:
>> http://aklaver.org/wordpress/2012/06/23/postgres-and-hstore/
>> I have since expanded that to include updates by using TG_OP to determine
>> the operation being done on the table.
>>
>> There is also pg_audit
>> :
>> https://github.com/jcasanov/pg_audit
>
>
> Once again you came through with valuable advice and guidance.
>
> Many thanks!
>
> Rich
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.