COLUMNS_UPDATED (Transact-SQL)COLUMNS_UPDATED (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

此函式會傳回 varbinary 位元模式,指出資料表或檢視的已插入或更新資料行。This function returns a varbinary bit pattern indicating the inserted or updated columns of a table or view. Transact-SQLTransact-SQL INSERT 或 UPDATE 觸發程序主體內的任何位置使用 COLUMNS_UPDATED,來測試觸發程序是否應該執行特定動作。Use COLUMNS_UPDATED anywhere inside the body of a Transact-SQLTransact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

COLUMNS_UPDATED ( )   

傳回類型Return types

varbinaryvarbinary

RemarksRemarks

COLUMNS_UPDATED 會測試多個資料行所執行的 UPDATE 或 INSERT 動作。COLUMNS_UPDATED tests for UPDATE or INSERT actions performed on multiple columns. 若要在一個資料行上測試 UPDATE 或 INSERT 作業,請使用 UPDATE()To test for UPDATE or INSERT attempts on one column, use UPDATE().

COLUMNS_UPDATED 會傳回從左到右排序的一或多個位元組。COLUMNS_UPDATED returns one or more bytes that are ordered from left to right. 每個位元組的最右邊位元都是最不重要的位元。The rightmost bit of each byte is the least significant bit. 最左邊位元組的最右邊位元代表資料表的第一個資料表資料行,而左邊的下一個位元代表第二個資料行,依此類推。The rightmost bit of the leftmost byte represents the first table column in the table, the next bit to the left represents the second column, and so on. 如果建立觸發程序的資料表包含超出八個資料行,則 COLUMNS_UPDATED 會傳回多個位元組,而最不重要的位元組在最左邊。COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than eight columns, with the least significant byte being the leftmost. COLUMNS_UPDATED 會針對 INSERT 動作中的所有資料行傳回 TRUE,因為資料行已插入明確值或隱含 (NULL) 值。COLUMNS_UPDATED returns TRUE for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

若要進行特定資料行的更新或插入測試,請遵循含有位元運算子和所測試資料行之整數位元遮罩的語法。To test for updates or inserts to specific columns, follow the syntax with a bitwise operator and an integer bitmask of the tested columns. 例如,假設 t1 資料表包含 C1C2C3C4C5 資料行。For example, say that table t1 contains columns C1, C2, C3, C4, and C5. 若要確認已全部成功更新 C2C3C4 資料行 (t1 資料表具有 UPDATE 觸發程序),請遵循含有 & 14 的語法。To verify that columns C2, C3, and C4 all successfully updated (with table t1 having an UPDATE trigger), follow the syntax with & 14. 若要測試是否僅有 C2 獲得更新,請指定 & 2To test whether only column C2 is updated, specify & 2. 如需實際範例,請參閱範例 A範例 BSee Example A and Example B for actual examples.

Transact-SQLTransact-SQL INSERT 或 UPDATE 觸發程序內的任何位置使用 COLUMNS_UPDATEDUse COLUMNS_UPDATED anywhere inside a Transact-SQLTransact-SQL INSERT or UPDATE trigger.

INFORMATION_SCHEMA.COLUMNS 檢視的 ORDINAL_POSITION 資料行與 COLUMNS_UPDATED 所傳回之資料行的位元模式不相容。The ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED. 若要取得與 COLUMNS_UPDATED 相容的位元模式,請在查詢 INFORMATION_SCHEMA.COLUMNS 檢視時,依下列範例所示來參考 COLUMNPROPERTY 系統函式的 ColumnID 屬性。To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when querying the INFORMATION_SCHEMA.COLUMNS view, as shown in the following example.

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

如果觸發程序套用至資料行,則 COLUMNS_UPDATED 會傳回 true1,即使資料行的值維持不變也是如此。If a trigger applies to a column, the COLUMNS_UPDATED returns as true or 1, even if the column value remains unchanged. 這是根據設計的行為,且觸發程序應該實作商務邏輯,以決定是否可允許插入/更新/刪除作業。This is by-design, and the trigger should implement business logic that determines if the insert/update/delete operation is permissible or not.

資料行集Column sets

針對資料表定義資料行集之後,COLUMNS_UPDATED 函式就會遵循下列方式運作:When a column set is defined on a table, the COLUMNS_UPDATED function behaves in the following ways:

  • 明確更新資料行集的成員資料行時,該資料行的對應位元會設定為 1,而資料行集位元設定為 1。When explicitly updating a member column of the column set, the corresponding bit for that column is set to 1, and the column set bit is set to 1.

  • 明確更新資料行集時,資料行集位元會設定為 1,而該資料表中所有疏鬆資料行的位元會設定為 1。When a explicitly updating a column set, the column set bit is set to 1, and the bits for all of the sparse columns in that table are set to 1.

  • 若為插入作業,所有位元都會設定為 1。For insert operations, all bits are set to 1.

    因為資料行集變更會導致資料行集中所有資料行的位元都重設為 1,所以資料行集中未變更的資料行會看起來像已修改過。Because changes to a column set cause the bits of all columns in the column set to reset to 1, unchanged columns in a column set will appear modified. 如需資料行集的詳細資訊,請參閱使用資料行集See Use Column Sets for more information about column sets.

範例Examples

A.A. 利用 COLUMNS_UPDATED 來測試資料表的前八個資料行Using COLUMNS_UPDATED to test the first eight columns of a table

此範例會建立兩份資料表:employeeDataauditEmployeeDataThis example creates two tables: employeeData and auditEmployeeData. employeeData 資料表保留機密的員工薪資資訊,而人力資源部門成員可以修改這項資訊。The employeeData table holds sensitive employee payroll information and human resources department members can modify it. 如果員工的社會保險號碼 (SSN)、年薪或銀行帳戶變更,就會產生一筆稽核記錄,並將其插入 auditEmployeeData 稽核資料表。If the social security number (SSN), yearly salary, or bank account number for an employee changes, an audit record is generated and inserted into the auditEmployeeData audit table.

使用 COLUMNS_UPDATED() 函式,即可快速測試包含對機密員工資訊的資料行進行的任何變更。With the COLUMNS_UPDATED() function, we can quickly test for any changes made to columns containing sensitive employee information. 只有在嘗試偵測資料表中前八個資料行的變更時,則以這個方式來使用 COLUMNS_UPDATED() 才有效。Using COLUMNS_UPDATED() this way works only when trying to detect changes to the first eight columns in the table.

USE AdventureWorks2012;  
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: 1 + 4 + 9 = 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 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 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.B. 利用 COLUMNS_UPDATED 來測試八個以上資料行Using COLUMNS_UPDATED to test more than eight columns

若要測試會影響前八個資料表資料行以外之資料行的更新,請使用 SUBSTRING 函式來測試 COLUMNS_UPDATED 所傳回的正確位元。To test for updates that affect columns other than the first eight table columns, use the SUBSTRING function to test the correct bit returned by COLUMNS_UPDATED. 此範例會測試影響 AdventureWorks2012.Person.Person 資料表中第 3 個、第 5 個和第 9 個資料行的更新。This example tests for updates affecting columns 3, 5, and 9 in the AdventureWorks2012.Person.Person table.

USE AdventureWorks2012;  
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  

另請參閱See also

位元運算子 (Transact-SQL)Bitwise Operators (Transact-SQL)
CREATE TRIGGER (Transact-SQL)CREATE TRIGGER (Transact-SQL)
UPDATE() (Transact-SQL)UPDATE() (Transact-SQL)