|
Home > Archive > PostgreSQL Discussion > December 2005 > Triggers and Audit Trail
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Triggers and Audit Trail
|
|
| Marcus Couto 2005-12-29, 1:23 pm |
| 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
| |
| Jeff Amiel 2005-12-29, 1:23 pm |
| >
> 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.
We (my company) never found a way. We ended up writing java code that
analyzed the catalog tables that generated the appropriate 'if'
statements in the trigger functions for us....
>
> 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?
Inside our application, when we grab a connection from our connection
pool, the user information is populated into a termporary table that the
audit triggers can then later read for any transactions on that
connection.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Jeff Amiel 2005-12-29, 1:23 pm |
|
> We (my company) never found a way. We ended up writing java code that
> analyzed the catalog tables that generated the appropriate 'if'
> statements in the trigger functions for us....
Actually....we tinkered with hitting the catalog tables inside our
triggers, but for performance reasons, we generated the 'if' statements
instead....
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Michael Fuhr 2005-12-29, 1:23 pm |
| On Thu, Dec 29, 2005 at 11:44:26AM -0600, Jeff Amiel wrote:
>
> We (my company) never found a way. We ended up writing java code that
> analyzed the catalog tables that generated the appropriate 'if'
> statements in the trigger functions for us....
As far as I know you can't do this yet in PL/pgSQL, but you can in
other languages like PL/Perl and PL/Tcl.
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
|
|
|
|