
/* Code Fragment 4.17 INCUMBENTS.PCN is a sequenced foreign key for POSITIONS.PCN (both tables are temporal). */

CREATE TRIGGER Sequenced_Ref_Integrity
ON INCUMBENTS
FOR INSERT, UPDATE, DELETE
AS
BEGIN
	IF (	EXISTS (SELECT *
			FROM INCUMBENTS AS I
			WHERE NOT EXISTS (	SELECT *
						FROM POSITIONS AS P
						WHERE I.PCN = P.PCN
						AND P.START_DATE <= I.END_DATE
						AND I.START_DATE < P.END_DATE)
			OR NOT EXISTS (	SELECT *
					FROM POSITIONS AS P
					WHERE I.PCN = P.PCN
					AND P.START_DATE < I.END_DATE
					AND I.END_DATE <= P.END_DATE)
			OR EXISTS (	SELECT *
					FROM POSITIONS AS P
					WHERE I.PCN = P.PCN
					AND I.START_DATE < P.END_DATE
					AND P.END_DATE < I.END_DATE
					AND NOT EXISTS (SELECT *
							FROM POSITIONS AS P2
							WHERE P2.PCN = P.PCN
							AND P2.START_DATE <= P.END_DATE
							AND P.END_DATE < P2.END_DATE )))
	   )
  	RAISERROR('Transaction violates sequenced referential integrity',1,2)
	ROLLBACK TRANSACTION
END
