SQL trigger for audit table

Question

I am writing a trigger to audit updates and deletes in tables. I am using SQL Server 2008

My questions are,

Is there a way to find out what action is being taken on a record without going through the selection phase of the deleted and inserted tables?

Another question is, if the record is being deleted, how do I record within the audit table the user that is performing the delete. (NOTE: the user connected to the database is a general connection string with a set user, I need the user who is logged into either a web app or a windows app)

Please help?

Answer 1

For part one, you can either set up separate triggers or have one trigger that checks the special tables INSERTED and DELETED to discriminate between updates and deletes.

For part two, there's no way around it in this case, you're going to have to get that username to the database somehow via your web/windows app. Unfortunately you can't communicate with the trigger itself, and with a generic connection string the DB doesn't have any idea who it's dealing with.

I've found that it can be helpful to add a "LastModifiedBy" column to the tables that you plan to audit so that you can store that info on the original tables themselves. Then your trigger just copies that info into the audit table. This is also nice because if you only need to know who the last person to touch something was you don't have to look in the audit table at all, just check that one column.

Answer 2

Consider this, if you don't actually delete records but add a field to mark them as deleted, you can get the user from the last modified. If you want to actually delete records then, you can have a nightly job that deletes in a batch not one at time. This could even be set up to flag if too many records are being deleted and not run.

The easiest way to do this so that nothing breaks is to rename the table, add the column IsDeleted as a bit field and then create a view with the same name the table was orginally called. The view will select all the records where isdelted is null.

Don't let anyone talk you out of using triggers for this. You don't want people who are doing unauthorized changes to be able to escape the auditing. With a trigger (and no rights to anyone except a production dba to alter the table in any way), then no one except the dba can delete without being audited. In a typical system with no stored procedures to limit direct table access, all too many people can usually directly affect a table opening it wide for fraud. People committing fraud do not typically use the application they are supposed to use to change the data. You must protect data at the database level.

When you write your triggers make sure they can handle multi-row inserts/updates/deletes. Triggers operate on the whole set of data not one row at a time.