SQL: триггер для предотвращения вставки неверных данных в таблицу

У меня есть следующая таблица:

CREATE TABLE booking(
booking_id NUMBER(8) NOT NULL;
booking_start DATE NOT NULL;
booking_end DATE NOT NULL;
booking_room NUMBER(3) NOT NULL;
guest_no NUMBER(5) NOT NULL;
);

В этой таблице отслеживаются все бронирования номеров в конкретном отеле. Я хочу написать триггер, чтобы предотвратить добавление бронирований, которые перекрываются с датами предыдущих бронирований для определенных номеров в отеле.

Например, предположим, что номер 5 в настоящее время забронирован с 1 января 2019 года по 7 января 2019 года. Если с 26 декабря 2018 года по 3 января 2019 года для номера 5 добавлено другое бронирование, я хочу, чтобы мой триггер предотвратил добавление этих данных в бронирования стол. То же самое применимо, если бронирование сделано с 3 января 2019 года по 10 января 2019 года, а также со 2 января 2019 года по 6 января 2019 года.

Обычно даты начала и окончания бронирования не могут совпадать с другими датами начала и окончания бронирования для одного и того же гостиничного номера.

Это то, что я пробовал до сих пор:

CREATE OR REPLACE TRIGGER check_booking_valid
BEFORE INSERT ON booking
BEGIN
    SELECT booking_start
    FROM booking
    WHERE booking_room = :new.booking_room;

    SELECT booking_end
    FROM booking
    WHERE booking_room = :new.booking_room;

    IF :new.booking_start > booking_start AND 
    :new.booking_start < booking_end
    THEN raise_application_error(-20000, 'Invalid booking');

    IF :new.booking_end > booking_start AND 
    :new.booking_end < booking_end
    THEN raise_application_error(-20000, 'Invalid booking');

    IF :new.booking_start > booking_start AND 
    :new.booking_start < booking_end AND
    :new.booking_end > booking_start AND 
    :new.booking_end < booking_end
    THEN raise_application_error(-20000, 'Invalid booking');

    END IF;
END;

Я получаю сообщение об ошибке «НОВЫЕ или СТАРЫЕ ссылки не разрешены в триггерах уровня таблицы». Я знаю, что если я сделаю это триггером на уровне строки, может возникнуть ошибка изменяющейся таблицы.

Может ли кто-нибудь указать, в чем ошибка?

Ваше здоровье!


sql oracle database-trigger mutating-table
person Big Rick    schedule 23.10.2019    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Оператор вставки может вставлять несколько строк. Например.:

insert into booking(booking_start, booking_end, booking_room, guest_no)
select date '2019-11-01', date '2019-11-10', 4, 10 from dual
union all
select date '2019-11-08', date '2019-11-15', 4, 88 from dual;

Эти вставки происходят в произвольном порядке, поэтому вы не можете принять одну строку, а не другую. Вместо этого вы должны отклонить весь оператор вставки. То же верно и для обновлений, если они могут быть сделаны.

Соответственно, вы должны написать триггер после оператора, в котором вы смотрите на новую ситуацию в таблице.

CREATE OR REPLACE TRIGGER trg_reject_invalid_bookings
AFTER INSERT OR UPDATE ON booking
DECLARE
  v_count INTEGER;
BEGIN
  SELECT count(*)
  INTO v_count
  FROM booking b1
  WHERE EXISTS
  (
    SELECT *
    FROM booking b2
    WHERE b2.booking_id <> b1.booking_id
    AND b2.booking_room = b1.booking_room
    AND b2.booking_start < b1.booking_end
    AND b2.booking_end > b1.booking_start
  )
  AND rownum = 1; -- it suffices to find one overlapping pair

  IF v_count > 0 THEN
    raise_application_error(-20000, 'Invalid booking');
  END IF;
END trg_reject_invalid_bookings;

Если таблица большая и вы хотите просматривать вставленные / обновленные строки только для того, чтобы этот триггер работал быстро, вам придется вместо этого написать составной триггер, в котором вы запоминаете идентификаторы бронирования в массиве на уровне строки и смотрите только на эти строки на уровне оператора.

person Thorsten Kettner    schedule 23.10.2019

arrow_upward
0
arrow_downward

По умолчанию триггер создается для statement level, где :new и :old не разрешены. Вам просто нужно объявить свой триггер как for each row

CREATE OR REPLACE TRIGGER check_booking_valid
BEFORE INSERT ON booking
For each row -- this
BEGIN
.....
.....

Ваше здоровье!!

person Popeye    schedule 23.10.2019
comment
Таблица BOOKING модифицируется и из нее также выполняется чтение, поэтому она находится в переходном состоянии - приведет ли это к возникновению ошибки изменяющейся таблицы? - person Big Rick; 23.10.2019
comment
@ Большой Рик: да, будет. - person Thorsten Kettner; 23.10.2019