MS SQL Auto Increment non Identity Column

Question

I have a table with an auto incrementing identity/primary key column called ID.

CREATE TABLE Table1(

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[TextContent] [nvarchar](450) NOT NULL,

[Version] [bigint] NOT NULL)

When I UPDATE [TextContent], I'd like [Version] to increment by one. The intent is to have a version for each row that increments anytime the row is updated.

Is there a reasonable way to do this within a single table? Something to do with computed column values and/or triggers perhaps?

I'm using MSSQL 2008 via Entity Framework.

Thanks for any info!

Answer 1

Method 1

Try creating a TRIGGER

CREATE TRIGGER incrementValue
ON Table1
FOR Insert
AS 
   Update Table1 
   set columnvalue = columnvalue +1 
   where id in (select id from inserted)
GO

Method 2

Using Update Command

Increment the Column Value each time along with using update command as below

UPDATE Tab
   SET Version= Version + 1
 WHERE id = 1