Update SQL Server HierarchyId in after insert trigger

Question

I'm developing a message center in a information system, and today user Eric suggested use hierarchyid datatype to track the message reply, because the objective is to show as a Outlook or Gmail conversation.

To simplify, I've in my database table Messages:

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

When a new message is inserted, I've a trigger to do the update.

I've inserted a new message, and the hierarchy is null, because is the first message, and isn't a reply.

If a try to insert a reply to that message, the hierarchyid still null... :(

My trigger:

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

What I'm doing wrong?

Another point, I've read about the index, but depth-first don't fit because have many with null value, because the first message from a conversation has null value, and bread-first is the best index type to has a better performance? Or I can discard this index?

Thanks in advance!

EDIT:

I've updated the trigger, but don't do the hierarchyid in right way.

Now the trigger is:

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

If I insert messages like id = 2 has parentId = 1, and id = 3 has parentId = 2 have this hierarchy:
id = 1, hierarchy = \ id = 2, hierarchy = \1\ id = 3, hierarchy = \1\1\

The first and second record has the right hierarchy, but next one no... :(

Any clue?

score 1 · Accepted Answer · 2012-11-03T03:56:15.633

That actually is one correct hierarchy implementation. However, it has limitations with batched inserts, but that's another issue .. also, it might make sense to start all "roots" with \x, or one level-deep, so the conversations do not share a parent tree.

In any case, insert a 2nd node with id = 4, parentId = 2, then it should look like hierarchy = \1\2 (it is on the same hierarchy level, but after \1\1).

The values shown in the hierarchy "string" form do not need to relate to the parentId value!

Update SQL Server HierarchyId in after insert trigger

1 Answers1