-- Code Fragment 11.22

CREATE OR REPLACE TRIGGER BKP_Contiguous_History
AFTER INSERT OR DELETE OR UPDATE ON BKP
DECLARE
  valid INTEGER;
BEGIN
  SELECT 1
  INTO valid
  FROM DUAL
  WHERE NOT EXISTS (
    SELECT *
    FROM BKP B, BKP B2
    WHERE B.WHEN_CHANGED < B2.WHEN_CHANGED
      AND B.FDYD_ID = B2.FDYD_ID
      AND B.BKP_ID = B2.BKP_ID
      AND B.OPERATION = 'D' AND B2.OPERATION = 'I'
      -- I is the last operation
      AND NOT EXISTS (
        SELECT *
        FROM BKP B3
        WHERE B3.FDYD_ID = B.FDYD_ID
          AND B3.BKP_ID = B.BKP_ID
          AND B2.WHEN_CHANGED < B3.WHEN_CHANGED )
      -- There are no operations between the delete and the insert
      AND NOT EXISTS (
        SELECT *
        FROM BKP B3
        WHERE B3.FDYD_ID = B.FDYD_ID
          AND B3.BKP_ID = B.BKP_ID
          AND B.WHEN_CHANGED < B3.WHEN_CHANGED
          AND B3.WHEN_CHANGED < B2.WHEN_CHANGED ) );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR( -20011, 'BKPs primary key is contiguous' );
END;
/

/* Data in Code Fragment 11.22
INSERT INTO BKP VALUES
	(1, 8, '1998-12', '02-JAN-99', 100, '05-JAN-99', '12-JAN-99', '18-JAN-99', 'D' ) ;

INSERT INTO BKP VALUES
	(1, 8, '1998-12', '02-JAN-99', 100, '05-JAN-99', '12-JAN-99', '22-JAN-99', 'I' ) ;
*/



