sql-server - 插入触发器后更新SQL Server HierarchyId

Question

我正在开发信息系统中的邮件中心，今天用户Eric建议使用architectureid数据类型来跟踪邮件回复，因为目标是显示为Outlook或Gmail对话。

为了简化，我在数据库表Messages中:

MessageId int PK
ReplyToId int FK null
Subject varchar
Body varchar
Hierarchy hierarchyid

当插入新消息时，我有一个触发器来进行更新。

我插入了一条新消息，并且层次结构为null，因为这是第一条消息，并且不是答复。

如果尝试插入对该消息的回复，则architectureid仍然为空... :(

我的触发器:

ALTER TRIGGER [dbo].[trg_UpdateHierarchy] 
   ON  [dbo].[Messages]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here

    DECLARE @replyId int
    SELECT @replyId = inserted.ReplyId
    FROM inserted
    IF(@replyId IS NULL)
    BEGIN
        RETURN
    END

    DECLARE @parent hierarchyid
    SELECT @parent = Hierarchy
    FROM   [Messages]
    WHERE  [Messages].MessageId = @replyId


    DECLARE @currentHierarchy hierarchyid = @parent.GetDescendant(null, null).ToString()
    DECLARE @messageId int
    SELECT @messageId = inserted.MessageId
        FROM inserted
    UPDATE [Messages]
        SET Hierarchy = @currentHierarchy
        WHERE [Messages].MessageId = @messageId
END
GO 

我做错了什么？

另一点，我已经读过有关索引的信息，但深度优先不适合，因为其中有许多具有空值，因为对话中的第一条消息具有空值，而面包优先是具有更好索引的最佳索引类型性能？或者我可以放弃该索引？

提前致谢!

编辑:

我已经更新了触发器，但是没有以正确的方式执行 hierarchyid。

现在触发是:

ALTER TRIGGER [dbo].[trg_UpdateHierarchy] 
   ON  [dbo].[Messages]
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    DECLARE @messageId int
    DECLARE @ParentId int
    SELECT  @messageId = inserted.MessageId,
            @ParentId = inserted.ParentId
        FROM inserted

    IF(@ParentId IS NULL)
        BEGIN
            UPDATE [Messages]
                SET Hierarchy = hierarchyid::GetRoot()
                WHERE [Messages].MessageId = @messageId
            RETURN
        END
    ELSE
        BEGIN
            DECLARE @parent hierarchyid
            SELECT @parent = Hierarchy
                FROM   [Messages]
                WHERE  [Messages].MessageId = @ParentId

            DECLARE @lastHierarchy hierarchyid
            SELECT @lastHierarchy = MAX(Hierarchy) 
                FROM [Messages] 
                WHERE Hierarchy.GetAncestor(1) = @parent

            UPDATE [Messages]
                SET Hierarchy = @parent.GetDescendant(@lastHierarchy, NULL)
                WHERE [Messages].MessageId = @messageId
        END
END  

如果我插入诸如 id = 2具有 parentId = 1的消息，而 id = 3具有 parentId = 2的消息具有以下层次结构: id = 1, hierarchy = \

id = 2, hierarchy = \1\

id = 3, hierarchy = \1\1\
第一条和第二条记录具有正确的层次结构，但下一条没有... :(

有什么线索吗？

Answer 1

这实际上是一种正确的层次结构实现。但是，它对于批量插入有局限性，但这是另一个问题..同样，以\x或一个级别较深的名称开始所有“根”可能是有意义的，因此 session 不共享父树。

无论如何，请使用id = 4, parentId = 2插入第二个节点，然后它应该看起来像hierarchy = \1\2(它在相同的层次结构级别上，但是在\1\1之后)。
hierarchy“字符串”形式显示的值不需要与parentId值相关!