SQL to reorder nodes in a hierarchy

Question

I have a task list database that uses the adjacency list model (see below), so each "task" can have unlimited subtasks. There is a column "TaskOrder" in the table, so everything is displayed in the correct order in the tree.

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

Task table
----------
Taskid
ParentTaskId
Taskorder
Taskname
--etc--

Any ideas? Thank.

Answer 1

... TaskOrder , . SQL Server delete, , , , , ( ):

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

, parentID TaskOrder , , , , .

, , "--" , , , .

Answer 2

TaskOrder , TaskOrder, . .

Answer 3

. , "" . , . , , - .

, , , . SQL - sproc, .

node , . ParentNodeID, TaskOrder count (*) , , - , .

, , , - .

Answer 4

ROW_Number.

DECLARE @Tasks TABLE
(
  TaskId int PRIMARY KEY,
  ParentTaskId int,
  TaskOrder int,
  TaskName varchar(30)
)

INSERT INTO @Tasks(TaskId, ParentTaskId, TaskOrder, TaskName)
SELECT 1, null, 1, 'ParentTask'

INSERT INTO @Tasks(TaskId, ParentTaskId, TaskOrder, TaskName)
SELECT 2, 1, 2, 'B'

INSERT INTO @Tasks(TaskId, ParentTaskId, TaskOrder, TaskName)
SELECT 3, 1, 1, 'A'

INSERT INTO @Tasks(TaskId, ParentTaskId, TaskOrder, TaskName)
SELECT 4, 1, 3, 'C'
--Initial
SELECT * FROM @Tasks WHERE ParentTaskId = 1 ORDER BY TaskOrder

DELETE FROM @Tasks WHERE TaskId = 2
--After Delete
SELECT * FROM @Tasks WHERE ParentTaskId = 1 ORDER BY TaskOrder


UPDATE t
SET TaskOrder = NewTaskOrder
FROM @Tasks t
  JOIN
(
SELECT TaskId, ROW_Number() OVER(ORDER BY TaskOrder) as NewTaskOrder
FROM @Tasks
WHERE ParentTaskId = 1
) sub ON t.TaskId = sub.TaskId

--After Update
SELECT * FROM @Tasks WHERE ParentTaskId = 1 ORDER BY TaskOrder

Answer 5

88:

UPDATE TaskTable
SET ParentTaskID = (SELECT ParentTaskID AS temp FROM Task_Table t1 WHERE TaskID = 88)
WHERE
TaskID IN (SELECT TaskID task2 FROM TaskTable t2 WHERE ParentTaskID = 88);
Delete FROM TaskTable WHERE TaskID = 88;

, , , .

CAVEAT: !!!