whether really any column gets changed in updated row?

Question

Post reply

whether really any column gets changed in updated row?

karthimca07

Mr or Mrs. 500

Points: 578
More actions
February 14, 2011 at 7:26 am

#245465

create table sample(rowid int,col1 int, col2 varchar(10),editcount int)
insert into sample values(1,10,'xxx',0)
insert into sample values(2,20,'yyy',0)
update sample set col1=15 where rowid=1
after this i want editcount to be increased by 1
if no columns get changed then it should not be increased

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

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

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 1

Hello!

This can be done in two ways:

1. Have a check-sum column. Compute the checksum of the row and if the checksum is different from the stored value, something changed

2. Try using the COLUMNS_UPDATED function - http://msdn.microsoft.com/en-us/library/ms186329.aspx

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

karthimca07 Mr or Mrs. 500 Points: 578 More actions · Answer 2

checksum uses more resources in my case.

as i have to check more than 20 columns off different types.

how columns_updated works?

if want random columns means how do i please help

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

karthimca07 (2/15/2011)
checksum uses more resources in my case.
as i have to check more than 20 columns off different types.
how columns_updated works?
if want random columns means how do i please help

Columns_Updated may not do the trick for you. It only indicates as to whether or not a column was explicitly "updated". If it was updated with the same data as the original (which happens more than some folks may realize), Columns_Updated will still indicate that the column was "updated".

For updates, I'd suggest that a column-by-column comparison between the DELETED and INSERTED tables using either OUTPUT or a Trigger) would probably be the way to go. The code can get quite lengthy but it can be written to run quite quickly.

--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)

tfifield SSCrazy Eights Points: 9655 More actions · Answer 4

Here's a quick and dirty example of what I use for this sort of thing. Assuming you have a table called MyTable and it has a column called UpdateCount:

UPDATE MT

SET

MT.UpdateCount = MT.UpdateCount + 1

FROM MyTable MT

INNER JOIN

(SELECT KeyValue, Col1, Col2, Col3

FROM inserted

EXCEPT

SELECT KeyValue, Col1, Col2, Col3

FROM deleted

) AS Upd ON

MT.KeyValue = Upd.KeyValue

If, as you say you have more than 20 columns then you'll have to explicitly use each one that matters in the SELECT statements above - there's just no way around it.

Todd Fifield