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