Audit data using SQL Server 2005's COLUMNS_UPDATED function

by Tim Chapman | Apr 16, 2007 7:00:00 AM

Tags: Databases, Enterprise software, Tim Chapman, sys.fn_IsBitSetInBitmask, Microsoft SQL Server, COLUMNS_UPDATED, trigger, update statement, Microsoft SQL Server 2005, audit, SQL Server Newsletter

Takeaway: DBA Tim Chapman looks at how the SQL Server 2005 COLUMNS_UPDATED function works and then discusses how to parse out the field names.

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.

UPDATE

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:

IF UPDATE(FieldName)

BEGIN
       --work to do

END

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.

COLUMNS_UPDATED

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 is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Print/View all Posts Comments on this article

QuestionITEngineerGuy  | 04/17/07

RE: Audit data using SQL Server 2005's COLUMNS_UPDATED functionJay@...  | 12/03/07

RE: Audit data using SQL Server 2005's COLUMNS_UPDATED functionfarrukh.amin@...  | 12/07/07

RE: Audit data using SQL Server 2005's COLUMNS_UPDATED functiondmatson@...  | 02/14/08