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