How can I do Cascading Delete with the HierarchyID SQL 2008 data type?

Question

I have not used HierarchyID much, so I'm a little unsure. If there is a Hierarchical Identifier in my table, how do I cascade delete? (ie delete all "children" when deleting the "parent")

I assume that I will have to use the CTE and HierarchyID functions, but I don’t know how to do this ...

Answer 1

The trigger based solution will be:

CREATE TRIGGER tr_Hierarchy_DeleteChildren ON Hierarchy FOR DELETE AS DELETE FROM Hierarchy WHERE ID IN ( SELECT DISTINCT h.ID FROM deleted d INNER JOIN Hierarchy h ON h.ObjectNode.IsDescendantOf(d.ObjectNode) = 1 EXCEPT SELECT ID FROM deleted ) 

EXCEPT ensures that we do not end an infinite recursive loop. In my own implementations, I actually set the flag in the context information that is triggered by the trigger, and then checks this flag at the start of the trigger and returns earlier if the flag is already set. This is not necessary, but slightly better for performance.

Alternatively, if you do not want to use a trigger, you can put the following logic in a stored procedure:

 CREATE PROCEDURE DeleteHierarchyTree @ParentID hierarchyid AS DELETE FROM Hierarchy WHERE ID.IsDescendantOf(@ParentID) = 1 

It seems a lot simpler at first, but keep in mind that people should remember to use this. If you do not have a trigger, and someone does a direct DELETE in the hierarchy table instead of going through the SP, it can very easily overpower your child records without knowing until it is too late.

Answer 2

You need to take a look at the IsDescendantOf method in T-SQL. Something like that:

DECLARE @ParentNodeHID hierarchy SET @ParentNodeHID = [the node you want to start by deleting]

DELETE Hierarchy WHERE NodeHID.IsDescendantOf (@ParentNodeHID) = 1

(HierarchyTable = table where your hierarchy is stored)

** Keep in mind that using this method, a node is considered a child by itself. So, everything you pass to @ParentNodeHID will match the WHERE clause.

Take a look at the article BOL: ms-help: //MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/edc80444-b697-410f-9419-0f63c9b5618d.htm