audit tracking on Sql Server

Here's the challenge. You've got a database. In that database, you've got a table (say T). In that table, there are some columns -- say A, B, & C. If column B changes, you want to log that change somewhere.

You've got a couple issues. First, you don't care about columns A or C. If they change, you don't want to log the change. So how do you know when it's column B that changes?

Next, you only want to log the change if it actually *changes*. So if the value of B is x and someone does "UPDATE T SET B='x'", you don't want to log that, since it's not really changing the value ,but just setting it to what it already was.

In Sql Server, you can do this easily with a table trigger, but how you do it is a bit whacked.

First you need to create the trigger, of course. Then we'll need 2 variables -- one for the old value and one for the new:

CREATE TRIGGER somename

ON T

AFTER UPDATE

AS

BEGIN

DECLARE @OldValue integer

DECLARE @NewValue integer

Note that the trigger actually fires after the update. I did this because, in this case, the trigger need not be transactional -- I don't want to rollback the change just because I can't audit it. There may be cases where this isn't true, of course, but it will due for what I need.

Next, you'll need to use this: COLUMNS_UPDATED()

COLUMNS_UPDATED() will show you the columns that got updated by the update statement that fired the trigger, but the weird part is that it returns it as a bit mask. If you don't know what a bit mask is and don't want to learn, you can use a built in function to help. The column I'm looking for (B) has a corresponding bit in the bitmask. You can look to see if it gets set, but the way bit masks work, it's not quite that simple. Changing columns A or C at the same time as B, will create a different bit mask value. So it's not as simple as " if COLUMNS_UPDATED() == x20000" or something.

Because it's been like a hundred years since I've had to bit shift, I' m going to suggest using Microsoft's built in function to look for this value. It is:

sys.fn_IsBitSetInBitmask()

So, the syntax:

IF (sys.fn_IsBitSetInBitmask(COLUMNS_UPDATED(), 2) >0 )

will return true if the 2nd column (in this case "B") has been set, regardless of whether A or C have.

But what it doesn't tell is if the value was actually *changed*. This if statement will return true anytime the column is "Set" in an update statement.

But it's pretty easy to check, although it takes a few extra lines:

SET @OldValue = (SELECT B FROM deleted);

SET @NewValue = (SELECT B FROM inserted);

"deleted" and "inserted" are in-memory tables that hold the values of the update before and after the update itself. "Inserted" holds the new values, "deleted" holds the old ones. Since it's in memory, it's pretty quick. In my case, updates to the table will only happen one row at a time. If you're talking about mass-updates, you need to be careful that performance won't suffer. On the other hand, it is an in-memory query, so the numbers have to get pretty big before that's an issue. If you're doing such bulk transactions, you probably have other issues anyway.

With this, the old and new values can just be compared:

IF (@OldDisp <> @NewDisp)

To insert them into the audit table, just pull the values from either "inserted", "deleted" or both, depending on what it is you're looking to audit.

INSERT INTO MyAudit (Value, AuditType)

Select B, 'OLD' from deleted;

or

INSERT INTO yAudit (Value, AuditType)

Select B, 'NEW' from deleted;

for the old and new values.

That's pretty much it.

You'll want to add some error handling. And be careful about this. If the error gets thrown, it probably means that you can't insert into a database table. If that's the case, you may not be able to insert into any (out of space or whatever). So you may want to log the issue to system logs

(EXEC XP_LOGEVENT @ErrorNumber, @ErrorMessage, @ErrorSeverity )

or RAISE the error.

What's cool about this, is that it's database centric. If anyone modifies anything, you have it -- whether it's a service that does it, a scheduled task, or even an individual user with database privs. You can pull off the user id that initiated the update, if that's helpful, but keep in mind that the scheduled tasks and services probably all have a shared account.

--kevin