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

Question

I haven't used the HierarchyID much, so I'm a little unsure. If my table has a HierarchyID, how do I perform a cascading delete? (i.e. delete all 'children' when deleting the 'parent')

I assume I would have to use a CTE and HierarchyID functions, but not sure how to go about it...

Answer 1

You'll want to take a look at the IsDescendantOf method in T-SQL. Something like this:

DECLARE @ParentNodeHID hierarchyid SET @ParentNodeHID = [the node you want to start deleting at]

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

(HierarchyTable = Table where your hierarchyis stored)

** Keep in mind, that with this method, a node is considered a child of itself. So, whatever you pass into the @ParentNodeHID will meet the WHERE clause conditions.

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

Answer 2

The trigger-based solution would 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
    )

The EXCEPT ensures that we don't end up in an infinite recursive loop. In my own implementations, I actually set a flag in the context info that the trigger is running, then check this flag at the beginning of the trigger and return early if the flag is already set. This is not necessary, but is slightly better for performance.

Alternatively, if you don't want to use a trigger, you could 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 have to remember to use this. If you don't have the trigger, and somebody does a direct DELETE on the hierarchy table instead of going through the SP, it could very easily orphan your child records without anyone knowing until it's too late.