如何使用SQL 2008 HierarchyID数据类型执行级联删除？

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')

我没有太多使用HierarchyID，所以我有点不确定。如果我的表有HierarchyID，我该如何执行级联删除？（即删除'父母'时删除所有'孩子'）

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

我假设我将不得不使用CTE和HierarchyID函数，但不知道如何去做...

2 个解决方案

#1

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.

EXCEPT确保我们不会以无限递归循环结束。在我自己的实现中，我实际上在触发器正在运行的上下文信息中设置了一个标志，然后在触发器的开头检查此标志并在标志已经设置的情况下提前返回。这不是必需的，但性能稍好一些。

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.

一开始似乎要简单得多，但请记住，人们必须记住使用它。如果你没有触发器，并且有人在层次结构表上直接删除而不是通过SP，它可以很容易地孤立你的孩子记录而没有任何人知道，直到为时已晚。

#2

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

您将要查看T-SQL中的IsDescendantOf方法。像这样的东西：

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

DECLARE @ParentNodeHID hierarchyid SET @ParentNodeHID = [您想要开始删除的节点]

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

DELETE HierarchyTable WHERE NodeHID.IsDescendantOf（@ ParentNodeHID）= 1

(HierarchyTable = Table where your hierarchyis stored)

（HierarchyTable =存储层次结构的表）

** 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.

**请记住，使用此方法，节点被视为自身的子节点。因此，无论您传入@ParentNodeHID，都将满足WHERE子句条件。

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

看看BOL文章：ms-help：//MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/edc80444-b697-410f-9419-0f63c9b5618d.htm

#1