Untitled

CREATE FUNCTION dbo.CheckManagerCycles()
RETURNS BIT
AS
BEGIN
    DECLARE @HasCycles BIT = 0;

    WITH ManagerHierarchy AS (
        -- Base case: direct management relationships
        SELECT crewId, manager, 1 AS level
        FROM Managed
        WHERE toDate IS NULL  -- Only consider current relationships

        UNION ALL

        -- Recursive case: find higher level managers
        SELECT mh.crewId, m.manager, mh.level + 1
        FROM ManagerHierarchy mh
        JOIN Managed m ON mh.manager = m.crewId
        WHERE m.toDate IS NULL  -- Only consider current relationships
        AND level < (SELECT COUNT(*) FROM Crew)  -- Prevent infinite recursion
    )
    
    -- Check if any crew member appears in their own management chain
    IF EXISTS (
        SELECT 1
        FROM ManagerHierarchy
        WHERE crewId = manager
    )
    BEGIN
        SET @HasCycles = 1;
    END

    RETURN @HasCycles;
END;

-- Add the constraint using the function
ALTER TABLE Managed
ADD CONSTRAINT CHK_NoCycles
CHECK (dbo.CheckManagerCycles() = 0);CREATE FUNCTION dbo.CheckManagerCycles()
RETURNS BIT
AS
BEGIN
    DECLARE @HasCycles BIT = 0;

    WITH ManagerHierarchy AS (
        -- Base case: direct management relationships
        SELECT crewId, manager, 1 AS level
        FROM Managed
        WHERE toDate IS NULL  -- Only consider current relationships

        UNION ALL

        -- Recursive case: find higher level managers
        SELECT mh.crewId, m.manager, mh.level + 1
        FROM ManagerHierarchy mh
        JOIN Managed m ON mh.manager = m.crewId
        WHERE m.toDate IS NULL  -- Only consider current relationships
        AND level < (SELECT COUNT(*) FROM Crew)  -- Prevent infinite recursion
    )
    
    -- Check if any crew member appears in their own management chain
    IF EXISTS (
        SELECT 1
        FROM ManagerHierarchy
        WHERE crewId = manager
    )
    BEGIN
        SET @HasCycles = 1;
    END

    RETURN @HasCycles;
END;

-- Add the constraint using the function
ALTER TABLE Managed
ADD CONSTRAINT CHK_NoCycles
CHECK (dbo.CheckManagerCycles() = 0);