Re: Triggers and Audit Trail

[Jeff Amiel <jamiel@xxxxxxxxxxxxxxxxxx>]

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