-- -----------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
--
-- Preventing Sequenced Duplicates in INCUMBENTS
-- By   : Bruce C. Huang
-- Date : Dec. 12, 1997
--
-- -----------------------------------------------
REM
REM Code Fragment 5.10
REM Oracle 7.3 Version
REM There can only be 1 insert/update trigger on a table.  In other words,
REM the trigger prevent_seq_dups cannot be used in conjunction with CF5.4 or
REM CF5.5.
REM
REM Drop INCUMBENTS if already exists
DROP Table INCUMBENTS;

Create Table INCUMBENTS(
  SSN		VARCHAR2(11),
  PCN		VARCHAR2(6),
  START_DATE    DATE,
  END_DATE      DATE,
  UNIQUE(SSN,PCN,END_DATE)
 )
;

----------------------------------------------------------------------------
- Oracle 7.3 Cannot process complex table constraints (CHECK).  So a 
- Trigger is used instead to prevent sequenced duplicate.
----------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER prevent_seq_dups
AFTER INSERT or UPDATE on INCUMBENTS
DECLARE 
   valid             INTEGER;
BEGIN
   SELECT 1
   INTO   valid
   FROM   DUAL
   WHERE  NOT EXISTS( SELECT I1.SSN
                      FROM   INCUMBENTS I1 , INCUMBENTS I2
                      WHERE  I1.SSN = I2.SSN
                         AND I1.PCN = I2.PCN
                         AND I1.END_DATE <= I2.END_DATE
                         AND I2.END_DATE <= I1.END_DATE
                         AND I1.ROWID != I2.ROWID );

  -- If this SELECT succeeds w/o raising the NO_DATA_FOUND exception,
  --   then this insert/update does not have an overlap period for a SSN 
  --   and PCN.

  EXCEPTION
     WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR ( -20001 , 
                                  'There is a SEQUENCED Duplicate');
        -- Abort the offending transaction, and
        --    rollback to a consistent state.

END;
/
