Check for overlapped dates on any row of a table Oracle SQL

I have the following schema:

CREATE TABLE EPOCA
(
    ID       INT
        CONSTRAINT PK_EPOCA PRIMARY KEY,
    NOME     VARCHAR(250),
    DATA_INI DATE
        CONSTRAINT NN_EPOCA_DATA_INI NOT NULL,
    DATA_FIM DATE,
    CONSTRAINT CK_EPOCA_DATAS CHECK (DATA_INI < DATA_FIM)
);

​x
 
CREATE TABLE EPOCA(    ID       INT        CONSTRAINT PK_EPOCA PRIMARY KEY,    NOME     VARCHAR(250),    DATA_INI DATE        CONSTRAINT NN_EPOCA_DATA_INI NOT NULL,    DATA_FIM DATE,    CONSTRAINT CK_EPOCA_DATAS CHECK (DATA_INI < DATA_FIM));​

And the following trigger, that is supposed to raise an error whenever an EPOCA is inserted into the database and the period between DATA_FIM and DATA_INI is overlapped with other periods of other EPOCAS.

CREATE OR REPLACE TRIGGER TRGEPOCASNAOSOBREPOSTAS
    AFTER INSERT OR UPDATE
    ON EPOCA
    FOR EACH ROW
BEGIN
    IF INSERTING THEN
        IF :OLD.DATA_INI <= :NEW.DATA_INI AND :OLD.DATA_FIM >= :NEW.DATA_FIM THEN
            RAISE_APPLICATION_ERROR(-20021, 'INSERT FAILED BECAUSE SELECTED DATES OVERLAP EXISTENT ONES');
        END IF;
    ELSIF UPDATING THEN
        IF :OLD.DATA_INI <= :NEW.DATA_INI AND :OLD.DATA_FIM >= :NEW.DATA_FIM THEN
            RAISE_APPLICATION_ERROR(-20022, 'UPDATE FAILED BECAUSE SELECTED DATES OVERLAP EXISTENT ONES');
        END IF;
    END IF;
END;

 
CREATE OR REPLACE TRIGGER TRGEPOCASNAOSOBREPOSTAS    AFTER INSERT OR UPDATE    ON EPOCA    FOR EACH ROWBEGIN    IF INSERTING THEN        IF :OLD.DATA_INI <= :NEW.DATA_INI AND :OLD.DATA_FIM >= :NEW.DATA_FIM THEN            RAISE_APPLICATION_ERROR(-20021, 'INSERT FAILED BECAUSE SELECTED DATES OVERLAP EXISTENT ONES');        END IF;    ELSIF UPDATING THEN        IF :OLD.DATA_INI <= :NEW.DATA_INI AND :OLD.DATA_FIM >= :NEW.DATA_FIM THEN            RAISE_APPLICATION_ERROR(-20022, 'UPDATE FAILED BECAUSE SELECTED DATES OVERLAP EXISTENT ONES');        END IF;    END IF;END;​

Think of it as: if I define the summer between june 1st and 30th of august I cannot define anyother period of the year with that period of time, nor can I update an existing period with those dates or nothing between june 1st and 30th august.

Right now I can insert any EPOCA with the same date as any other present in the table and I can update the date of any EPOCA with the dates of other EPOCAS and it allows me. What can I change?

Answer

I guess you would need a trigger like this one:

CREATE OR REPLACE TRIGGER TRGEPOCASNAOSOBREPOSTAS
    AFTER INSERT OR UPDATE
    ON EPOCA
  c INTEGER;
BEGIN
    SELECT COUNT(*)
    INTO c
    FROM EPOCA e
    WHERE EXISTS (
        SELECT 1
        FROM EPOCA ee 
        WHERE (e.DATA_INI BETWEEN ee.DATA_INI AND ee.DATA_FIM 
              OR e.DATA_FIM BETWEEN ee.DATA_INI AND ee.DATA_FIM) 
           AND ee.ROWID <> e.ROWID);

    IF c > 0 THEN
            RAISE_APPLICATION_ERROR(-20021, 'INSERT FAILED BECAUSE SELECTED DATES OVERLAP EXISTENT ONES');
    END IF;
END;

 
CREATE OR REPLACE TRIGGER TRGEPOCASNAOSOBREPOSTAS    AFTER INSERT OR UPDATE    ON EPOCA  c INTEGER;BEGIN    SELECT COUNT(*)    INTO c    FROM EPOCA e    WHERE EXISTS (        SELECT 1        FROM EPOCA ee         WHERE (e.DATA_INI BETWEEN ee.DATA_INI AND ee.DATA_FIM               OR e.DATA_FIM BETWEEN ee.DATA_INI AND ee.DATA_FIM)            AND ee.ROWID <> e.ROWID);​    IF c > 0 THEN            RAISE_APPLICATION_ERROR(-20021, 'INSERT FAILED BECAUSE SELECTED DATES OVERLAP EXISTENT ONES');    END IF;END;​

Note, the FOR EACH ROW clause is not given!

Otherwise the trigger performs only the currently inserted/updated row but does not compare to any existing data.

Consider also cases like this:

In the table you have a period from 1st to 30th of August, then you try to add period for 1st of May to 31th of December. Of course, such situations should be also blocked by the trigger. Thus you need only a statement-level trigger, i.e. a row level trigger which checks only the inserted/updated row is not sufficient.

Advertisement

Answer