Re: Audit Trigger puzzler

 > Most of the time, my application will set the edited_by field to
 > reflect an application username (i.e., the application logs into the
 > database as a database user, and that's not going to be the
 > application user) So I log into my application as "Dave", but the
 > application connects to the database as "dbuser".

 > If the app doesn't specifically send an "edited_by" value in it's
 > update, then I want to default that value to the database user.

 > This would also be good for auditing any manual data changes that
 > could happen at the psql level.

In Oracle, the way we handle audit triggers is by using Package
Variables.  We emulate some of that functionality in postgresql by
adding a custom variable to the configuration file:

custom_variable_classes = 'mysess'

Then, whenever a user logs into the application, my login procedure
calls this function:

CREATE OR REPLACE FUNCTION begin_sess(staffid character varying)
   RETURNS void AS $BODY$ BEGIN
PERFORM set_config('mysess.curr_user', coalesce(staffid,''), false);
END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;

This makes the current application user automatically available to every
       function, including triggers.  Then, in your triggers, you can do
this:

DECLARE
     curr_user    staff.staff_id%TYPE;
BEGIN
     SELECT current_setting('mysess.curr_user') INTO curr_user;


In your trigger, you could check that this variable was unset, and fall
back to the database user.


HTH.