COLUMNS_UPDATED (Transact-SQL)

项目
12/15/2008

更新日期： 2006 年 4 月 14 日

返回 varbinary 位模式，它指示表或视图中插入或更新了哪些列。COLUMNS_UPDATED 可用于 Transact-SQL INSERT 或 UPDATE 触发器主体内部的任意位置，以测试该触发器是否应执行某些操作。

语法

COLUMNS_UPDATED ( )

返回类型

varbinary

备注

COLUMNS_UPDATED 针对多列执行的 UPDATE 或 INSERT 操作的进行测试。若要对一列的 UPDATE 或 INSERT 尝试进行测试，请使用 UPDATE()。

COLUMNS_UPDATED 返回一个或多个从左至右排序的字节，每个字节中最不重要的位位于最右侧。最左侧字节的最右侧位表示表中的第一列；向左的下一位表示第二列，依此类推。如果创建了触发器的表包含八列以上，则 COLUMNS_UPDATED 返回多个字节，最左侧的为最不重要的字节。在 INSERT 操作中 COLUMNS_UPDATED 将对所有列返回 TRUE，因为这些列插入了显式值或隐式 (NULL) 值。

若要测试针对特定列的更新或插入操作，请遵循使用位运算符和所测试列的整数位掩码的语法。例如，表 t1 包含列 C1、C2、C3、C4 和 C5。若要验证列 C2、C3 和 C4 是否已全部更新（使用具有 UPDATE 触发器的表 t1），请遵循使用 & 14 的语法。若要测试是否只更新了列 C2，请指定 & 2。

可以在 Transact-SQL INSERT 或 UPDATE 触发器内部的任意位置使用 COLUMNS_UPDATED。

注意：
在 SQL Server 2005 中，INFORMATION_SCHEMA.COLUMNS 视图的 ORDINAL_POSITION 列与 COLUMNS_UPDATED 所返回列的位模式不兼容。若要获取与 COLUMNS_UPDATED 兼容的位模式，请在查询 `INFORMATION_SCHEMA.COLUMNS` 视图时引用 `COLUMNPROPERTY` 系统函数的 `ColumnID` 属性，如以下示例所示：

SELECT TABLE_NAME, COLUMN_NAME,
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
    COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact';

示例

A. 使用 COLUMNS_UPDATED 测试表的前八列

以下示例创建两个表：employeeData 和 auditEmployeeData。人力资源部的成员可以修改 employeeData 表，该表包含敏感的雇员薪水信息。如果更改了雇员的社会保险号码 (SSN)、年薪或银行帐户，则生成审核记录并插入到 auditEmployeeData 审核表。

通过使用 COLUMNS_UPDATED()，可以快速测试对包含敏感雇员信息的列所进行的任何更改。仅当您尝试检测对表的前八列进行的更改时，以这种方式使用 COLUMNS_UPDATED() 才有效。

USE AdventureWorks;
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME = 'employeeData')
   DROP TABLE employeeData
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_NAME = 'auditEmployeeData')
   DROP TABLE auditEmployeeData;
GO
CREATE TABLE employeeData (
   emp_id int NOT NULL,
   emp_bankAccountNumber char (10) NOT NULL,
   emp_salary int NOT NULL,
   emp_SSN char (11) NOT NULL,
   emp_lname nchar (32) NOT NULL,
   emp_fname nchar (32) NOT NULL,
   emp_manager int NOT NULL
   );
GO
CREATE TABLE auditEmployeeData (
   audit_log_id uniqueidentifier DEFAULT NEWID(),
   audit_log_type char (3) NOT NULL,
   audit_emp_id int NOT NULL,
   audit_emp_bankAccountNumber char (10) NULL,
   audit_emp_salary int NULL,
   audit_emp_SSN char (11) NULL,
   audit_user sysname DEFAULT SUSER_SNAME(),
   audit_changed datetime DEFAULT GETDATE()
   );
GO
CREATE TRIGGER updEmployeeData 
ON employeeData 
AFTER UPDATE AS
/*Check whether columns 2, 3 or 4 have been updated. If any or all
 columns 2, 3 or 4 have been changed, create an audit record. The 
bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To test 
whether all columns 2, 3, and 4 are updated, use = 14 instead of >0
 (below).*/

   IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see whether all columns 2, 3, 
and 4 are updated.*/
      BEGIN
-- Audit OLD record.
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT 'OLD', 
            del.emp_id,
            del.emp_bankAccountNumber,
            del.emp_salary,
            del.emp_SSN
         FROM deleted del

-- Audit NEW record.
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT 'NEW',
            ins.emp_id,
            ins.emp_bankAccountNumber,
            ins.emp_salary,
            ins.emp_SSN
         FROM inserted ins
   END;
GO

/*Inserting a new employee does not cause the UPDATE trigger to fire.*/
INSERT INTO employeeData
   VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32);
GO

/*Updating the employee record for employee number 101 to change the 
salary to 51000 causes the UPDATE trigger to fire and an audit trail to 
be produced.*/

UPDATE employeeData
   SET emp_salary = 51000
   WHERE emp_id = 101;
GO
SELECT * FROM auditEmployeeData;
GO

/*Updating the employee record for employee number 101 to change both 
the bank account number and social security number (SSN) causes the 
UPDATE trigger to fire and an audit trail to be produced.*/

UPDATE employeeData
   SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
   WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO

B. 使用 COLUMNS_UPDATED 测试八个以上的列

若要对影响表中前八列以外的列的更新进行测试，请使用 SUBSTRING 函数测试 COLUMNS_UPDATED 返回的更正位。以下示例对影响 AdventureWorks.Person.Contact 表中的列 3、5 和 9 的更新进行测试。

USE AdventureWorks;
GO
IF OBJECT_ID (N'uContact2', N'TR') IS NOT NULL
    DROP TRIGGER Person.tr1;
GO
CREATE TRIGGER uContact2 ON Person.Contact
AFTER UPDATE AS
    IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20) 
        AND (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1) ) 
    PRINT 'Columns 3, 5 and 9 updated';
GO

UPDATE Person.Contact 
   SET Title=Title,
      MiddleName=MiddleName,
      EmailPromotion=EmailPromotion;
GO

请参阅

参考

位运算符 (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
UPDATE() (Transact-SQL)

帮助和信息

获取 SQL Server 2005 帮助

更改历史记录

版本	历史记录
2006 年 4 月 14 日	已更改的内容：更新了示例 B。