IF UDPATE(columnName) NOT EQUALS

  • Morning all, 

    I have 20 + columns.  I only want my trigger to fire if any of 18 are changed.  I can write ..

    IF (UPDATE(col1) OR UPDATE (col2) OR UPDATE ... x 18 )
    BEGIN 
       write to my audit table
    END

    But, it would be more elegant to write:

    IF (UPDATE <> (col19) AND UPDATE <> (col20))
    BEGIN 
       write to my audit table
    END

    Is there a not equals equivalent that would work with the IF(UPDATE)? 

    Failing that I could go for:
    IF (UPDATE (col19) OR UPDATE (col20)
    BEGIN
       do nothing
    END
    ELSE
       write to my audit table
    END

    I know the above is a bit pants, but it seems better than a massive OR statement.  

    But what could my "do nothing" code be.

    Hope this is clear.  It's crystal in my head!

  • If columns 17 & 19 are changed, what do you want to do?  Fire the trigger, or not fire the trigger?

    Either way, rather than checking all 20 column fields, then have a look at the COLUMNS_UPDATED function.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • Blimey!  That looks complicated.  Thank you Thomas.  I think that's exactly what I need, just need to get my head around it.  Appreciate your time / response.  Sometimes it's hard to know what to google for when you don't have the right words.  I'd never even heard of this function before. 

    🙂

  • I think you can use NOT:

    IF NOT UPDATE(col19) AND NOT UPDATE(col20)

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Or NOT ( UPDATE(col19) OR UPDATE(col20) )

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

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