在工作中，对数据改变情况进行审计是很重要的，尤其是正在处理的机密信息。除了跟踪被改变的数据之外，跟踪单个字段名称的改变也十分有用。

这些信息对审计部门尤其重要，而且当你调试数据库代码时，也十分有用。SQL Server 2005提供了两个函数来跟踪改变的字段：UPDATE 和COLUMNS_UPDATE。

UPDATE

这个TSQL触发函数接受一个字段名参数，返回一个布尔值来表明指定的字段是否包含在调用该触发函数的update语句中。当调用一个Insert语句时，该函数总是返回真。应用该函数的形式如下：

IF UPDATE(FieldName)
BEGIN
--work to do

END

如果你仅仅想知道当某些字段更新后的变化，那么这个函数足以完成任务。但是，如果你想记录哪些字段包含在你的update语句中，那又怎么样呢？仅用UPDATE函数编写能够确定哪些字段包含在update语句中的代码，这将很困难。COLUMNS_UPDATED函数就是为实现这一目的提供的。

COLUMNS_UPDATED

这个TSQL函数只能通过触发器获得，它返回包含在update语句中字段的二进制掩码。该update语句基于数据表中列的顺序。然而，编写处理二进制值并分析每位数据的代码是需要技巧的。不过，在SQL Server 2005中提供了一个系统函数将使你的工作变得更容易。首先，我们看一下COLUMNS_UPDATED函数的工作机制，然后，我们将讨论如何解析出字段名称。

列表A显示了如何创建一个数据表并为它添加一条记录。列表B创建审计表，用来保存数据改变情况。列表C在SalesHistory数据表上创建触发器，这个触发器真正实现的是sys.fn_IsBitSetInBitmask函数，它从INFORMATION_SCHEMA视图接受位掩码（就是从COLUMNS_UPDATED函数返回的二进制值）和列号，并为数据表中包含在位掩码中的每个字段返回一个非零值。然后，触发器中包含在update语句中的字段名映射为一个XML文档，从而可以将更新的字段保存在审计表中。

触发器设置之后，让我们运行一个update语句，该语句将更新我们的数据表中的一个值。然后，查看审计表，就可以来查看是否捕捉到了update语句中的字段。

UPDATE TOP(1) SalesHistory
SET SalePrice = SalePrice + 1
GO
SELECT * FROM SalesHistoryAudit

SQL Server 2000用户需要注意的地方

sys.fn_IsBitSetInBitmask是SQL Server 2005新增加的函数，因此在SQL Server 2000中不能获得。不过你可以复制SQL Server 2005中的脚本，然后在SQL Server 2000中创建你自己的函数。这样你就可以在SQL Server 2000的机器上使用定制审计解决方案了。

Tim Chapman是SQL Server数据库管理员，在美国肯塔基州路易斯维尔德一家银行工作，有7年多的IT经验。同时，他还获得微软的SQL Server 2000 和SQL Server 2005认证。

责任编辑：德东

查看本文国际来源

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

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

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