Триггер для проверки того, не пересекают ли даты уже существующие

Question

Имеется таблица, которая записывает время начала и конца занятия (в спортивном клубе). Необходимо создать триггер для проверки того, что время занятий у разных групп в ОДНОМ ЗАЛЕ не пересекается. Как я понимаю, это триггер BEFORE INSERT и при добавлении новой записи проверяются все существующие записи, в которых номер зала равен добавляемому, и в них добавляемое время начала и конца занятия проверяются на пересечения с существующими записями. Но я не имею ни малейшего понятия, как написать такой триггер, нужна помощь.

Структура таблицы занятий:

CREATE TABLE lesson
( lesson_id number(5) NOT NULL,
lesson_section_id number(5) NOT NULL,
lesson_coach_id number(5) NOT NULL,
lesson_room number(5) NOT NULL,
lesson_start_date date NOT NULL,
lesson_end_date date NOT NULL,
lesson_cost number(5),
FOREIGN KEY (lesson_section_id) REFERENCES sections(section_id),
FOREIGN KEY (lesson_coach_id) REFERENCES coachs(coach_id),
CONSTRAINT lesson_pk PRIMARY KEY (lesson_id)
);

Нужно проверить чтобы в одинаковом зале (lesson_room) промежутки начала и конца занятий не пересекались (lesson_start_date и lesson_end_date в них хранится и дата и время начала и конца занятия соответственно)

Answer 1

Если решать проблему в лоб, то в триггере AFTER INSERT OR UPDATE (без FOR ECH ROW) необходимо подсчитать количество совпадений, если больше одного, то вывести ошибку.

create or replace trigger TRG_AIU_LESSON
  after insert or update on LESSON 
declare
  v_cnt integer;
begin
  select
    count(*) into v_cnt
  from lesson l1,
       lesson l2
  where l1.lesson_id <> l2.lesson_id
  and   l1.lesson_room = l2.lesson_room
  and   l1.lesson_start_date <= l2.lesson_end_date
  and   l2.lesson_start_date <= l1.lesson_end_date

 if v_cnt > 0 then
   raise_application_error(-20001, 'Пересечение');
 end if;
end TRG_AIU_LESSON;

Проблема в том, что при росте числа записей это будет замедлять внесение изменений.

Решение можно улучшить, используя COMPOUND TRIGGER.

Answer 2

Триггер здесь не нужен. Проверять условие надо до вставки, а не во время её. Попробуйте:

create or replace procedure createLesson (room number, s date, e date) is
begin 
    for r in (
        select *
        from lesson
        where lesson_room = room
        and lesson_start_date < e and lesson_end_date > s) loop  
        raise_application_error (-20000, 'The room '||r.lesson_room||' already booked for '||
            r.lesson_id||'/'||r.lesson_start_date||' till '||r.lesson_end_date);
    end loop;
    insert into lesson values (null, room, s, e);
    dbms_output.put_line (sql%rowcount||' row inserted.');
end;
/

Сама вставка:

exec createLesson (1, date'2020-05-07'+10/24, date'2020-05-07'+12/24)

1 row inserted.

exec createLesson (1, date'2020-05-07'+11/24, date'2020-05-07'+13/24)

ORA-20000: The room 1 already booked for 1/2020-05-07 10:00:00 till 2020-05-07 12:00:00

БЛОГ НА HUSL

Триггер для проверки того, не пересекают ли даты уже существующие

Ответы (2 шт):