> So, if anyone out there has an effective alternative, I would love to
> hear about it.
The way I do it is to create a copy of the table I want to track and add
a text column for the user name and a timestamp column.
I then set up an on update trigger on the original table that does the
following:
insert into xxxx_log select * from xxxx where keyfield = NEW.keyfield;
I also set up an on insert trigger on the log table that adds the timestamp
and the user ID (from session_user).
The big negative is that if you add columns to the original table, you
have to fiddle with the log table to make sure you keep the username and
timestamp columns after all the columns in the original table and keep the
columns in sync with the original table as to both size and order in which
they appear, or you'll get errors.
Pulling the data out of the log table can be a bit more, because it has
the OLD data but not the NEW data. But you know what the values were,
who changed them and when, and you can check the original table to see
what the current value is. (If there are multiple changes, you
have to check the next one in timestamp order, of course.)
One of the nicer aspects is that because this is done at the trigger
level, the user does NOT have to have any access to the log table,
the trigger can use SECURITY DEFINER. That way you get full control
over who can even look at the log.
--
Mike Nolan
