
// CF 5.16

// Positions table

CREATE TABLE Positions (
                        PCN             integer,
                        JobTitleCode1   integer,
                        StartDate       date,
                        EndDate         date
                       );

// Incumbents table

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


// No assertions; triggers used instead

CREATE TRIGGER inc_seq_refint
BEFORE COMMIT
IF (EXISTS (SELECT *
              FROM Incumbents AS I
             WHERE NOT EXISTS (
                  SELECT *
                    FROM Positions AS P
                   WHERE I.PCN = P.PCN
                     AND P.StartDate <= I.StartDate
                     AND I.StartDate < P.EndDate)
                OR NOT EXISTS (
                  SELECT *
                    FROM Positions AS P
                   WHERE I.PCN = P.PCN
                     AND P.StartDate < I.StartDate
                     AND I.StartDate <= P.EndDate))
)
EXECUTE REJECT;

