CODEWITHSUNDEEP
sqltriggers
Vimal Patel
Vimal Patel

Reputation: 3065

Auto Increment Column value whenever update happen on that row in sql server

I want to maintain a column which will store that how many times a row has been modified. So whenever the row has been updated I want to increase the column value. I think I have to use trigger for that.But I am looking for an alternative solution.

Upvotes: 0

Views: 4095

Answers (2)

Mahdi Tahsildari
Mahdi Tahsildari

Reputation: 13582

CREATE TRIGGER CountRows 
    ON TestCount 
    after Update
AS 
Update TestCount  set Cnt = Cnt +1 where ID in (select ID from inserted)
GO

whenever some value in a row changes, the grigger adds +1 to the same row's Cnt column value.

Upvotes: 1

peterm
peterm

Reputation: 92805

IMHO trigger is the way to go, but if you sure that you control all your updates, then you can do as simple as this: 

UPDATE mytable 
   SET somefield='newvalue', 
       update_count = update_count+1 
   WHERE id=n

Upvotes: 4

Related Questions