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!

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

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


Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why