Auditing data changes in your production environment is very important, especially if you are dealing with confidential information. Besides tracking the data that has been modified, it is also useful to track the individual field names that have been modified. This information is great for your auditing department, and it is very useful when debugging your database code. SQL Server 2005 provides two functions for tracking which fields have been modified: UPDATE and COLUMNS_UPDATED.
This TSQL trigger function accepts a field name and returns a Boolean value indicating if the specified field has been included in the update statement that invoked the trigger. This function will always return a true value when an Insert statement is invoked. The form of usage for this function is:
BEGIN --work to do
This function works great if you are specifically only looking for actions to be taken when certain fields are updated. But, what if you want to record which fields were included in your update statement? It would be very difficult to write code that would determine the set of fields included in your update statement using only the UPDATE function. The COLUMNS_UPDATED function has been provided for just such a purpose.
This TSQL function, which is only available through triggers, returns a binary mask of the fields that were included in your update statement based upon the column order of the table. However, writing code that takes a binary value and parses out the individual bits is tricky. A system function has been included in SQL Server 2005 that will make your job much easier. First, I'll look at how the COLUMNS_UPDATED function works, and then I'll discuss how to parse out the field names.Listing A shows how to create a table and add a record to it. Listing B creates the audit table, which I will use to house the data changes. Listing C creates the trigger on your SalesHistory table. The real power of this trigger is the sys.fn_IsBitSetInBitmask function. It accepts a bitmask value (which is the bitmask returned from our COLUMNS_UPDATED function) and the columnid from the INFORMATION_SCHEMA view and returns a non-zero value for each field in the table that is contained in the bitmask. Our trigger then casts the field names that were included in the update statement as an XML document so that we can store which fields were updated in our audit table.
Now that our trigger is in place, let's run an update statement that updates a value in our table. We can then check our audit table to see if we caught the fields from our update statement.
UPDATE TOP(1) SalesHistory
SET SalePrice = SalePrice + 1 GO
SELECT * FROM SalesHistoryAudit
Note to SQL Server 2000 users
The sys.fn_IsBitSetInBitmask is new to SQL Server 2005, so it isn't available in SQL Server 2000. However, you can copy the script from SQL Server 2005 and create your own function in SQL Server 2000. This will allow you to use this custom auditing solution on SQL Server 2000 machines.
Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at firstname.lastname@example.org.
Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.