Post reply

Referencing column with large index in a Trigger

  • 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?

  • December 15, 2005 at 12:59 pm

    #609864

    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.

  • December 15, 2005 at 3:24 pm

    #609908

    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

  • December 15, 2005 at 5:22 pm

    #609924

    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!

  • December 16, 2005 at 10:07 am

    #610112

    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.

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

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