Solution to Oracle mutating trigger

Question

I am stuck in a small requirement. my table should restrict if any overlapping data is getting inserted or updated.

Below is my try so far:

CREATE TABLE my_table (
    ID          NUMBER,
    startdate   DATE,
    enddate     DATE,
    CONSTRAINT my_table_pk PRIMARY KEY ( ID,startdate,enddate )
);
/
CREATE OR REPLACE TRIGGER trg_my_table_biu
    BEFORE INSERT OR UPDATE 
    ON my_table
    FOR EACH ROW
DECLARE 
    v_count NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO v_count
    FROM my_table
    WHERE  id = :new.id
    AND startdate < = :new.enddate
    AND enddate   >= :new.startdate;

    IF v_count >= 1 THEN
        raise_application_error( -20001, 'Cannot make the data overlapped.!' );
    END IF;
END;

/
--existing data - good data - Result: Success

INSERT INTO my_table VALUES (1, to_date('01/02/2018','dd/mm/yyyy '),to_date('01/03/2018','dd/mm/yyyy '));

--1 good data - Result: Success
INSERT INTO my_table VALUES (1, to_date('01/01/2018','dd/mm/yyyy '),to_date('15/01/2018','dd/mm/yyyy '));   

--2 good data - Result: Success
INSERT INTO my_table VALUES (1, to_date('02/03/2018','dd/mm/yyyy '),to_date('31/03/2018','dd/mm/yyyy '));

--3 bad data - Result: Success
INSERT INTO MY_TABLE VALUES (1, TO_DATE('01/01/2018','dd/mm/yyyy '),TO_DATE('01/04/2018','dd/mm/yyyy '));

--4 bad data - Result: Success
INSERT INTO my_table VALUES (1, to_date('15/01/2018','dd/mm/yyyy '),to_date('02/02/2018','dd/mm/yyyy '));

--5 bad data - Result: Success
INSERT INTO my_table VALUES (1, to_date('16/02/2018','dd/mm/yyyy '),to_date('15/03/2018','dd/mm/yyyy '));

--6 bad data - Result: Success
INSERT INTO my_table VALUES (1, to_date('15/02/2018','dd/mm/yyyy '),to_date('20/02/2018','dd/mm/yyyy '));

--7 good data - Result: Fail
UPDATE my_table 
SET enddate = TO_DATE('31/03/2018','dd/mm/yyyy') + 1 
WHERE startdate = TO_DATE('02/03/2018','dd/mm/yyyy');

For the 7th statement ie, UPDATE. I am facing mutaing table error. Please help me here.

Thanks in advance.

Answer 1

As @mic.sca's answer says, triggers are a poor/tricky way to implement rules like this. What you really want is a constraint that can work at table-level rather than row-level. ANSI SQL would call this an "assertion", but no DBMS vendor has yet implemented this to date (though it seems that Oracle is seriously considering doing so in a future release).

However, there is a way to simulate such a constraint/assertion using materialized views. I blogged about this way back in 2004 - your requirement is very like my example 2 there. Modified for your table this would be:

create materialized view my_table_mv1
refresh complete on commit as
select 1 dummy
from my_table t1, my_table t2
where t1.id = t2.id
and t1.startdate <= t2.enddate
and t1.enddate >= t2.startdate;

alter table my_table_mv1
add constraint my_table_mv1_chk
check (1=0) deferrable;

This materialized view only contains instances of overlaps, so should always be empty. As soon as an overlap is created, a row is inserted into the materialized view - but immediately violates its check constraint, which can never be satisfied!

Note that this is a deferred constraint, i.e. it will not be checked until commit time.

By the way, I don't know why I didn't use ANSI join syntax back in 2004 - maybe I just wasn't using it then. However, there are cases (I think more with outer joins) where materialized views can't be created using ANSI syntax but can be with the equivalent old-style syntax!

Answer 2

The mutating table error occurs because during the update in the trigger you are selecting the same row that you are updating.

My advice would be not to use a trigger and instead doing all the insert and update using stored procedures that check that the dates do not overlap before doing the operation.

To prevent concurent operation on the same id. you need as well to have a mechanism to serialize the possible concurrent sessions running the operations on the data. You might have a separate parent table with your ids and all the operations which operate on a specific Id should do a select for update on that id on the parent table before running insert or updates on my_table.

Trigger might look cool but can create maintenance headaches in the long run as they are not that explicit and they apply on all the operations on a table(http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html).

By the way if two users update concurrently two rows with the same id with your trigger you could end up with overlapping values without your trigger raising any error (though it is very unlikely).