Manage Hierarchical data in SQL Server

Representing hierarchical database in SQL Server, involves the following steps
First, Create a table which represents the hierarchical information.
Second, Insert few information.
Third, Create the trigger which fires whenever any row is deleted, and this
trigger recursively delete all the sub tasks associated with it.

Step 1: Create Table

CREATE TABLE Tasks
(
TaskID int IDENTITY (1, 1) NOT NULL,
TaskName varchar(100),
Description varchar(1000),
ParentTaskID int NULL
)

Step 2: Insert Information

INSERT INTO Tasks VALUES(‘task 1’, ‘task 1 Description’,NULL)
INSERT INTO Tasks VALUES(‘task 2’, ‘task 2 Description’,1)
INSERT INTO Tasks VALUES(‘task 3’, ‘task 3 Description’,1)
INSERT INTO Tasks VALUES(‘task 4’, ‘task 4 Description’,1)
INSERT INTO Tasks VALUES(‘task 5’, ‘task 5 Description’,2)
INSERT INTO Tasks VALUES(‘task 6’, ‘task 6 Description’,2)
INSERT INTO Tasks VALUES(‘task 7’, ‘task 7 Description’,5)
INSERT INTO Tasks VALUES(‘task 8’, ‘task 8 Description’,5)

GO



Step 3: Create Trigger

ALTER DATABASE <db_name> SET RECURSIVE_TRIGGERS ON

GO

CREATE TRIGGER triggerDeleteTask ON
Tasks FOR DELETE
AS

IF @@rowcount = 0 RETURN
DELETE FROM T
FROM Tasks AS T JOIN deleted AS D
ON T. ParentTaskID = D. TaskID

GO