MS SQL Server 2005 - How to Auto Increment a field (not primary key)

Question

Hi, I would like to automatically increment a field named `incrementID' anytime any field in any row within the table named 'tb_users' is updated. Currently I am doing it via the sql update statement. i.e "UPDATE tb_users SET name = @name, incrementID = incrementID + 1 .....WHERE id = @id;

I'm wondering how I can do this automatically. for example, by changing the way sql server treats the field - kind of like the increment setting of 'Identity'. Before I update a row, I wish to check whether the incrementID of the object to be updated is different to the incrementID of the row of the db.

Any help muchos appreciated. Thanks

Answer 1

You could use a trigger for this (if I've read you correctly and you want the value incremented each time you update the row).

Answer 2

Columns in the Table can have an Identity Specification set. Simply expand the node in the property window and fill in the details (Is Identity, Increment, Seed).

The IDENTITYCOL keyword can be used for operations on Identity Specifications.

Answer 3

If you just need to know that it changed, rather than specifically that this is a later version or how many changes there have been, consider using a rowversion column.

Answer 4

This trigger should do the trick:

create trigger update_increment for update as
if not update(incrementID) 
  UPDATE tb_users SET incrementID = incrementID + 1 
    from inserted WHERE tb_users.id = inserted.id