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