WITH GroupMembers (Child, ParentGroup, Level, hierarchypath)
AS
(-- Anchor member definition
SELECT	g.child, 
	g.parent, 
	0 AS Level, 
	convert(varchar(max), g.child + '->' + g.parent) AS hierarchypath
FROM ChildrenAndParents AS g
WHERE child not in (select parent from ChildrenAndParents)
UNION ALL
-- Recursive member definition
SELECT	g.child,
	g.parent,
	Level + 1,
	hierarchypath + '->' + g.parent          -- Add '-->...' to end when recursion found
	 + Case When gm.hierarchypath like '%->'+g.parent+'->%' Then '-->...'
		Else	''
		End
FROM ChildrenAndParents as g
INNER JOIN GroupMembers AS gm
ON gm.parentgroup = g.child
--Exclude if the hierarchypath text contains a recursion
where gm.hierarchypath not like '%->...'
)

child     parentgroup level       hierarchypath
--------- ----------- ----------- -----------------------
a         b           0           a->b
a         c           1           a->b->c
a         d           2           a->b->c->d
a         b           3           a->b->c->d->b-->...
e         b           0           e->b
e         c           1           e->b->c
e         d           2           e->b->c->d
e         b           3           e->b->c->d->b-->...

child     parentgroup level       hierarchypath
--------- ----------- ----------- -----------------------
b         c           0           b->c
c         d           0           c->d
etc

child     parentgroup level       hierarchypath
--------- ----------- ----------- -----------------------
b         c           0           b->c
c         d           0           c->d
etc

WHERE child not in (select parent from childrenandparents)

Mgr      Emp
NULL     A
A          B
B          C

UPDATE tbl
SET Mgr = 'C'
WHERE Emp = 'B'

CREATE TRIGGER tg_NoCycles
ON Employee_Temp
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON

    --Only check if the ManagerId column was updated
    IF NOT UPDATE(ManagerId)
        RETURN

    --Avoid cycles
    DECLARE @CycleExists BIT
    SET @CycleExists = 0

    --Traverse up the hierarchy toward the
    --root node
    ;WITH e AS
    (
        SELECT EmployeeId, ManagerId
        FROM inserted

        UNION ALL

        SELECT e.EmployeeId, et.ManagerId
        FROM Employee_Temp et
        JOIN e ON e.ManagerId = et.EmployeeId
        WHERE
            et.ManagerId IS NOT NULL
            AND e.ManagerId <> e.EmployeeId
    )
    SELECT @CycleExists = 1
    FROM e
    WHERE e.ManagerId = e.EmployeeId

    IF @CycleExists = 1
    BEGIN
        RAISERROR('The update introduced a cycle', 16, 1)
        ROLLBACK
    END
END
GO

Blog |
Twitter | LinkedIn