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.

Answer 3

As roufamatic said, you can either set up triggers specific to each action or you can check against the INSERTED and DELETED tables.

As for the deleting user, it is possible to pass that information into the trigger as long as the code in your application handles it. I encountered this requirement about a year ago with a client and the solution that I came up with was to use SET CONTEXT_INFO and CONTEXT_INFO() to pass the user name along. All of our database access was through stored procedures, so I just needed to add a line or two of code to the delete stored procedures to SET CONTEXT_INFO then I changed the delete triggers to get the user from CONTEXT_INFO(). The user name had to be passed as a parameter from the application of course. If you aren't using stored procedures you might be able to just do the SET CONTEXT_INFO in the application. I don't know how connection pooling might affect that method. Obviously, if someone does a delete outside of the application there wouldn't be a record of that unless you also separately captured the USERNAME() in your trigger (probably a good idea, although it wasn't necessary for our audit log, which was more for reporting than security).

There was a little bit of trickiness because CONTEXT_INFO is a binary string, but it didn't take long to get that all sorted out.

I'm afraid that I don't have any of the code handy since it was for a past client. If you run into any problems after going through the help for CONTEXT_INFO and SET CONTEXT_INFO then feel free to post here and I'll see what I can remember.

Answer 4

To find out what action is being taken you can use the INSERTED and DELETED tables to compare before and after values. There is no magic way to tell which user of a web app has made a change. The usual method is to have a modified column in your table and have the web app code populated this with the relevant username