Referencing column with large index in a Trigger

Question

Referencing column with large index in a Trigger

Alex Tersine

SSC Enthusiast

Points: 170
More actions
December 15, 2005 at 12:15 pm

#92869

The reference/test SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(col-1)) in an UPDATE trigger appears to works only if col <= 8. Is there a way to query where the 9th, 10th or 11th field have been updated and act accordingly?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alex Tersine SSC Enthusiast Points: 170 More actions · Answer 1

If there is a way to reference the column that is being tested for update by name (rather than number), then it won't matter whether the column number is 5 or 50. Unfortunately, I haven't seen an example of reference by name.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

From BOL:

F. Use COLUMNS_UPDATED to test more than 8 columns

If you must test for updates that affect columns other than the first 8 columns in a table, you must use the SUBSTRING function to test the proper bit returned by COLUMNS_UPDATED. This example tests for updates that affect columns 3, 5, or 9 in the Northwind.dbo.Customers table.

USE NorthwindDROP TRIGGER  tr1GOCREATE TRIGGER tr1 ON CustomersFOR UPDATE AS   IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))      + power(2,(5-1)))       AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))      )    PRINT 'Columns 3, 5 and 9 updated'

_____________
Code for TallyGenerator

Alex Tersine SSC Enthusiast Points: 170 More actions · Answer 3

I had seen the BOL sentence, but failed to read it carefully enough the first time around to understand how to handle columns that are larger than 8. The BOL guideline work. Thank you!

Brian Munier SSCrazy Points: 2875 More actions · Answer 4

Couldn't you use the IF UPDATE (ColumnName) to determine if a column was updated?

I have never had a problem with that, but I haven't used it excessively.