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.