SQL: trigger to prevent invalid data from being inserted into a table

Question

I have the following table:

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;
);

This table keeps track of all bookings for rooms in a particular hotel. I want to write a trigger to prevent bookings being added that overlap with the dates of previous bookings for particular rooms in the hotel.

For example, let's say that room 5 is currently booked from 01 Jan 2019 to 07 Jan 2019. If another booking is added for room 5 from 26 Dec 2018 to 03 Jan 2019 I want my trigger to prevent this data from being added to the bookings table. The same thing applies if a booking is made from 03 Jan 2019 to 10 Jan 2019, and also 02 Jan 2019 to 06 Jan 2019.

Basically booking start and end dates cannot overlap with other booking start and end dates for the same hotel room.

This is what I have tried so far:

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; 

I am getting an error message saying "NEW or OLD references not allowed in table level triggers". I know that if I make this into a row-level trigger there may be a mutating-table error that is thrown.

Could anyone please point out what the error is?

Cheers!

Answer 1

An insert statement can insert multiple rows. E.g.:

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;

These inserts occur in arbitrary order, so you cannot really accept one row and not the other. Instead you must reject the whole insert statement. The same is true for updates of course, if such can be made.

Accordingly you'd write an after statement trigger where you look at the new situation in the table.

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;

If the table is big and you want to look at inserted/updated rows only in order to have this trigger run fast, you'd have to write a compound trigger instead where you remember booking IDs in an array on row level and only look at these rows at statement level.

Answer 2

By default trigger is created for statement level where :new and :old are not allowed, You just need to declare your trigger as for each row

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

Cheers!!