title: Update SQL Server HierarchyId in after insert trigger
body: <p>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.</p>
  
  <p>To simplify, I've in my database table Messages:</p>
  
  <pre><code>MessageId int PK
  ReplyToId int FK null
  Subject varchar
  Body varchar
  Hierarchy hierarchyid
  </code></pre>
  
  <p>When a new message is inserted, I've a trigger to do the update.</p>
  
  <p>I've inserted a new message, and the hierarchy is null, because is the first message, and isn't a reply.</p>
  
  <p>If a try to insert a reply to that message, the hierarchyid still null... :(</p>
  
  <p>My trigger:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>What I'm doing wrong?</p>
  
  <p>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?</p>
  
  <p>Thanks in advance!</p>
  
  <p>EDIT:</p>
  
  <p>I've updated the trigger, but don't do the <code>hierarchyid</code> in right way.</p>
  
  <p>Now the trigger is:</p>
  
  <pre><code>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  
  </code></pre>
  
  <p>If I insert messages like <code>id = 2</code> has <code>parentId = 1</code>, and <code>id = 3</code> has <code>parentId = 2</code> have this hierarchy:<br>
  <code>id = 1, hierarchy = \<br>
  id = 2, hierarchy = \1\<br>
  id = 3, hierarchy = \1\1\</code>  </p>
  
  <p>The first and second record has the right hierarchy, but next one no... :(</p>
  
  <p>Any clue?</p>