Detecting Multiple Changes

Question

Detecting Multiple Changes

Steve Jones - SSC Editor

SSC Guru

Points: 738683
More actions
August 27, 2025 at 12:00 am

#4593639

Comments posted to this topic are about the item Detecting Multiple Changes

Viewing 6 posts - 1 through 6 (of 6 total)

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

Carlo Romagnano SSC-Insane Points: 22788 More actions · Answer 1

If I want to check that ONLY the CustomerContactFirstName and CustomerContactLastName are update, then the right command is

IF COLUMNS_UPDATED ()= 0x0C0000

If I want to check that the CustomerContactFirstName and CustomerContactLastName are update together, then the right command is

SUBSTRING(COLUMNS_UPDATED (),1,1) & 12) = 12

update CustomerLarge set
CustomerContactFirstName= 1
,CustomerContactLastName= 1

edwardwill SSCertifiable Points: 5959 More actions · Answer 2

Is it just me, or is this just a little bit, well, crap?

Thomas Franz Hall of Fame Points: 3938 More actions · Answer 3

I doubt that anybody (besides some old, long retired boomer nerds that implented it decades ago) are using COLUMNS_UPDATED(), particularly since

you could write UPDATE(col1) OR UPDATE(col2) OR UPDATE(col3) to test multiple columns (or use AND if all needes to be part of the UPDATE/MERGE)
you never know 100% exact if the columns are still in the same order 5 years later (and you really don't want to select sys.columns every time the trigger fires just to recalculate the bit value
it is very clumsy when you have more then 8 columns in the table (=almost every table)

Keep in mind that if UPDATE() or COLUMNS_UPDATED() returns "true", it does not mean, that there is really a change on the data in the column. It is just an indicator, that the column was part of the SET operator in the triggering UPDATE / MERGE.

You still need to compare the INSERTED / DELETED "tables" to find out, if there was really a change (imagine someone clicked onto the save button on a form and the app is not very sophicated and just runs an UPDATE on all columns that have an editable field on the screen, regardless if there was really a change or not)

This reply was modified 2 weeks, 1 day ago by Thomas Franz.

God is real, unless declared integer.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

The question specifically states "I want to determine if both the CustomerContactFirstName and CustomerContactLastName fields are changed, but no others. "

That means that the first answer cannot be correct because it only checks for the first byte and none of the other bytes, which could have been changed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

Thomas Franz wrote:

I doubt that anybody (besides some old, long retired boomer nerds that implented it decades ago) are using COLUMNS_UPDATED(), particularly since
you could write UPDATE(col1) OR UPDATE(col2) OR UPDATE(col3) to test multiple columns (or use AND if all needes to be part of the UPDATE/MERGE)
you never know 100% exact if the columns are still in the same order 5 years later (and you really don't want to select sys.columns every time the trigger fires just to recalculate the bit value
it is very clumsy when you have more then 8 columns in the table (=almost every table)
Keep in mind that if UPDATE() or COLUMNS_UPDATED() returns "true", it does not mean, that there is really a change on the data in the column. It is just an indicator, that the column was part of the SET operator in the triggering UPDATE / MERGE.
You still need to compare the INSERTED / DELETED "tables" to find out, if there was really a change (imagine someone clicked onto the save button on a form and the app is not very sophicated and just runs an UPDATE on all columns that have an editable field on the screen, regardless if there was really a change or not)

A million likes! Especially the part about how to detect actual data changes instead of some API writing the same value.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)