-- Code Fragment 11.20
--  drop first trigger LOT_CUR_CUR_REF_INTEGRITY

ALTER TABLE LOT DROP UNIQUE (FDYD_ID, LOT_ID_NUM);

CREATE OR REPLACE TRIGGER LOT_ID_Seq_Curr_UNIQUE
AFTER INSERT OR DELETE OR UPDATE ON LOT
DECLARE
  valid INTEGER;
  forever DATE;
BEGIN
  forever := TO_DATE('31-DEC-9999', 'DD-MON-YYYY');
  SELECT 1
  INTO valid
  FROM DUAL
  WHERE NOT EXISTS (
    SELECT *
    FROM LOT L1
    WHERE L1.STOP_DATE = forever
      AND EXISTS (
        SELECT *
        FROM LOT L2
        WHERE L1.FDYD_ID = L2.FDYD_ID
          AND L1.LOT_ID_NUM = L2.LOT_ID_NUM
          AND L2.STOP_DATE = forever
          AND L1.FROM_DATE < L2.TO_DATE
          AND L2.FROM_DATE < L1.TO_DATE
          AND L2.rowid <> L1.rowid ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20009, 'FDYD_ID and LOT_ID_NUM are sequenced/current unique' );
END;
/

/* Data for Code Fragment 11.20
INSERT INTO LOT VALUES
   (1, 137, 10, 'c', '31-DEC-99', 100, 1, 'R', 'LOT1', '10-JAN-98', '28-FEB-98', '01-JAN-98', TO_DATE('31-DEC-9999', 'DD-MON-YYYY')) ;
*/


