-- Code Fragment 11.23

DROP TRIGGER LOT_ID_Seq_Curr_UNIQUE;

CREATE OR REPLACE TRIGGER LOT_ID_VT_Invariant_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.LOT_ID <> L2.LOT_ID really having this
          AND L1.FROM_DATE < L2.TO_DATE
          AND L2.FROM_DATE < L1.TO_DATE
          AND L1.rowid <> L2.rowid ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20012, 'FDYD_ID and LOT_ID_NUM is valid time-invariant unique' );
END;
/


-- Code Fragment 11.23

DROP TRIGGER LOT_ID_Seq_Curr_UNIQUE;

CREATE OR REPLACE TRIGGER LOT_ID_VT_Invariant_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.LOT_ID <> L2.LOT_ID
          AND L1.rowid <> L2.rowid ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20012, 'FDYD_ID and LOT_ID_NUM is valid time-invariant unique' );
END;
/


