sql-server - 当 ColumnId 相同时，COLUMNS_UPDATED() 返回不同的值

Question

我读了关于COLUMNS_UPDATED()的文章在 msdn 上。

有例子。我减少了示例中的代码。使用触发器创建表:

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 TRIGGER dbo.updEmployeeData 
ON dbo.employeeData 
AFTER UPDATE AS
    print COLUMNS_UPDATED() 
    print COLUMNS_UPDATED() & 14
GO
INSERT INTO employeeData
VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32);
GO

1. 第一次更新

   UPDATE dbo.employeeData
   SET emp_salary = 51000
   WHERE emp_id = 101;
   

   触发器返回 0x04 和 4 - 一切正常

2. 第二次更新

   UPDATE dbo.employeeData
   SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
   WHERE emp_id = 101;
   

   触发器返回 0x0A 和 10 - 一切正常

但是让我们尝试添加一些列

CREATE TABLE dbo.employeeData2 (
    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,
    trash1 int NULL,
    trash2 int NULL,
    trash3 int NULL,
    trash4 int NULL,
    trash5 int NULL,
    trash6 int NULL,
    trash7 int NULL,
    trash8 int NULL,
    trash9 int NULL,
    trash10 int NULL,
    trash11 int NULL,
    trash12 int NULL,
    trash13 int NULL,
    trash14 int NULL,
    trash15 int NULL,
    trash16 int NULL,
    trash17 int NULL,
    trash18 int NULL,
    trash19 int NULL,
    trash20 int NULL,
    trash21 int NULL,
    trash22 int NULL,
    trash23 int NULL,
    trash24 int NULL,
    trash25 int NULL,
    trash26 int NULL,
    trash27 int NULL,
    trash28 int NULL,
    trash29 int NULL,
    trash30 int NULL,
    trash31 int NULL
   );
GO

CREATE TRIGGER dbo.updEmployeeData2
ON dbo.employeeData2
AFTER UPDATE AS
   print COLUMNS_UPDATED() 
   print COLUMNS_UPDATED() & 14
GO
INSERT INTO employeeData2
(emp_id,emp_bankAccountNumber,emp_salary,emp_SSN,emp_lname,emp_fname,emp_manager)
VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32);
GO

现在更新时返回 false

UPDATE dbo.employeeData2
SET emp_salary = 51000
WHERE emp_id = 101;
-- return 0x0400000000
-- return 0

UPDATE dbo.employeeData2
SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
WHERE emp_id = 101;
-- return 0x0A00000000
-- return 0

问题:为什么 0x04 变成 0x0400000000 而 0x0A 变成 0x0A00000000 ？两个表中的 ColumnId 相同。

Answer 1

嗯，msdn对此并不是很清楚，但是在您链接到的文档中有一个声明，您可以在其中看到当表中的列超过8列时，您必须以另一种方式工作。

事实是，当超过 8 列时，您需要使用子字符串，即使您只处理前 8 列!

如上所述here ，另外(给出的示例代码与msdn中的相同)

However, if there are more than eight columns, the COLUMNS_UPDATED() function returns the bytes in order from left to right, with the least significant byte being the leftmost. The leftmost byte will contain information about columns 1 through 8, the second byte will contain information about columns 9 through 16, and so on. If there were nine columns in the table and you want to check if columns 2, 3, or 4 have been updated, the correct bitmask to use is 0x0E00 (decimal 3584).

Since the bitwise operator only works on 32-bit integers, you may have difficulty checking a table with more than 32 columns. The correct bitmask to check if columns 3, 5, and 9 have changed when there are 16 columns or less is 0x1401 (decimal 5121). The correct bitmask is 0x140100 if there are 24 columns or less, 0x14010000 if 32 columns or less, and so on.

Therefore, if there are more than eight columns, you will need to use SUBSTRING to extract the bytes separately