2

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;

EDIT:
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.