3

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!

Improve this question
2
  • Trigger can do the trick. Have a look at Change tracking/CDC (change data capture) also. Commented Feb 19, 2016 at 17:58
  • 1
    Do you need Version column be updated only on update of TextContent column, or on update of any column? In case of the latter, have a look at special rowversion column type, which is castable to bigint as well. Commented Feb 20, 2016 at 15:35

4 Answers 4

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@Simon Ordo It will update the column value to PrevValue +1 after inserting the value . Let me know if it helps
2

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
Improve this answer

7 Comments

I'd say set version += 1
@IvanStarostin you are correct it should be MAX(Version) not MAX(ID).
No, no, not MAX! I believe his point is row version number. Own counter for each row.
I really mean version += 1. I think this is what this column is for.
you lost a join to inserted in this version of trigger
|
1

use trigger

Create trigger testtrigger ON Table1 After insert As begin declare @tempIDversion bigint select @tempIDversion =ID from Table1 insert into Table1(version) values (@tempIDversion) END
Improve this answer

1 Comment

Somehow this version of trigger does not refer INSERTED table and does not support multiple inserts/updates.
0

Maybe too late, but SQL has the rowversion data type that changes automatically on each row insert or update.

It is counter of all the database as inserts or updates as Microsoft explains here:

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column.

This does not increment by one Vs. the previous value of the column as you are trying to achieve, it is just set with the whole database counter of inserts and updates.

But is pretty useful because it only changes when you update a row and always is updated with a higher value, which you can cast to bigint.

Also you can forget about triggers for this purpose.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.