問(wèn)題描述
我閱讀了關(guān)于 COLUMNS_UPDATED() 的文章 在 msdn 上.
I read the article about COLUMNS_UPDATED() on msdn.
有例子.我從示例中減少了代碼.使用觸發(fā)器創(chuàng)建表:
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
第一次更新
First update
UPDATE dbo.employeeData
SET emp_salary = 51000
WHERE emp_id = 101;
觸發(fā)器返回 0x04 和 4 - 一切正常
Trigger returned 0x04 and 4 - everything OK
第二次更新
UPDATE dbo.employeeData
SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
WHERE emp_id = 101;
觸發(fā)器返回 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
現(xiàn)在更新時(shí)返回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
問(wèn)題:為什么 0x04 變成了 0x0400000000 而 0x0A 變成了 0x0A00000000 ?兩個(gè)表中的 ColumnId 相同.
推薦答案
嗯，msdn 對(duì)這個(gè)不是很清楚，但是有一個(gè)聲明，在你鏈接到的文檔中，你可以看到你必須當(dāng)您的表格中的列超過(guò) 8 列時(shí)，可以采用另一種方式.
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.
事實(shí)上，當(dāng)您有超過(guò) 8 列時(shí)，您需要使用子字符串，即使您只處理前 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)
但是，如果列數(shù)超過(guò)八列，則 COLUMNS_UPDATED()函數(shù)按從左到右的順序返回字節(jié)，最少的最左邊的有效字節(jié).最左邊的字節(jié)將包含關(guān)于第 1 列到第 8 列的信息，第二個(gè)字節(jié)將包含有關(guān)第 9 列到第 16 列的信息，依此類推.如果有九個(gè)表中的列，并且您想檢查列 2、3 或 4 是否有已更新，使用的正確位掩碼是 0x0E00(十進(jìn)制 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).
由于按位運(yùn)算符僅適用于 32 位整數(shù)，因此您可能有難以檢查超過(guò) 32 列的表格.正確的位掩碼，用于檢查第 3、5 和 9 列在有 16 列時(shí)是否已更改列或更少是 0x1401(十進(jìn)制 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.
因此，如果列數(shù)超過(guò)八列，則需要使用SUBSTRING 分別提取字節(jié)
Therefore, if there are more than eight columns, you will need to use SUBSTRING to extract the bytes separately
這篇關(guān)于當(dāng) ColumnId 相同時(shí)，COLUMNS_UPDATED() 返回不同的值的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！