Oracle SQL使用触发器检查重叠的日期 [英] Oracle SQL checking for overlapped dates using a trigger

问题描述

我有一个名为EPOCA的表，具有以下架构:

I have the table named EPOCA with this schema:

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

即使我已经检查了起始日期是否短于结束日期，我仍需要检查一下，当我插入新的EPOCA时，插入的日期不会与任何当前日期重叠.

And even though I already check if the initial date is smaller then the end date, I need to check that when I insert a new EPOCA the dates I insert will not overlap with any of the present dates.

我开发了此触发器:

CREATE OR REPLACE TRIGGER TRGEPOCASNAOSOBREPOSTAS
BEFORE INSERT OR UPDATE
ON EPOCA
FOR EACH ROW

DECLARE
    FLAG_DATE NUMBER;
BEGIN
    FLAG_DATE := 0;

    IF INSERTING THEN
        SELECT E.ID INTO FLAG_DATE FROM EPOCA E WHERE E.DATE_INITIAL < :NEW.DATE_INITIAL AND E.DATE_END > :NEW.DATE_END;
        IF FLAG_DATE <> 0 THEN
            RAISE_APPLICATION_ERROR(-2098, 'INSERT FAILED BECAUSE SELECTED DATES OVERLAP EXISTENT ONES');
        END IF;
    ELSIF UPDATING THEN
        SELECT E.ID INTO FLAG_DATE FROM EPOCA E WHERE E.DATE_INITIAL < :NEW.DATE_INITIAL AND E.DATA_END > :NEW.DATA_END;
        IF FLAG_DATE <> 0 THEN
            RAISE_APPLICATION_ERROR(-2099, 'UPDATE FAILED BECAUSE SELECTED DATES OVERLAP EXISTENT ONES');
        END IF;
    END IF;
END;

我在插入时遇到的错误是因为在触发器的第7行上没有找到数据，而当我更新表时，该错误是因为表处于突变状态并且触发器无法读取表，这是有道理的，但是我不知道该怎么解决.

The error I am getting when inserting is because no data was found on line 7 of the trigger and when I am updating the table the error is because the table is in mutation and the trigger cannot read the table, which makes sense but I don't know how to solve it.

关于如何解决此问题的任何建议?

Any suggestions on how to solve this problem?

推荐答案

您应该在两个地方的 select 查询中都使用 count 聚合函数，因为它总是返回tge即使它为零也要进行计数，如下所示:

You should use the count aggregate function in your select query at both places as it always returns tge count evenif it is zero as follows:

...
...
SELECT count(*) INTO FLAG_DATE FROM EPOCA E WHERE E.DATA_INI < :NEW.DATA_INI AND E.DATA_FIM > :NEW.DATA_FIM;
IF FLAG_DATE <> 0 THEN
...
...

这篇关于Oracle SQL使用触发器检查重叠的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！