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

我正在开发信息系统中的邮件中心，今天用户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\
第一条和第二条记录具有正确的层次结构，但下一条没有... :(

有什么线索吗？

最佳答案

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

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

关于sql-server - 插入触发器后更新SQL Server HierarchyId，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8683264/