Uniqueness constraints

CREATE TRIGGER uq_ST_NAM_Stage_Name
ON [dbo].[ST_NAM_Stage_Name]
AFTER INSERT
AS
IF EXISTS (
    SELECT
        i.ST_NAM_Stage_Name
    FROM
        inserted i
    CROSS APPLY (
        SELECT
            st.ST_NAM_Stage_Name
        FROM
            [dbo].[pST_Stage](i.ST_NAM_ChangedAt) st
        WHERE
            st.ST_ID <> i.ST_NAM_ST_ID
        AND
            st.ST_NAM_Stage_Name = i.ST_NAM_Stage_Name
    ) ts
)
BEGIN
    DECLARE @msg varchar(2047) = 'Duplicate stage name found.';
    RAISERROR (@msg, 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
END;

CREATE TRIGGER uq_PE_DAT_Performance_Date
ON [dbo].[PE_DAT_Performance_Date]
AFTER INSERT
AS
IF EXISTS (
    SELECT
        i.PE_DAT_Performance_Date
    FROM
        inserted i
    JOIN
        [dbo].[lPE_wasHeld_ST_at] pest
    ON
        pest.PE_ID_wasHeld = i.PE_DAT_PE_ID
    CROSS APPLY (
        SELECT
            pe.PE_DAT_Performance_Date
        FROM
            [dbo].[lPE_Performance] pe
        JOIN
            [dbo].[lPE_wasHeld_ST_at] sub_pest
        ON
            sub_pest.PE_ID_wasHeld = pe.PE_DAT_PE_ID
        WHERE
            sub_pest.ST_ID_at = pest.ST_ID_at
        AND
            pe.PE_ID <> i.PE_DAT_PE_ID
        AND
            DATEDIFF(day, pe.PE_DAT_Performance_Date, i.PE_DAT_Performance_Date) = 0
    ) ts
)
BEGIN
    DECLARE @msg varchar(2047) = 'Performances on the same stage and day was found.';
    RAISERROR (@msg, 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
END;

/*
    Note that this trigger is only necessary if the following load
    order cannot be guaranteed.

    1. Create identities for PE performance
    2. Assign stages ST_ID to the created PE_ID in the tie
    3. Populate the attributes of the PE performance anchor

*/
CREATE TRIGGER uq_PE_wasHeld_ST_at__PE_DAT_Performance_Date
ON [dbo].[PE_wasHeld_ST_at]
AFTER INSERT
AS
IF EXISTS (
    SELECT
        i.PE_ID_wasHeld
    FROM
        inserted i
    JOIN
        [dbo].[PE_DAT_Performance_Date] dat
    ON
        dat.PE_DAT_PE_ID = i.PE_ID_wasHeld
    CROSS APPLY (
        SELECT
            pe.PE_DAT_Performance_Date
        FROM
            dbo.lPE_wasHeld_ST_at sub_pest
        JOIN
            [dbo].[lPE_Performance] pe
        ON
            pe.PE_ID = sub_pest.PE_ID_wasHeld
        WHERE
            sub_pest.ST_ID_at = i.ST_ID_at
        AND
            pe.PE_ID <> dat.PE_DAT_PE_ID
        AND
            DATEDIFF(day, pe.PE_DAT_Performance_Date, dat.PE_DAT_Performance_Date) = 0
    ) ts
)
BEGIN
    DECLARE @msg varchar(2047) = 'Performances on the same stage and day was found.';
    RAISERROR (@msg, 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
END;
GO