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

2021-09-10 00:00:00 tsql sql-server

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

I read the article about COLUMNS_UPDATED() on msdn.

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

There are example. I reduce code from example. Create table with trigger:

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. 第一次更新

  1. First update

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

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

Trigger returned 0x04 and 4 - everything OK

第二次更新

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

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

Trigger returned 0x0A and 10 - everything OK

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

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

Now it return false when update

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 相同.

推荐答案

嗯，msdn 对这个不是很清楚，但是有一个声明，在你链接到的文档中，你可以看到你必须当您的表格中的列超过 8 列时，可以采用另一种方式.

Well, msdn is not really clear on this one, but there's a statement, in the doc you're linking to, where you can see that you have to work another way when you have more than 8 columns in your table.

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

The fact is that you need to use substring when you have more than 8 column, even if you're working only on the first 8 columns !

如此处所述，同样(给出的示例代码与在 msdn)

as stated here, also (the sample code given is the same as in msdn)

但是，如果列数超过八列，则 COLUMNS_UPDATED()函数按从左到右的顺序返回字节，最少的最左边的有效字节.最左边的字节将包含关于第 1 列到第 8 列的信息，第二个字节将包含有关第 9 列到第 16 列的信息，依此类推.如果有九个表中的列，并且您想检查列 2、3 或 4 是否有已更新，使用的正确位掩码是 0x0E00(十进制 3584).

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).

由于按位运算符仅适用于 32 位整数，因此您可能有难以检查超过 32 列的表格.正确的位掩码，用于检查第 3、5 和 9 列在有 16 列时是否已更改列或更少是 0x1401(十进制 5121).正确的位掩码是0x140100 如果有 24 列或更少，0x14010000 如果有 32 列或少，等等.

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.

因此，如果列数超过八列，则需要使用SUBSTRING 分别提取字节

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

相关文章