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

quote:

---

If I have messageId = 1 is the start of a new thread, and messageId = 2 has parentId = 1, and messageId = 3 has parentId = 3.

---