SQL to reorder nodes in a hierarchy

Question

I've got a 'task list' database that uses the adjacency list model (see below) so each 'task' can have unlimited sub-tasks. The table has an 'TaskOrder' column so everything renders in the correct order on a treeview.

Is there an SQL statement (MS-SQL 2005) that will select all the child nodes for a specified parent and update the TaskOder column when a sibling is deleted?

Task Table
----------
TaskId
ParentTaskId
TaskOrder
TaskName
--etc--

Any ideas? Thanks.

Answer 1

Couple of different ways... Since the TaskOrder is scoped by parent id, it's not terribly difficult to gather it. In SQL Server, I'd put a trigger on delete that decrements all the ones 'higher' than the one you deleted, thereby closing the gap (pseudocode follows):

CREATE TRIGGER ON yourtable FOR DELETE
AS
  UPDATE Task
     SET TaskOrder    = TaskOrder - 1
   WHERE ParentTaskId = deleted.ParentTaskId
     AND TaskOrder    > deleted.TaskOrder

If you don't want a trigger, you can capture the parentID and TaskOrder in a query first, delete the row, then execute that same update statement but with literals rather than the trigger.

Or if you want to minimize server round-trips, you could move the to-be-deleted task all the way to the bottom, then move the others up, then do the delete, but that seems overly complicated.