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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy