-- Code Fragment 11.12
ALTER TABLE LOT_MOVE ADD CONSTRAINT FK_MOVE_BKP FOREIGN KEY (FDYD_ID, BKP_ID, WHEN_CHANGED) REFERENCES BKP;


-- Code Fragment 11.13, some errors
ALTER TABLE PEN DROP CONSTRAINT FK_PEN_BKP;

CREATE OR REPLACE TRIGGER PEN_Cur_Ref_Integrity
AFTER INSERT OR DELETE OR UPDATE ON PEN
DECLARE
  valid INTEGER;
BEGIN
  SELECT 1
  INTO valid
  FROM DUAL
  WHERE NOT EXISTS (
    SELECT *
    FROM PEN P
    WHERE NOT EXISTS (
      SELECT *
      FROM BKP B
      WHERE P.FDYD_ID = B.FDYD_ID
        AND P.BKP_ID = B.BKP_ID
        AND (B.OPERATION = 'I' OR B.OPERATION = 'U')
        AND NOT EXISTS (
          SELECT *
          FROM BKP B2
          WHERE B2.FDYD_ID = B.FDYD_ID
            AND B2.BKP_ID = B.BKP_ID
            AND B2.WHEN_CHANGED > B.WHEN_CHANGED ) ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20002, 'FDYD_ID and BKP_ID are current foreign key' );
END;
/

INSERT INTO BKP VALUES
(1, 2, '1998-12', '02-JAN-99', 50, '05-JAN-99', '12-JAN-99', '15-JAN-99', 'D');

INSERT INTO PEN VALUES
	(1, 2, 'B', 200, 10, 2000, 100, 2);

/* The last INSERT statement will cause the following error:
ERROR at line 1:
ORA-20002: FDYD_ID and BKP_ID are current foreign key
ORA-06512: at "WEIL.PEN_CUR_REF_INTEGRITY", line 24
ORA-04088: error during execution of trigger 'WEIL.PEN_CUR_REF_INTEGRITY'
*/

-- Code Fragment 11.14
CREATE OR REPLACE TRIGGER MASS_Curr_Curr_Ref_Integrity
AFTER INSERT OR DELETE OR UPDATE ON MASS_TRTMNT
DECLARE
  valid INTEGER;
BEGIN
  SELECT 1
  INTO valid
  FROM DUAL
  WHERE NOT EXISTS (
    SELECT *
    FROM MASS_TRTMNT M
    WHERE NOT EXISTS (
      SELECT *
      FROM BKP B
      WHERE M.FDYD_ID = B.FDYD_ID
        AND M.BKP_ID = B.BKP_ID
        AND (B.OPERATION = 'I' OR B.OPERATION = 'U')
        AND NOT EXISTS (
          SELECT *
          FROM BKP B2
          WHERE B2.FDYD_ID = B.FDYD_ID
          AND B2.BKP_ID = B.BKP_ID
          AND B2.WHEN_CHANGED > B.WHEN_CHANGED ) ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20003, 'FDYD_ID and BKP_ID are non-sequenced/current foreign key' );
END;
/  

-- Code Fragment 11.15
--  before this is used, 11.11 must be dropped first

CREATE OR REPLACE TRIGGER LOT_LOC_Seq_Cur_Ref_Integrity
AFTER INSERT OR DELETE OR UPDATE ON LOT_LOC
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_LOC LL
    -- LL is one of the last entries
    WHERE (LL.OPERATION = 'I' OR LL.OPERATION = 'U')
    AND NOT EXISTS (
      SELECT *
      FROM LOT_LOC L3
      WHERE L3.FDYD_ID = LL.FDYD_ID
      AND L3.LOT_ID_NUM = LL.LOT_ID_NUM
      AND L3.PEN_ID = LL.PEN_ID
      AND L3.WHEN_CHANGED > LL.WHEN_CHANGED )
    AND ( NOT EXISTS (
              SELECT *
              FROM LOT L
              WHERE LL.FDYD_ID = L.FDYD_ID
              AND LL.LOT_ID_NUM = L.LOT_ID_NUM
              AND L.STOP_DATE = forever
              AND L.FROM_DATE <= LL.FROM_DATE
              AND LL.FROM_DATE < L.TO_DATE )
          OR NOT EXISTS (
              SELECT *
              FROM LOT L
              WHERE LL.FDYD_ID = L.FDYD_ID
                AND LL.LOT_ID_NUM = L.LOT_ID_NUM
                AND L.STOP_DATE = forever
                AND L.FROM_DATE < LL.TO_DATE
                AND LL.TO_DATE <= L.TO_DATE )
          OR EXISTS (
              SELECT *
              FROM LOT L
              WHERE LL.FDYD_ID = L.FDYD_ID
                AND LL.LOT_ID_NUM = L.LOT_ID_NUM
                AND L.STOP_DATE = forever
                AND LL.FROM_DATE < L.TO_DATE
                AND L.TO_DATE < LL.TO_DATE
                AND NOT EXISTS (
                  SELECT *
                  FROM LOT L2
                  WHERE L2.FDYD_ID = L.FDYD_ID
                    AND L2.LOT_ID_NUM = L.LOT_ID_NUM
                    AND L2.STOP_DATE = forever
                    AND L2.FROM_DATE <= L.TO_DATE
                    AND L.TO_DATE < L2.TO_DATE ) ) ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20004, 'FDYD_ID and LOT_ID_NUM are sequenced/current foreign key' );
END;
/

/*  Data value for Code Fragment 11.15

The corresponding date value in the table LOT is:
	FROM_DATE: 03-JAN-98,
	TO_DATE  : 18-FEB-98          
	
So the following 3 INSERT statements will all fail
	
*/

INSERT INTO LOT_LOC VALUES
	(1, 137, 1, 30, '25-DEC-98', '05-JAN-98', 8, '25-JAN-99', 5, '20-JAN-99', 'I' ) ;

INSERT INTO LOT_LOC VALUES
	(1, 137, 1, 30, '25-DEC-98', '01-JAN-98', 8, '25-JAN-99', 5, '20-JAN-99', 'I' ) ;

INSERT INTO LOT_LOC VALUES
	(1, 137, 1, 30, '25-DEC-98', '25-JAN-98', 8, '25-FEB-99', 5, '20-JAN-99', 'I' ) ;

-- Code Fragment 11.16

CREATE OR REPLACE TRIGGER LOT_CO_Cur_Cur_Ref_Integrity
AFTER INSERT OR DELETE OR UPDATE ON LOT_CONTAINS
DECLARE
  valid INTEGER;
  cur_date DATE;
  forever DATE;
BEGIN
  forever := TO_DATE('31-DEC-9999', 'DD-MON-YYYY');
  cur_date := SYSDATE;

  SELECT 1
  INTO valid
  FROM DUAL
  WHERE NOT EXISTS (
    SELECT *
    FROM LOT_CONTAINS C
    WHERE (C.OPERATION = 'I' OR C.OPERATION = 'U')
	 	-- C is the lasted changed entry
      AND NOT EXISTS (
        SELECT *
        FROM LOT_CONTAINS C2
        WHERE C2.FDYD_ID = C.FDYD_ID
          AND C2.LOT_ID_NUM = C.LOT_ID_NUM
          AND C2.WHEN_CHANGED > C.WHEN_CHANGED )
      AND NOT EXISTS (
        SELECT *
        FROM LOT L
        WHERE C.FDYD_ID = L.FDYD_ID
          AND C.LOT_ID_NUM = L.LOT_ID_NUM
          AND L.STOP_DATE = forever
          AND L.FROM_DATE <= cur_date
          AND cur_date < L.TO_DATE ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20005, 'FDYD_ID and LOT_ID_NUM are current/current foreign key' );
END;
/

/* 
Data value used for Code Fragment 11.16, and the last INSERT statement will fail.  
*/
INSERT INTO LOT VALUES
	(1, 138, 10, 'c', '31-DEC-99', 100, 1, 'R', 'LOT1', '03-JAN-99', '28-JAN-99', '01-JAN-98', TO_DATE('31-DEC-9999', 'DD-MON-YYYY')) ;

INSERT INTO APPLICATION VALUES
	('A', 'TEST', '01-JAN-99');

INSERT INTO DBF_FILE VALUES
	('A', 'A', 'JUST FOR TEST', 1);

INSERT INTO LOT_CONTAINS VALUES
	(1, 137, 1, 'A', 'A', 11, '10-JAN-99', 'I');


-- Code Fragment 11.17
--  must delete the trigger in the CF-11.16

CREATE OR REPLACE TRIGGER LOT_CONTAINS_Cur_Ref_Integrity
AFTER INSERT OR DELETE OR UPDATE ON LOT_CONTAINS
DECLARE
  valid INTEGER;
BEGIN
  SELECT 1
  INTO valid
  FROM DUAL
  WHERE NOT EXISTS (
    SELECT *
    FROM LOT_CONTAINS C
    -- C is the last relevant entry
    WHERE (C.OPERATION = 'I' OR C.OPERATION = 'U')
      AND NOT EXISTS (
        SELECT *
        FROM LOT_CONTAINS C2
        WHERE C2.FDYD_ID = C.FDYD_ID
          AND C2.LOT_ID_NUM = C.LOT_ID_NUM
          AND C2.WHEN_CHANGED > C.WHEN_CHANGED )
          -- There is not a match for C in BKP
          AND NOT EXISTS (
            SELECT *
            FROM BKP B
            WHERE B.FDYD_ID = C.FDYD_ID
              AND B.BKP_ID = C.BKP_ID
              -- B is the last relevant entry
              AND (B.OPERATION = 'I' OR B.OPERATION = 'U')
              AND NOT EXISTS (
                SELECT *
                FROM BKP B2
                WHERE B2.FDYD_ID = B.FDYD_ID
                AND B2.BKP_ID = B.BKP_ID
                AND B2.WHEN_CHANGED > B.WHEN_CHANGED ) ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20007, 'FDYD_ID and BKP_ID are current/current foreign key' );
END;
/

-- Code Fragment 11.18

CREATE OR REPLACE TRIGGER LOT_MOVE_Seq_Cur_Ref_Integrity
AFTER INSERT OR DELETE OR UPDATE ON LOT_MOVE
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_MOVE M
    WHERE (M.OPERATION = 'I' OR M.OPERATION = 'U')
      AND NOT EXISTS (
        SELECT *
        FROM LOT_MOVE M2
        WHERE M2.FDYD_ID = M.FDYD_ID
          AND M2.LOT_ID_NUM = M.LOT_ID_NUM
          AND M2.FROM_PEN_ID = M.FROM_PEN_ID
          AND M2.TO_PEN_ID = M.TO_PEN_ID
          AND M2.WHEN_CHANGED > M.WHEN_CHANGED )
      AND NOT EXISTS (
        SELECT *
        FROM LOT L
        WHERE M.FDYD_ID = L.FDYD_ID
          AND M.LOT_ID_NUM = L.LOT_ID_NUM
          AND L.STOP_DATE = forever
          AND L.FROM_DATE <= M.AT_DATE
          AND M.AT_DATE < L.TO_DATE ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20006, 'FDYD_ID and LOT_ID_NUM are sequenced/current foreign key' );
END;
/

/* Data value for Code Fragment 11.18 and the last INSERT will fail 
ERROR at line 1:
ORA-20006: FDYD_ID and LOT_ID_NUM are sequenced/current foreign key
ORA-06512: at "WEIL.LOT_MOVE_SEQ_CUR_REF_INTEGRITY", line 31
ORA-04088: error during execution of trigger
'WEIL.LOT_MOVE_SEQ_CUR_REF_INTEGRITY'
*/

DROP TRIGGER PEN_Cur_Ref_Integrity ;  -- trigger dropped for testing new data

INSERT INTO PEN VALUES
   (1, 2, 'B', 200, 10, 2000, 100, 2);
	
INSERT INTO LOT_MOVE VALUES
	(1, 137, 1, 2, 23, 1, 'A', 'A', '23-JAN-99', 1, '15-JAN-99', 'I');


-- Code Fragment 11.19
--  must drop first trigger MASS_CURR_CURR_REF_INTEGRITY

CREATE OR REPLACE TRIGGER MASS_Seq_Ref_Integrity
AFTER INSERT OR DELETE OR UPDATE ON MASS_TRTMNT
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 MASS_TRTMNT M
    WHERE NOT EXISTS (
      SELECT *
      FROM LOT L
      WHERE M.FDYD_ID = L.FDYD_ID
        AND M.LOT_ID_NUM = L.LOT_ID_NUM
        AND L.STOP_DATE = forever
        AND L.FROM_DATE <= M.AT_DATE
        AND M.AT_DATE < L.TO_DATE ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20008, 'FDYD_ID and LOT_ID_NUM are valid-time sequenced foreign key' );
END;
/

/* Data for Code Fragment 11.19 and the last INSERT will fail
ERROR at line 1:
ORA-20008: FDYD_ID and LOT_ID_NUM are valid-time sequenced foreign key
ORA-06512: at "WEIL.MASS_SEQ_REF_INTEGRITY", line 22
ORA-04088: error during execution of trigger 'WEIL.MASS_SEQ_REF_INTEGRITY'
*/
INSERT INTO PEN VALUES
	(1, 1, 'A', 100, 200, 2000, 200, 1);

INSERT INTO MASS_TRTMNT VALUES
	(1, 137, 1, 10, 'A', 23, 1, '05-JAN-98');

INSERT INTO MASS_TRTMNT VALUES
	(1, 137, 1, 10, 'A', 23, 1, '05-JAN-99');

