
// CF 5.15

// Positions table

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

// No assertions; triggers used instead

CREATE TRIGGER pos_conthist
BEFORE COMMIT
IF (EXISTS (SELECT *
              FROM Positions AS P, Positions AS P2
             WHERE P.EndDate < P2.StartDate
               AND P.PCN = P2.PCN
               AND NOT EXISTS (
                      SELECT *
                        FROM Positions AS P3
                       WHERE P3.PCN = P.PCN
                         AND (
                           ((P3.StartDate <= P.EndDate)
                            AND (P.EndDate < P3.EndDate)) OR
                           ((P3.StartDate < P2.StartDate)
                            AND (P.StartDate <= P3.EndDate))))))
EXECUTE REJECT;

