|What I have done is build an audit table:
id - unique id on this table
operation - insert,delete or update
table_name - name of table operation pertains to
col_name - name of the column operation pertains to
key_col - name of the key column operation pertains to
key_val - value of the key column operation pertains to
col_val_prior - value of col_name column before operation
col_val_new - value of col_name column after operation
username - name of user running operation
transaction_dt - datetime trigger was run
batch - guid that uniquely identifies an operation
I then created three triggers that I add to all tables to be audited(the triggers cover insert, update and delete operations).
This allows me to see all operations on tables I wish to audit.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens