
// CF 5.12

// 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_curr_refint
BEFORE COMMIT
IF (EXISTS (SELECT *
              FROM Incumbents AS I
              WHERE I.EndDate = DATE '01/01/3000'
                AND NOT EXISTS (
                        SELECT *
                          FROM Positions AS P
                          WHERE I.PCN = P.PCN
                            AND P.EndDate = DATE '01/01/3000'))
)
EXECUTE REJECT;

