利用SQL Server 2005 COLUMNS_UPDATED函数进行数据审计

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