Chapter 7
Pages 177-79
Example 7-2

SQL*Plus Script to Create and Verify Triggers to Prevent
Overlap and Gaps in a Simple Time-Series Table

REM
REM test script to demonstrate checking for overlapping
REM effective dates and gaps in the time series
REM
REM note the granularity assumed to be a day
REM
CREATE TABLE contig
  ( date_from DATE NOT NULL
    CONSTRAINT contig_date_from_time CHECK (date_from = TRUNC(date_from))
  , date_to DATE NOT NULL
    CONSTRAINT contig_date_to_time CHECK (date_to = TRUNC(date_to))
  , CONSTRAINT contig_range CHECK (date_to >= date_from)
  , CONSTRAINT contig_pk PRIMARY KEY (date_to)
  );

CREATE OR REPLACE TRIGGER contig_biur
BEFORE INSERT OR UPDATE ON contig FOR EACH ROW
BEGIN
   IF :new.DATE_TO IS NULL
   THEN :new.DATE_TO := TO_DATE('31-DEC-4712','DD-MON-YYYY');
   END IF;
END;
/

CREATE OR REPLACE TRIGGER contig_aiud
AFTER INSERT OR DELETE OR UPDATE ON contig
DECLARE
   l_dummy VARCHAR2(1);
   l_prev_date_to DATE;
   CURSOR c_contig_overlap IS SELECT 'x'
                         FROM    contig contig1
                                ,contig CONTIG2
                         WHERE   contig1.date_from <= contig2.date_to
                         AND     contig1.date_to >= contig2.date_from
                         AND     contig1.date_to <> contig2.date_to;
   CURSOR c_contig_gaps IS SELECT TRUNC(contig.date_from) date_from
                            ,TRUNC(contig.date_to) date_to
                      FROM   contig
                      ORDER BY contig.date_to;
BEGIN
   IF INSERTING OR UPDATING THEN
      -
      - Check that we don't have overlap
      -
      OPEN c_contig_overlap;
      FETCH c_contig_overlap INTO l_dummy;
         IF c_contig_overlap%FOUND THEN
           CLOSE c_contig_overlap;
           RAISE_APPLICATION_ERROR(-20001,'Overlap detected');
         END IF;
      CLOSE c_contig_overlap;
   END IF;

   IF DELETING OR INSERTING THEN
      -
      - Check that we don't have gaps
      -
      FOR c_gaps IN c_contig_gaps LOOP
        IF c_gaps.date_from <> NVL(l_prev_date_to + 1,c_gaps.date_from) 
THEN
           RAISE_APPLICATION_ERROR(-20002,'Gap detected');
        END IF;
        l_prev_date_to := c_gaps.date_to;
      END LOOP;
   END IF;
END;
/

REM
REM Valid actions on the tables.
REM ============================
REM Terminating the last row in the time series
REM and adding new ones after it
REM

INSERT INTO contig VALUES('01-JAN-90',NULL);
UPDATE contig SET date_to = '31-JAN-90' WHERE date_from = '01-JAN-90';
insert into CONTIG values('01-feb-90',null);
UPDATE contig SET date_to = '28-FEB-90' where date_from = '01-FEB-90';
INSERT INTO contig VALUES('01-MAR-90',NULL);

REM
REM Data Now looks like:
REM   DATE_FROM          DATE_TO
REM   ------ -----
REM   01-JAN-1990 31-JAN-1990
REM   01-FEB-1990 28-FEB-1990
REM   01-MAR-1990 31-DEC-4712
REM
REM Now try some invalid actions
REM              ===============
REM 1. Take out the middle Row (create a gap) - will generate
REM     ORA-20002: Gap detected
REM     ORA-06512: at line 35
REM     ORA-04088: error during execution of trigger
REM                                         'ORIGIN.CONTIG_AIUD'
REM

DELETE contig WHERE date_from = '01-FEB-90';

REM
REM 2. Create an overlap - will generate
REM    ORA-20001: Overlap detected
REM    ORA-06512: at line 23
REM    ORA-04088: error during execution of trigger
REM                                         'ORIGIN.CONTIG_AIUD'
REM

INSERT INTO contig VALUES('15-FEB-90','20_FEB-90');

REM
REM 3. Suppose we got the boundaries wrong and the 1st period
REM    should end on 15-JAN-90 and the 2nd one start on
REM    16-JAN-90
REM    The challenge is to do it in a single statement so as
REM    not to create transient gaps of overlaps
REM

UPDATE contig SET date_to = DECODE(date_to,'31-JAN-90','15-JAN-90',date_to)
           ,date_from = DECODE(date_from,'01-FEB-90','16-JAN-90',date_from)
      WHERE  date_to IN ('31-JAN-90','28-FEB-90');

REM
REM table now looks like this:
REM   DATE_FROM          DATE_TO
REM   ------ -----
REM   01-JAN-1990 15-JAN-1990
REM   16-JAN-1990 28-FEB-1990
REM   01-MAR-1990 31-DEC-4712
REM
REM 4. Let's put the table back how it was, if solution 3 is
REM    too obscure or messy, then the alternative is to delete
REM    back to where the change starts from and re-insert from there.

DELETE contig WHERE date_to >= '28-FEB-90';
UPDATE contig SET date_to = '31-JAN-90' WHERE date_from = '01-JAN-90';
INSERT INTO contig VALUES ('01-FEB-90','28-FEB-90');
INSERT INTO contig VALUES ('01-MAR-90',NULL);