How to create a trigger in oracle to prevent overlapping date ranges

dateoracleoracle-11gtrigger

I'm learning Oracle and I had a problem.

Chat table:

CREATE TABLE training
(
   id_training      NUMBER,
   id_user          NUMBER,
   start_training   DATE,
   end_training     DATE
);

I want to insert the same id_user with another start_training and end_training dates. I have to create a trigger just to ensure that the dates will not be located between any previously registered start_training and end_training dates.

I was thinking of using :old and :new in the trigger, but if I use an insert in my trigger the data of :old always will be null.

How can I overcome this problem of changing :old values, if I'm inserting new values inside my trigger?

Best Answer

Here's a sketch. I get an error from the trigger on db<>fiddle, but you shpould be able to use the idea. First I'll add some constraints on the table, this will simplify things a lot:

CREATE TABLE training
( id_user          NUMBER NOT NULL
, start_training   DATE NOT NULL
, end_training     DATE
,     constraint ak1_training unique (id_user, start_training)
,     constraint c1_training check (start_training <= end_training)
);

I assume id_training is some sort of auto increment number so I left that out since it is not relevant for the idea. Comments:

  • All attributes except end_training is not null
  • is_user and start_training is unique
  • start_training is before or equal to end_training. The constraint is satisfied if end_training is null.

.

CREATE OR REPLACE TRIGGER training_validation
BEFORE INSERT ON training
FOR EACH ROW
BEGIN
    IF EXISTS (
           select 1 from training t
                 where t.id_user = new.user
             and (t.start_training between new.start_training and coalesce(new.end_training, current_date))
                or
                  coalesce(t.start_end, current_date) between new.start_training and coalesce(new.end_training, current_date))
     ) 

  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Some msg ...' );
  END IF;
END training_validation;

I get the error:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; The symbol ";" was substituted for "end-of-file" to continue.

FWIW, I get the same error with the trivial examples I found googling around

Anyhow, the idea is to throw an error if there exists a previous start_training between the new.start_training and new.end_training. Since the new.end_training is typically null, we map that to current_date via coalesce. In the same manner, we check for the previous start_training.

Note that if start_training is not null, we won't have to take possible nulls in consideration. Also since we know that:

start_training <= end_training

we can rely on that as well.

Related Question