
/* Code Fragment 4.7 (SSN, PCN) is a sequenced primary key for INCUMBENTS. */

CREATE  TRIGGER Seq_Primary_Key
ON INCUMBENTS
FOR INSERT, UPDATE, DELETE
AS
BEGIN
	IF (( EXISTS (	SELECT I1.SSN
               	      	FROM INCUMBENTS AS I1
			WHERE 1 < (	SELECT COUNT(I2.SSN)
				   	FROM INCUMBENTS AS I2
               			   	WHERE I1.SSN = I2.SSN AND I1.PCN=I2.PCN
					AND I1.START_DATE < I2.END_DATE
                  			AND I2.START_DATE < I1.END_DATE)))
            OR
      	    ( EXISTS (	SELECT *
               	      	FROM INCUMBENTS AS I
               	      	WHERE I.SSN IS NULL OR I.PCN IS NULL))
	   )

		RAISERROR('Transaction violates sequenced constraint',1,2)
	      	rollback transaction
END
