-- -----------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
--
-- Sequenced Referential Integrity
-- By   : Bruce C. Huang
-- Date : Dec. 12, 1997
--
-- There can only be 1 trigger on an INSERT/UPDATE
-- for table INCUMBENTS.
-- -----------------------------------------------
REM
REM Code Fragment 5.14 in Oracle 7.3
REM There is no ASSERTIONS in Oracle 7.3, only Triggers.
REM
CREATE OR REPLACE TRIGGER seq_ref_integrity
AFTER INSERT or UPDATE on INCUMBENTS
DECLARE 
   valid             INTEGER;
BEGIN
   SELECT 1
   INTO   valid
   FROM   DUAL
   WHERE  NOT EXISTS( SELECT *
                      FROM   INCUMBENTS I
                      WHERE  NOT EXISTS( SELECT *
                                         FROM  POSITIONS P
                                         WHERE I.PCN = P.PCN
                                           AND P.START_DATE <= I.START_DATE
                                           AND I.START_DATE < P.END_DATE)
                          OR NOT EXISTS( SELECT *
                                         FROM  POSITIONS P
                                         WHERE I.PCN = P.PCN
                                           AND P.START_DATE < I.END_DATE
                                           AND I.END_DATE <= P.END_DATE)
                          OR EXISTS( SELECT *
                                     FROM  POSITIONS P
                                     WHERE I.PCN = P.PCN
                                       AND I.START_DATE < P.END_DATE
                                       AND P.END_DATE < I.END_DATE
                                       AND NOT EXISTS( SELECT *
                                                       FROM  POSITIONS P2
                                                       WHERE P2.PCN = P.PCN
                                                         AND P2.START_DATE <= P.END_DATE
                                                         AND P.END_DATE < P2.END_DATE)));

  -- If this SELECT succeeds w/o raising the NO_DATA_FOUND exception,
  --   then this insert/update ensures that this incumbent's PCN
  --   is valid for the period in the POSITIONS table.

  EXCEPTION
     WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR ( -20001 , 
                                  'Sequenced Positions Referential Violated' );
        -- Abort the offending transaction, and
        --    rollback to a consistent state.

END;
/
