sql - 触发 UPDATE() 和 COLUMNS_UPDATED() 函数

Question

我在表上有 AFTER UPDATE 触发器。

我需要获取更改列的名称及其旧值和新值。

对代码中的每一列执行语句UPDATE(column_name) - 糟糕的解决方案。但我无法通过查询获取所有表列名称

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.Columns 
WHERE TABLE_NAME = 'smth'

并在游标中动态获取UPDATE(@column_name)值。

同时，当我尝试使用 COLUMNS_UPDATED() 函数时，当我仅更新表中的一列时，我得到结果(值转换为 int):

64 (Updated column with ORDINAL_POSITION = 31)
32 (Updated column with ORDINAL_POSITION = 30)
8 (Updated column with ORDINAL_POSITION = 29)
4 (Updated column with ORDINAL_POSITION = 28)
2 (Updated column with ORDINAL_POSITION = 27)
1 (Updated column with ORDINAL_POSITION = 26)
32768 (Updated column with ORDINAL_POSITION = 25)

我觉得很奇怪，请帮忙。

Answer 1

不管奇怪不奇怪，这至少是documented :

Caution

In SQL Server 2008, the ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED. To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query 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 AdventureWorks2008R2.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact';