Audit Trigger puzzler

From: David Kerr <dmk(at)mr-paradox(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Audit Trigger puzzler
Date: 2009-08-28 15:50:28
Message-ID: 4A97FCC4.5030004@mr-paradox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

all of my tables have 4 fields
edited_by
edited_date
created_by
created_date

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.

my trigger was essentially

if NEW.edited_by is null then
edited_by = :current_user
end if

but, unfortunately, i didn't realize that in an update, the NEW
variables contains a full record, so edited_by will never be null.

If i do
if NEW.edited_by = OLD.edited_by
edited_by = :current_user
end if

then, if i do 2 updates to edited_by in a row, i get the DB user instead
of the user i was intending to update.

so, is there a way in a trigger to know if edited_by is expressly being
set in the update statement? it seems like if I can know that, then i
should be able to figure it out.

Thanks

Dave

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sébastien Lardière 2009-08-28 15:54:22 Re: [Skytools-users] WAL Shipping + checkpoint
Previous Message Martin Gainty 2009-08-28 15:22:26 Re: Anybody know where to find Dan Langille?