-- -----------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
--
-- Sequenced Primary Key for INCUMBENTS
-- INCUMBENTS is temporal
-- By   : Bruce C. Huang
-- Date : Dec. 12, 1997
--
-- Note:  There can only be 1 UPDATE/INSERT trigger
--        for table INCUMBENTS.  So future triggers
--        involving INCUMBENTS must be either
--        merged with this one or replaced.
-- -----------------------------------------------
REM
REM Code Fragment 5.8
REM Oracle 7.3 Version
REM
REM Oracle 7.3 table constraints cannot handle complex SQL92 "CHECK(..)"
REM conditions, so a trigger is used to enforce the CHECK conditions.

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,START_DATE,END_DATE)
 )
;

CREATE OR REPLACE TRIGGER prevent_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 = TO_DATE('3000-01-01','YYYY-MM-DD')
                         AND I2.END_DATE = TO_DATE('3000-01-01','YYYY-MM-DD')
                         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 employee that CURRENTLY
  --   has 2 or more positions

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