Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table in a database with three columns: ID, Key and Value. Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's schema is this:

RowID (new ID field for this table, identity(1,1) column)
ID (from source table)
Key (from source table)
Value (from source table)
Version (integer)

So anytime the first table record's changed, it creates a new record in the second with the table everytime.

For every record with a matching ID, I want to add a unique incremental version number. If a record with ID of 1 is entered into this table 5 times, those records should have version numbers of 1, 2, 3, 4, and 5. Is the way to accomplish this through a subquery for existing version numbers, or setting up a partition, or something else?

Thanks.

share|improve this question
1  
is the sequence absolutely needed? It can be generated at runtime from, say, datetime stamps using ROW_NUMBER (whether from the log table or using an slowly changing dimension) –  gbn Mar 27 '12 at 14:09
    
That might be a valid alternative... I'll have to think about it. –  Brian Mains Mar 27 '12 at 14:13
    
@gbn datetime stamps might not be unique –  AlexKuznetsov Mar 27 '12 at 15:52
    
@AlexKuznetsov: I'm assuming a type 2 SCD situation or variety thereof. And datetime is unique per ID –  gbn Mar 27 '12 at 16:28
add comment

2 Answers 2

up vote 1 down vote accepted

In such cases I am storing version numbers, and using referential integrity to make sure version numbers begin with 1 and have no gaps. Because I use constraints, I am sure my solution works in high concurrency situations. I have described this approach in this article

share|improve this answer
add comment

Can't you add rowversion(http://msdn.microsoft.com/en-us/library/ms182776(v=sql.105).aspx) column to your audited table and store this number in another table? It's sequential by definition, though it doesn't start with 1.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.