Triggers and Audit Trail

From: "Marcus Couto" <marcus(at)altapoint(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Triggers and Audit Trail
Date: 2005-12-29 17:31:14
Message-ID: 003c01c60c9d$ab543860$bc00000a@programmer2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all. I'm new with PostgreSQL and this is my first post, so easy on me... :)

I'm thinking of using the native procedural language and triggers to keep an audit trail. For editing changes, we only keep a log of the modified fields and we create a record for each modified value. The audit table record holds information like user, date/time, table_name, field_name, old_value, new_value, type(delete, new, edit). I have a couple of questions:

Using triggers, is there a way to loop through the fields of the OLD and NEW records? I haven't found a generic way to get the field name and value that triggered the update other than hard coding if statements to compare every field of the OLD and NEW records.

Another issue is how to keep track of the audit user since we share the same postgres user and our application keeps track of the actual current user locally. Is there some kind of way we can set the current user so that we're able to read it from the trigger event? Other suggestions?

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John DeSoi 2005-12-29 17:35:08 Re: new beginner to postgresql. Looking at it for a church
Previous Message Marc Munro 2005-12-29 17:22:09 Re: I want to know how to improve the security of postgresql