Ryan’s Tech Blog

An update trigger using COLUMNS_UPDATED() was failing, and I figured out the problem:

Some simple SQL:

SELECT CAST(0×0200 as bigint), 0×0200 | 0
SELECT CAST(0×02000 as bigint), 0×02000 | 0
SELECT CAST(0×020000 as bigint), 0×020000 | 0
SELECT CAST(0×0200000 as bigint), 0×0200000 | 0
SELECT CAST(0×02000000 as bigint), 0×02000000 | 0
SELECT CAST(0×020000000 as bigint), 0×020000000 | 0
SELECT CAST(0×0200000000 as bigint), 0×0200000000 | 0
I had “IF COLUMNS_UPDATED() | 0 > 0″ in my trigger, and on large values of COLUMNS_UPDATED(), bitwise ORing with zero equals zero.  Of course, “IF COLUMNS_UPDATED() | 0 > 0″ is a retarded way to say “IF COLUMNS_UPDATED() > 0″, but whatever codepath that large number triggers wigs out there to.  Try these out:

SELECT 1 WHERE 0×0200000000 > 0
SELECT 1 WHERE CAST(0×0200000000 as bigint) > 0

So, to fix my trigger, I have “IF CAST(COLUMNS_UPDATED() as bigint) > 0″, which still seems a little retarded, but at least my trigger works.