How do you setup table structure for auto increment primary key with revision?

Question

C# interface accessing Sql server db.

I have a table with a composite key ID and revision.

When I "insert" a new record I want to auto increment the ID. When I "update" the record I want to actually insert a new record but increment revision.

What is the proper table settings for this?

Answer 1

What I have done in the past is actually use a trigger for UPDATE. It would copy all the values into a new record and increment the version column. I would also have an indexed column that would serve as a key, but not a primary key so that all revisions would be tied together.