January 23, 2018 at 4:43 am
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!
January 23, 2018 at 6:04 am
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
January 23, 2018 at 7:03 am
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.
🙂
January 23, 2018 at 9:41 am
I think you can use NOT:
IF NOT UPDATE(col19) AND NOT UPDATE(col20)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 23, 2018 at 11:59 am
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