Tracking table updates in SQL SERVER 2005

Inspecting 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 useful when debugging your database code.

SQL Server 2005 provides COLUMNS_UPDATED function for tracking which fields have been modified . This function is available with trigger. so we need to create a trigger on table whose data we want to inspect.

So now look at the example:

Step 1:

First we create a source table:

IF OBJECT_ID('ProductHistoryTable','U') > 0
DROP TABLE ProductHistoryTable
GO
CREATE TABLE ProductHistoryTable
(
ProductID INT IDENTITY(1,1),
ProductName NVARCHAR(30),
InsertDate DATETIME )
GO

Step 2:

Insert some data into this table:

INSERT INTO ProductHistoryTable( ProductName, InsertDate) VALUES ('Computer', GETDATE())

GO

Step 3:

Create Audit Table:

IF OBJECT_ID('HistoryAudit','U') > 0
DROP TABLE HistoryAudit
GO

CREATE TABLE HistoryAudit
(
AuditID INT IDENTITY(1,1),
ProductId INT,
ProductName NVARCHAR(30),
UpdateTime DATETIME,
ColumnsUpdated XML NULL
)
GO

Final Step :

Write a update trigger on ProductHistoryTable .

CREATE TRIGGER tr_ProductHistory ON ProductHistoryTable

FOR UPDATE

AS

BEGIN
DECLARE @FldsUpdated XML
DECLARE @ColumnsUpdated VARBINARY(100)


SET @ColumnsUpdated = COLUMNS_UPDATED()

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


INSERT INTO HistoryAudit
(ProductId, ProductName, UpdateTime, ColumnsUpdated)
SELECT ProductId, ProductName, GETDATE(), @FldsUpdated
FROM INSERTED

END

GO

Main catch is the use of sys.fn_IsBitSetInBitmask function in this trigger. 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 time to test the stuff:

execute update statement: Update ProductHistoryTable set ProductName = 'Computer Changed to Laptop' where ProductId=1

Results: