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


Leave a Reply

Your email address will not be published. Required fields are marked *