通过

COLUMNS_UPDATED（Transact-SQL）

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

此函数将返回 varbinary 位模式，它指示表或视图中已插入或已更新的列。 使用 COLUMNS_UPDATED Transact-SQL INSERT 或 UPDATE 触发器正文内的任意位置来测试触发器是否应执行某些作。

Transact-SQL 语法约定

语法

COLUMNS_UPDATED ( )

返回类型

varbinary

备注

COLUMNS_UPDATED测试或INSERT对UPDATE多个列执行的作。 若要测试UPDATE或尝试对一列进行测试或INSERT尝试，请使用 UPDATE（）。

COLUMNS_UPDATED 将按从左到右的顺序返回一个或多个字节。 每个字节的最右侧位是最低有效位。 最左侧字节的最右侧位表示表中的第一表列；向左的下一位表示第二列，依此类推。 如果创建了触发器的表中包含八列以上，COLUMNS_UPDATED 则将返回多个字节，最低有效字节位于最左侧。 COLUMNS_UPDATED 返回 TRUE 作中的所有 INSERT 列，因为插入了显式值或隐式 （NULL） 值。

若要检测针对特定列的更新或插入操作，请使用按位运算符和已测试列的整数位掩码的语法。 例如，假设该表t1包含列C1、C2、C3和C4C5。 若要验证列C2以及C4C3所有成功更新（具有触发器的表t1UPDATE），请遵循语法。& 14 若要测试是否只更新列 C2 ，请指定 & 2。 有关实际示例，请参阅示例 A 和示例 B。

在 Transact-SQL INSERT 或UPDATE触发器内的任意位置使用COLUMNS_UPDATED。 如果在触发器外部运行，则返回 NULL。

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

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

如果将触发器应用于列，COLUMNS_UPDATED 将返回为 true 或 1，即使列值保持不变也是如此。 这是有意为之，并且触发器应实现确定是否允许插入/更新/删除操作的业务逻辑。

列集

对表定义了列集后，COLUMNS_UPDATED 函数的行为将如下所述：

  • 显式更新列集的成员列时，该列的相应位设置为 1，列集位设置为 1。

  • 显式更新列集时，列集位设置为 1，该表中所有稀疏列的位都设置为 1。

  • 对于插入作，所有位都设置为 1。

    由于对列集的更改会导致列集中所有列的位重置为 1，因此列集中未更改的列将显示修改。 有关列集的详细信息，请参阅使用列集。

示例

答： 使用COLUMNS_UPDATED测试表的前八列

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

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

USE AdventureWorks2022;
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 dbo.employeeData
(
    emp_id INT NOT NULL PRIMARY KEY,
    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 dbo.auditEmployeeData
(
    audit_log_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    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 dbo.updEmployeeData
ON dbo.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.
This bitmask translates into base_10 as: 2 + 4 + 8 = 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 dbo.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 AS del;
    -- Audit NEW record.
        INSERT INTO dbo.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 AS 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 dbo.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 dbo.employeeData
SET emp_bankAccountNumber = '133146A0',
    emp_SSN = 'R-M53550M'
WHERE emp_id = 101;
GO

SELECT * FROM dbo.auditEmployeeData;
GO

B. 使用COLUMNS_UPDATED测试八列以上

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

USE AdventureWorks2022;
GO

IF OBJECT_ID(N'Person.uContact2', N'TR') IS NOT NULL
    DROP TRIGGER Person.uContact2;
GO

CREATE TRIGGER Person.uContact2
    ON Person.Person
    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.Person
    SET NameStyle = NameStyle,
        FirstName = FirstName,
        EmailPromotion = EmailPromotion;
GO

相关内容