T-SQL Re-Assigning Order Numbering

Question

Say I have a table with a field called "ordernum" that denotes the order of a given set of rows. Now imagine that I delete one of these rows. What type of query would work best for re-assigning the order numbers so that they remain sequential?

Here's an example:

id group_id name ordernum active
---------------------------------------------------
0_____0______'Name1'___5__true
1_____0______'Name2'___4__true
2_____0______'Name3'___3__true
3_____1______'Name4'___2__true
4_____1______'Name5'__1__true
5_____1______'Name6'__NULL___false

Now if I deleted the column with id='4' how would I reset the values in the 'ordernum' field for that specific group? Is this even possible?
Or if I added a new row. (The first time the rows are created they are sorted by date, but then the user has the option to set the order himself.)

In my table design I have a column 'active' boolean. If 'active' is set to false, then 'ordernum' is set to NULL. Otherwise it should be given an order number.

Answer 1

You could use a trigger for the delete:

update tbl
set ordernum = ordernum - 1
from deleted d
where d.active = 1
and group_id = d.group_id
and ordernum > d.ordernum

Adding a new row shouldn't be a big deal either, though I wouldn't necessarily use a trigger for the insert.