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!
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
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
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