insert语句可以插入多行.例如:

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.

因此，您将编写一个after语句触发器，以查看表中的新情况.

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;

如果表很大，并且您只想查看插入/更新的行才能快速运行此触发器，则必须编写一个复合触发器，而不要记住行级数组中的预订ID，仅在语句级别查看这些行.

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.