Data Management

Audit data using SQL Server 2005's COLUMNS_UPDATED function

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 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 chapman.tim@gmail.com.

About

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.

7 comments
philip.collins.aus
philip.collins.aus

This has been a lifesaver.  Many thanks.  I was using bit maths from another sample I found on the net but it failed on a wide table - 160 columns.  After a few months of use I realised that the bit maths wasn't working after column 141.  Too brain-dead to work it out myself, your code came to the rescue.  Saw a couple of other comments on the net about some mysterious column limit with the columns_updated function.  Hope they find this. fn_IsBitSetInBitmask seems to be the missing link in the other samples.

sanjeevopt
sanjeevopt

i have aretail database we maintain sale as sales persan wise ,but it showing wrongly report ,how i can solve this problem

MikeAinOz
MikeAinOz

Tim, Thanks for this, the bitflag 'bit' adds the missing piece of information to my audit tables, an attribution will be added to my triggers.

dmatson
dmatson

This solution works great. The problem I'm having is reading the UpdatedColumns field in the SalesHistoryAudit table. For example this what I have stored in that field. I've been trying everything to read that xml data but all I can get it to do is return null or blank. Do es anybody have any suggestions.

farrukh.amin
farrukh.amin

well i have a kind of different requirement if you could please suggest. i need to save the complete record as it was before any DML operation. I am making a history table for all the tables having the same schema but two additional fields having update on (date) and udated by (user id) and i need to insert the data in history table for each record updated in any table in the respective history table. thanks

Jay
Jay

Great piece! Ok, so this raises the question "How does one generate a list of changed columns wit their column name, original value and new value?"

ITEngineerGuy
ITEngineerGuy

Is there a way to tell when the last time a DB has been accessed by using a query, sp, etc? I do not want to browse the log files of over 82 databases. Thanks in advance

Editor's Picks