ORACLE Stored procedure to avoid records of dates included in a range of dates

0

My Training table is this:

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

The problem that arises is when I have a user (any user) with a date of

  

01-01-18 in "start_training" and 05-01-18 in "end_training"

Now if I want to register dates with the same user, but this time with

  

03-01-18 as "start_training" and 06-01-18 in "end_training"

These dates are different from the dates of the first record, but 03-01-18 is included in the range of dates of the first record, it is feasible to create a trigger to avoid inserting me or complicating me more than What do I think? In advance, thank you.

PROCEDURE:

create or replace procedure "training_pc"(
pc_id_training training.id_training%type,
pc_id_user training.id_user%type,
pc_start_training training.start_training%type,
pc_end_training training.end_training%type
)
is 
begin
 if pc_id_user = training.id_user then
  if pc_start_training between training.start_training and training.end_training then
   if pc_end_training between training.start_training and training.end_training then
      RAISE_APPLICATION_ERROR(-20099,'NO PUEDE REGISTRAR OTRA CHARLA SI AUN MANTIENE CHARLA VIGENTE.');
        END IF;
    END IF;
else
 insert into training(id_training,id_user,start_training,end_training)
 values(pc_id_training,pc_id_user,pc_start_training,pc_end_training);
end if;
end;
    
asked by Stalinn 02.08.2018 в 16:37
source

0 answers