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?