Trevor,
We've implemented something like what you're talking about, and
believe me, knowing what changes were made and who made them is a BIG
benefit. You can undo changes months later if you need to.
By no means am I an expert so don't automatically take this to be the
best way. It's just what we've come up with.
We initially had update triggers that checked the Deleted value
against the Inserted value for each column and if they didn't match,
then we added a record to the trace table. The IF logic (if
Update(ColumnA) , If Update(ColumnB) , etc...) in the trigger slowed
things down too much. We now have intermediate tables which are
basically duplicates of the base tables we want to trace, with a few
extra fields thrown in. In our Update trigger we don't check anything,
we just copy both the Inserted row and the Deleted row into the
intermediate table. Then in a job that runs once a night, we check
each set of Inserted and Deleted values in the intermediate table, and
if they don't match, we throw this information into the trace table.
This keeps the trigger processing in the base table to a minimum.
There are some other things that we do to ease the maintenance of
keeping these trace tables, but this is the basic structure of how
we're handling it, and right now it seems to work pretty good for us.
Hope this helps,
Gary
tr*****@e-crime.on.ca (Trevor Fairchild) wrote in message news:<5b******* *************** ****@posting.go ogle.com>...
I am trying to create a very minimal auditing system for a series of
databases.
I am in the process of writing Update triggers for 5 Tablse. I will
write a trigger for each table-the trigger's function will be to
INSERT a row into my MasterChanges table everytime ANY data is changed
in each of the 5 tables.
I have set up MasterChanges to capture the following:
what Table the change was made in
what Field the change was made in
what the Field was changed to
when this change occured.
I have used the Columns_Update( ) function to enable the trigger to
survey all 22 fields in the first table (my first attempt at this). I
can hardcode in the table's name, as there will be a separate trigger
for each table.
The problem is how to identify the NAME of the COLUMN that was updated
and have the trigger write that NAME into MasterChanges, as well as
the new value of that column.
My first thought is a very unacceptable hardcoding of multiple IF
STATEMENTS for each of the 22 fields.
ie:
IF(colA) then...elseif(c olB) then...elseif(c olC) then...etc.
There must be a better way to do it
I assume this would be done using Columns_Update( ) in some way, but
cannot figure it out.
Can anyone help?
Thanks,
Trevor Fairchild
