MS SQL Auto Increment non Identity Column

Question

MS SQL Auto Increment non Identity Column

entity-framework entity-framework-core sql-server sql-server-2008 tsql

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!

Simon Ordo

Popular Answer

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

Heemanshu Bhalla

View more on Stack Overflow

Accepted Answer

This is after Update as discussed in the comments. It simply increments Version of this row.

ALTER TRIGGER triggerIncrementUpdate
ON Table1
AFTER UPDATE

AS
BEGIN

UPDATE Table1 SET Version += 1 FROM Table1 INNER JOIN INSERTED As I ON Table1.ID = I.ID

END

MS SQL Auto Increment non Identity Column

Question

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related