COLUMNS_UPDATED （Transact-SQL）

2025-05-20

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

此函式會傳回 varbinary 位元模式，指出資料表或檢視的已插入或更新資料行。使用 COLUMNS_UPDATED Transact-SQL INSERT 或 UPDATE 觸發程式主體內的任何位置來測試觸發程式是否應該執行特定動作。

Transact-SQL 語法慣例

語法

COLUMNS_UPDATED ( )

傳回類型

varbinary

備註

COLUMNS_UPDATED測試或UPDATEINSERT對多個數據行執行的動作。若要測試 UPDATE 或 INSERT 嘗試資料行，請使用 UPDATE（）。

COLUMNS_UPDATED 會傳回從左到右排序的一或多個位元組。每個位元組的最右邊位元都是最不重要的位元。最左邊位元組的最右邊位元代表資料表的第一個資料表資料行，而左邊的下一個位元代表第二個資料行，依此類推。如果建立觸發程序的資料表包含超出八個資料行，則 COLUMNS_UPDATED 會傳回多個位元組，而最不重要的位元組在最左邊。 COLUMNS_UPDATED TRUE會針對動作中的所有INSERT數據行傳回，因為數據行已插入明確值或隱含（NULL）值。

若要進行特定資料行的更新或插入測試，請遵循含有位元運算子和所測試資料行之整數位元遮罩的語法。例如，假設資料表t1包含資料列C1、、C2C3、、 C4和 C5。若要確認資料列C2、 C3與 C4 都已成功更新（具有觸發程式的數據表t1UPDATE），請遵循語法搭配 & 14。若要測試是否只更新資料列 C2 ，請指定 & 2。如需實際範例，請參閱範例 A 和範例 B。

在 Transact-SQL COLUMNS_UPDATED 或INSERT觸發程式內的任何位置使用UPDATE。如果這是在觸發程式外部執行，則會傳回 NULL。

檢視 ORDINAL_POSITION 的數據 INFORMATION_SCHEMA.COLUMNS 行與所 COLUMNS_UPDATED傳回之數據行的位模式不相容。若要取得與 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，因此數據行集中的未變更數據行將會出現修改。如需資料行集的詳細資訊，請參閱使用資料行集。

範例

A。使用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

共用方式為

COLUMNS_UPDATED （Transact-SQL）

語法

傳回類型

備註

資料行集

範例

A。 使用COLUMNS_UPDATED測試數據表的前八個數據行

B. 使用COLUMNS_UPDATED測試八個以上的數據行

相關內容

意見反應

其他資源

A。使用COLUMNS_UPDATED測試數據表的前八個數據行