MySQL BEFORE INSERT触发器：预订重复计数与设施容量校验

我来帮你解决这个预订容量限制的触发器问题！首先得明确核心需求：每次往bookings表插入新预订前，必须检查同一设施、同一日期、同一时段的已预订数量，加上这次要插的记录后，如果总数≥对应设施的容量，就抛出'Facility Full'的错误。

先说说常见的坑（很多人写触发器时容易踩这些）：

• 忘了关联facility表拿容量值，硬写死数值或者压根没取到正确的容量
• 计数时没算上当前要插的这条记录（毕竟是BEFORE INSERT，新记录还没进表呢）
• 没考虑批量插入的情况，只处理了单条记录

下面直接上正确的代码，分MySQL和PostgreSQL两种主流数据库来写，你按需取用：

MySQL版本触发器代码 #

DELIMITER //

CREATE TRIGGER check_facility_capacity BEFORE INSERT ON bookings
FOR EACH ROW
BEGIN
    DECLARE current_bookings INT;
    DECLARE max_capacity INT;

    -- 先从facility表拿到对应设施的容量
    SELECT facility_capacity INTO max_capacity
    FROM facility
    WHERE facility_id = NEW.facility_id;

    -- 统计当前已有的同设施、同日期、同时段的预订数
    SELECT COUNT(*) INTO current_bookings
    FROM bookings
    WHERE facility_id = NEW.facility_id
      AND booking_date = NEW.booking_date
      AND time_slot = NEW.time_slot;

    -- 插入后的总数是现有数+1，超过容量就报错
    IF (current_bookings + 1) >= max_capacity THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Facility Full';
    END IF;
END //

DELIMITER ;

代码细节解释 #

• DELIMITER //：因为触发器体里有分号，得先把MySQL的语句结束符临时改成//，不然它会提前把触发器体拆成多个语句，直接报错。
• FOR EACH ROW：不管你是插一条还是批量插N条，每条记录都会单独做校验，完美适配批量插入场景。
• NEW.xxx：代表即将插入的那条新记录的字段值，比如NEW.facility_id就是新预订的设施ID。
• SIGNAL SQLSTATE '45000'：这是MySQL里抛自定义错误的标准写法，45000是MySQL预留的用户自定义错误码，后面的MESSAGE_TEXT就是要抛的错误提示。

PostgreSQL版本触发器代码 #

PostgreSQL的触发器写法是先写函数，再绑定触发器，语法略有不同：

CREATE OR REPLACE FUNCTION check_facility_capacity()
RETURNS TRIGGER AS $$
DECLARE
    current_bookings INT;
    max_capacity INT;
BEGIN
    -- 获取对应设施的容量
    SELECT facility_capacity INTO max_capacity
    FROM facility
    WHERE facility_id = NEW.facility_id;

    -- 统计现有同条件的预订数
    SELECT COUNT(*) INTO current_bookings
    FROM bookings
    WHERE facility_id = NEW.facility_id
      AND booking_date = NEW.booking_date
      AND time_slot = NEW.time_slot;

    -- 校验容量，超了就抛错
    IF (current_bookings + 1) >= max_capacity THEN
        RAISE EXCEPTION 'Facility Full';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_facility_capacity_trigger
BEFORE INSERT ON bookings
FOR EACH ROW EXECUTE FUNCTION check_facility_capacity();

优化建议 #

为了让触发器跑起来更快，别每次插入都全表扫描bookings表，建议给bookings表加个联合索引：

-- MySQL和PostgreSQL通用的索引创建语句
CREATE INDEX idx_booking_facility_date_slot ON bookings(facility_id, booking_date, time_slot);

另外，如果booking_date或者time_slot字段可能为空，记得调整WHERE条件，比如用IS NOT DISTINCT FROM（PostgreSQL支持）或者单独处理空值匹配，避免因为空值导致统计不准。

内容的提问来源于stack exchange，提问作者Gary Mannion