
// CF 5.8

// Incumbents table

CREATE TABLE Incumbents (
                        SSN             integer,
                        PCN             integer,
                        StartDate       date,
                        EndDate         date
                        );

// No assertions; triggers used instead

CREATE TRIGGER inc_chk
BEFORE COMMIT
IF (EXISTS (SELECT I1.SSN
              FROM Incumbents AS I1
              WHERE 1 < (SELECT COUNT(SSN)
                 FROM Incumbents AS I2
                 WHERE I1.SSN = I2.SSN AND I1.PCN = I2.PCN
                    AND I1.EndDate = DATE '01/01/3000'
                    AND I2.EndDate = DATE '01/01/3000'))
)
EXECUTE REJECT;

