Audit data using SQL Server 2005's COLUMNS_UPDATED function

Audit data using SQL Server 2005's COLUMNS_UPDATED function

By Tim Chapman, TechRepublic | 2008/09/18 11:38:01

Tags: function, sql server 2005

 

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.

The code below shows how to create a table and add a record to it.

IF OBJECT_ID('SalesHistory','U') > 0
DROP TABLE SalesHistory
GO
CREATE TABLE SalesHistory
(
SaleID INT IDENTITY(1,1),
Product VARCHAR(30) NOT NULL,
SaleDate DATETIME,
SalePrice MONEY
)
GO

INSERT INTO SalesHistory ( Product, SaleDate, SalePrice)
VALUES
('Computer', DATEADD(mm, 55, '3/11/1919'), DATEPART(ms, GETDATE()) + (57) )
GO

The code below creates the audit table, which I will use to house the data changes.

IF OBJECT_ID('SalesHistoryAudit','U') > 0
DROP TABLE SalesHistoryAudit
GO
CREATE TABLE SalesHistoryAudit
(
AuditID INT IDENTITY(1,1),
SaleID INT,
Product VARCHAR(30) NOT NULL,
SaleDate DATETIME,
SalePrice MONEY,
ColumnsUpdated XML NULL
)
GO

The code below creates the trigger on your SalesHistory table.

CREATE TRIGGER tr_SalesHistory ON SalesHistory
FOR UPDATE
AS
BEGIN
DECLARE @FldsUpdated XML
DECLARE @ColumnsUpdated VARBINARY(100)

SET @ColumnsUpdated = COLUMNS_UPDATED()

SET @FldsUpdated =
(
SELECT COLUMN_NAME AS Name
FROM TRS.INFORMATION_SCHEMA.COLUMNS Field
WHERE
TABLE_NAME = 'SalesHistory' AND
sys.fn_IsBitSetInBitmask
(
@ColumnsUpdated,
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')
) <> 0
FOR XML AUTO, ROOT('Fields')
)

INSERT INTO SalesHistoryAudit
(SaleID, Product, SaleDate, SalePrice, ColumnsUpdated)
SELECT SaleID, Product, SaleDate, SalePrice, @FldsUpdated
FROM INSERTED
END
GO

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.