Re: Database Design: Maintain Audit Trail of Changes

On 01/03/2013 07:38 AM, Rich Shepard wrote:

>
> The middleware of the application needs to check this table when data
> are
> to be viewed in the UI and present only the current row contents. A
> separate
> view would display a history of changes for that row.
>
> All thoughts, suggestions, and recommendations based on your
> expertise and
> experience will be most welcome.

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.

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

>
> TIA,
>
> Rich
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com