SQL:触发以防止将无效数据插入到表中 [英] SQL: trigger to prevent invalid data from being inserted into a 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.

例如，假设当前在5号房间预订的时间为2019年1月1日至2019年1月7日.如果为5号房间添加了另一个预订，时间为2018年12月26日至2019年1月3日，我希望我的触发器阻止添加此数据到预订表.如果在2019年1月3日至2019年1月10日之间以及2019年1月2日至2019年1月6日之间进行预订，则同样适用.

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; 

我收到一条错误消息，提示表级触发器不允许使用NEW或OLD引用".我知道，如果我将其放入行级触发器，则可能会引发变异表错误.

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?

干杯！

推荐答案

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.

这篇关于SQL:触发以防止将无效数据插入到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！