I am working with an INSTEAD OF UPDATE trigger, where I have to calculate / re-calculate the hierarchyid of a row based on another column value. Thus far, I have it working. The problem that I run into however, is that I need all hierarchyid values in this column to be unique; and when ascertaining the last child of x parent, I always get the same result for every row that is being updated in this trigger.
DECLARE @tenantId UNIQUEIDENTIFIER = (SELECT TOP 1 Id FROM Tenant.tenant WHERE Sid=SUSER_SID()); UPDATE dbo.organization SET Name = i.Name, Parent = i.Parent, Node = COALESCE( -- Use existing value for Node if parent hasn't changed (SELECT node FROM dbo.organization WHERE tenant_id = @tenantId and id=i.id AND parent = i.parent), -- Change Node to root if organization is assigned as a parent to itself (SELECT hierarchyid::GetRoot() WHERE i.parent IS NULL OR i.parent = i.id), -- Update the value of Node as a new descendant of the updated Parent node (SELECT node.GetDescendant( (SELECT TOP 1 node FROM dbo.organization WHERE Tenant_Id = @tenantId and Parent = i.Parent AND node.GetLevel() != 0 AND id != i.Id ORDER BY node desc), NULL) FROM dbo.organization WHERE Tenant_Id = @tenantId AND id = i.parent), -- Fallback to insert as new descendant of parent node if NULL was inserted (SELECT node.GetDescendant( (SELECT TOP 1 node FROM dbo.organization WHERE Tenant_Id=@tenantId AND Parent=i.Parent AND node.GetLevel() != 0 AND id != i.Id ORDER BY node DESC), NULL) FROM dbo.organization WHERE Tenant_Id = @tenantId AND id=i.Parent AND i.node IS NULL), i.Node) FROM inserted i WHERE dbo.organization.Tenant_Id = @tenantId AND dbo.organization.Id = i.Id;
I feel I should add some additional info here. An example: If you were to update 100 rows via this trigger, and set their Parent column to point to the record containing the root hierarchyid (/), all of the values updated by this trigger would be set to /1/ instead of /1/, /2/, /3/... etc.
Any insight that can be provided would be greatly appreciated.