ALTER TABLE PEN DROP CONSTRAINT FK_PEN_BKP;
ALTER TABLE LOT_MOVE DROP CONSTRAINT FK_MOVE_BKP;
ALTER TABLE MASS_TRTMNT DROP CONSTRAINT FK_MASS_BKP;
ALTER TABLE LOT_CONTAINS DROP CONSTRAINT FK_CO_BKP;

ALTER TABLE LOT_MOVE DROP CONSTRAINT FK_MOVE_LOT;
ALTER TABLE LOT_LOC DROP CONSTRAINT FK_LOC_LOT;
ALTER TABLE MASS_TRTMNT DROP CONSTRAINT FK_MASS_LOT;
ALTER TABLE LOT_CONTAINS DROP CONSTRAINT FK_CO_LOT;

-- Code Fragment 11.7
ALTER TABLE MASS_TRTMNT DROP PRIMARY KEY;
ALTER TABLE MASS_TRTMNT ADD PRIMARY KEY (FDYD_ID, LOT_ID_NUM, PEN_ID, AT_DATE);

-- Code Fragment 11.8
ALTER TABLE BKP DROP PRIMARY KEY;
ALTER TABLE BKP ADD PRIMARY KEY (FDYD_ID, BKP_ID, WHEN_CHANGED);

ALTER TABLE LOT_CONTAINS DROP PRIMARY KEY;
ALTER TABLE LOT_CONTAINS ADD PRIMARY KEY (FDYD_ID, LOT_ID_NUM, WHEN_CHANGED);

-- Code Fragment 11.9
ALTER TABLE LOT DROP PRIMARY KEY ;
ALTER TABLE LOT ADD PRIMARY KEY (FDYD_ID, LOT_ID_NUM, FROM_DATE, STOP_DATE);

CREATE OR REPLACE TRIGGER LOT_seq_seq_primary_key
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 L1.FROM_DATE < L2.TO_DATE
            AND L2.FROM_DATE < L1.TO_DATE
            AND L2.STOP_DATE = forever
            AND L1.rowid <> L2.rowid  ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20000, 'FDYD_ID and LOT_ID_NUM are sequenced primary keys' );
END;
/

INSERT INTO FDYD VALUES
	(1, 'R', 'S') ;
	
INSERT INTO LOT VALUES
   (1, 137, 10, 'c', '31-DEC-99', 100, 1, 'R', 'LOT1', '03-JAN-98', '18-FEB-98', '01-JAN-98', TO_DATE('31-DEC-9999', 'DD-MON-YYYY')) ;

INSERT INTO LOT VALUES
   (1, 137, 10, 'c', '31-DEC-99', 100, 1, 'R', 'LOT2', '23-JAN-98', '28-FEB-98', '01-JAN-98', TO_DATE('31-DEC-9999', 'DD-MON-YYYY')) ;

/*
The last insert statement will cause the following error:

ERROR at line 1:
ORA-20000: FDYD_ID and LOT_ID_NUM are sequenced primary keys
ORA-06512: at "WEIL.LOT_SEQ_SEQ_PRIMARY_KEY", line 24
ORA-04088: error during execution of trigger 'WEIL.LOT_SEQ_SEQ_PRIMARY_KEY'
*/

-- Code Fragment 11.10
ALTER TABLE LOT_LOC DROP PRIMARY KEY;
ALTER TABLE LOT_LOC ADD PRIMARY KEY (FDYD_ID, LOT_ID_NUM, PEN_ID, FROM_DATE, WHEN_CHANGED);


-- Code Fragment 11.11
CREATE OR REPLACE TRIGGER LOT_LOC_seq_seq_primary_key
AFTER INSERT OR DELETE OR UPDATE ON LOT_LOC
DECLARE
  valid INTEGER;
BEGIN
  SELECT 1
  INTO valid
  FROM DUAL
  WHERE NOT EXISTS
    ( SELECT *
      FROM LOT_LOC L1
		-- L1 is the last insert or update entry
      WHERE (L1.OPERATION='I' OR L1.OPERATION='U')
        AND TO_CHAR(L1.WHEN_CHANGED, 'DD-MON-YYYY') = TO_CHAR( SYSDATE, 'DD-MON-YYYY')
        AND EXISTS (
          SELECT *
          FROM LOT_LOC L2
          WHERE L1.FDYD_ID = L2.FDYD_ID
            AND L1.LOT_ID_NUM = L2.LOT_ID_NUM
            AND L1.PEN_ID = L2.PEN_ID
            AND L1.FROM_DATE < L2.TO_DATE
            AND L2.FROM_DATE < L1.TO_DATE
            AND L1.rowid <> L2.rowid 
            -- L2 is the last insert or update entry
            AND (L2.OPERATION='I' OR L2.OPERATION='U')
            AND NOT EXISTS (
              SELECT *
              FROM LOT_LOC L3
              WHERE L3.FDYD_ID = L2.FDYD_ID
                AND L3.LOT_ID_NUM = L2.LOT_ID_NUM
                AND L3.PEN_ID = L2.PEN_ID
					 AND L3.FROM_DATE = L2.FROM_DATE
					 AND L3.TO_DATE = L2.TO_DATE
                AND L3.WHEN_CHANGED > L2.WHEN_CHANGED ) ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20001, 'FDYD_ID, LOT_ID_NUM and PEN_ID are sequenced primary keys' );
END;
/

INSERT INTO BKP VALUES
	(1, 1, '1998-12', '02-JAN-99', 100, '05-JAN-99', '12-JAN-99', '15-JAN-99', 'I' ) ;

INSERT INTO PEN VALUES
   (1, 1, 'L', 100, 10, 1000, 200, 1) ;

INSERT INTO LOT_LOC VALUES
   (1, 137, 1, 28, '25-DEC-98', '03-JAN-99', 11, '25-JAN-99', 5, '27-JAN-99', 'I' ) ;

INSERT INTO LOT_LOC VALUES
   (1, 137, 1, 31, '25-DEC-98', '10-JAN-99', 12, '27-JAN-99', 6, '27-JAN-99', 'I' ) ;



