trigger is inconsistent in firing 1

[ethorn10]

Hi all --

I'm pretty new to SQL Server but I was asked to look into why something is happening. The unfortunate part is that I do not have the specific trigger to give to you guys. What I was told is that it works on tables with 8 or fewer columns in it but more than 8 columns causes it not to fire. Does this seem right or is there some other problem that we should look into?

Thanks in advance

 

[vongrunt]

Very likely something related to COLUMNS_UPDATED() used in trigger (bad practice).

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[ethorn10]

thanks vongrunt. i wish i had the trigger itself so i could look at it but i was just told to look into why it might be happening. i'll look into the columns_updated() aspect but do you have any ideas on what kind of problems this could cause?

like i said...i'm very new to this. thanks again.

 

[ethorn10]

vongrunt...

upon further investigation of columns_updated() i found this from msdn:

IF (COLUMNS_UPDATED())

Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns in the table were inserted or updated.

The COLUMNS_UPDATED function returns the bits in order from left to right, with the least significant bit being the leftmost. The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost. COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

i can almost guarantee that's the problem and i'll keep you posted on anything i find. thanks for the direction. a star for you.

 

[vongrunt]

Note that COLUMNS_UPDATED() is not limited to 8 columns - problem may be somewhere in expressions that attempt to isolate bits from bitmask.

My primary complain about this check method is kind of theoretical. In relational algebra order of columns is supposed to be irrelevant. ABCD or BCDA or ACDB shouldn't matter. COLUMNS_UPDATED() violates this rule. So when someone changes table structure and swaps some columns (as DB designers vs DBAs sometimes like to do), trigger still works but performs checks on wrong columns.

Btw. here is one article:

http://www.databasejournal.com/features/mssql/article.php/1479821

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[ethorn10]

thanks for the reference there as well. yes, i noticed that it is not limited to 8 columns but after the 8th column, columns_updated begins returning bytes instead of bits. i can almost guarantee this is the problem as we're probably doing bitwise comparisons on bytes.

i thank you much vongrunt.