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
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow