检查表的任何行上是否有重叠的日期Oracle SQL [英] Check for overlapped dates on any row of a table Oracle SQL

问题描述

我有以下架构:

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)
);

以下触发器，每当将EPOCA插入数据库并且DATA_FIM和DATA_INI之间的时间段与其他EPOCAS的其他时间段重叠时，就会引发错误.

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;

认为:如果我定义了8月1日至8月30日之间的夏季，则无法定义该时间段内的一年中的其他时间段，也不能用6月1日至6月30日之间的日期或日期来更新现有时间段.8月30日.

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.

现在，我可以插入任何与表中其他日期相同的日期的EPOCA，并且可以使用其他EPOCAS的日期来更新任何EPOCA的日期，这样我就可以了.我可以更改什么?

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?

推荐答案

我想您需要像这样的触发器:

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;

请注意，未提供 FOR EACH ROW 子句！

否则，触发器仅执行当前插入/更新的行，而不与任何现有数据进行比较.

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

还考虑以下情况:

在表格中，您有一个从8月1日到30日的期间，然后尝试添加从5月1日到12月31日的期间.当然，这种情况也应由触发器阻止.因此，您只需要一个语句级触发器，即仅检查插入/更新的行的行级触发器是不够的.

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.

这篇关于检查表的任何行上是否有重叠的日期Oracle SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！