April 6, 2015 at 2:44 pm
I recently came across a requirement to determine in a trigger whether a given column was the only one updated. I didn't want to have to enumerate all of the other columns, I didn't want loops, etc. I wanted to be able to add a guard statement near the top of the trigger that said, "If the only column being updated is X, get out of here." So I came up with the following solution, but I'm curious if anyone can spot holes in it or has another approach.
My general approach is to test two things - first, is the column of interest being updated, and second, is only one column being updated. If both are true, then I know no other columns are being updated. The first is easy - UPDATE(ColumnName) will tell me that. The second took a little thinking:
REPLACE(CAST(COLUMNS_UPDATED() AS varchar(128)) COLLATE Latin1_General_Bin, CHAR(0), '') IN
(CHAR(1), CHAR(2), CHAR(4), CHAR(8), CHAR(16), CHAR(32), CHAR(64), CHAR(128))
Basically, I strip out all of the null bytes from COLUMNS_UPDATED() and then test to see whether the remaining byte is one of the 8 that have a single bit set.
I then wanted a general solution - testing for a single column is trivial, but testing for even just 2 is a bit harder. I came up with the following:
10*LEN(CONVERT(varchar(256), COLUMNS_UPDATED(), 2)) -
LEN(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT(varchar(256), COLUMNS_UPDATED(), 2),
'1', ''), '2', ''), '4', ''), '8', '')) -
2*LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT(varchar(256), COLUMNS_UPDATED(), 2),
'3', ''), '5', ''), '6', ''), '9', ''), 'A', ''), 'C', '')) -
3*LEN(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT(varchar(256), COLUMNS_UPDATED(), 2),
'7', ''), 'B', ''), 'D', ''), 'E', '')) -
4*LEN(REPLACE(CONVERT(varchar(256), COLUMNS_UPDATED(), 2), 'F', ''));
Basically, I convert the varbinary() to a hex string and then count how many of each nibble are present - some nibbles have 1 bit, some have 2, some have 3, and 'F' has 4. In each case, I use REPLACE to strip out the nibbles I'm looking for and compare string length, and I used simple algebraic manipulations to simplify the calculations.
Note that I'm not worried about whether the actual data changed or not (especially since that isn't a well-defined problem once you have more than one record being updated), just whether the column was SET.
Once you've got the above, you can use it in scenarios where you want to add a guard for an UPDATE statement that updates 2 or 3 or whatever known columns (i.e. UPDATE(Col1) AND UPDATE(Col2) AND @number_of_columns_updated = 2, or what have you).
Thoughts?
April 6, 2015 at 5:47 pm
A warning:
if you have a column set defined on the table, and the column you are checking is part of that column set, then it will never be the only column updated.
With that in mind, if the value of columns_updated() when you only update your selected column is (for example) 4, then a simple
if columns_updated() & 4 <> columns_updated() return;
would do what you want, I would think.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply