mysql - 使用触发器确保插入的数据不会超过限制

为了获得快速而良好的答案，您需要提供的功能比提供示例功能的示例数据还要多。

如果room_size小于合计席位，则此触发器将阻止任何插入尝试。

请阅读我解释的结尾，您必须在其中进行一些工作

DELIMITER $$
CREATE TRIGGER check_roomsize_Before_Insert BEFORE insert on booked 
FOR EACH ROW
begin 
    if (SELECT SUM(booked_seats) + NEW.booked_seats FROM booked  WHERE room_id  = NEW.room_id  AND booked  = NEW.booked  GROUP BY room_id) 
        > (select room_size from rooms where rooms.room_id= new.room_id) then
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Roomsize too smal!';
    end if;
END$$
DELIMITER ;

模式（MySQL v5.7）

create table rooms ( room_id int primary key,
   room_size int);

 create table booked (room_id int,
   booked datetime, booked_seats int, remaining_seats int,    CONSTRAINT fk_category
    FOREIGN KEY (room_id) 
        REFERENCES rooms(room_id));

INSERT INTO rooms VALUES ( 1,5);

DELIMITER $$
CREATE TRIGGER check_roomsize_Before_Insert BEFORE insert on booked 
FOR EACH ROW
begin 
    if (SELECT SUM(booked_seats) + NEW.booked_seats FROM booked  WHERE room_id  = NEW.room_id  AND booked  = NEW.booked  GROUP BY room_id) 
        > (select room_size from rooms where rooms.room_id= new.room_id) then
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Roomsize too smal!';
    end if;
END$$
DELIMITER ;

INSERT INTO booked VALUES (1,now(),3,2);
#INSERT INTO booked VALUES (1,now(),3,0);

查询＃1

SELECT * FROM booked;

| room_id | booked              | booked_seats | remaining_seats |
| ------- | ------------------- | ------------ | --------------- |
| 1       | 2020-04-19 20:04:07 | 3            | 2               |

在数据库小提琴上查看

如您在示例中看到的，插入了第一行，第二行给出了一个例外。

您需要改进我总结的预定座位的部分，

AND已预订= NEW.booked

因为我绝对不知道什么时间标准才能算出完整的座位数。现在的时间戳没有任何意义来测试我需要一些日期的触发器。