Seeding an order field in a table on insert/update
Posted on 2004-08-28
I have a couple tables which have sequential, non-PK integer fields which describes the order of records.
Example from one table:
I have a procedure for this table which handles inserts and updates. Sometimes the new record will be inserted at the end (max + 1), but sometimes it will be inserted in the middle somewhere. An updated record may move from one position to another.
What is the most efficient way to re-seed the rest of the record accordingly with the aforementioned operations. I'll place this at the end of the stored procedure.