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

  -- If this SELECT succeeds w/o raising the NO_DATA_FOUND exception,
  --   then this insert/update ensures no time holes for a position validity

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

END;
/
