使用触发器确保插入的数据不会超过限制 [英] Using trigger to make sure inserted data doesnt cross limit

问题描述

我想将表1中的值总和与表2中的极限值进行比较。我尝试使用约束或触发器，但没有任何帮助

I want to compare the sum of values in table one with the limit value in table 2 . I tried with constraint or triggers but did not work any help

由于下面的照片中有2个表
表1有2列保留且可用
这些列中的值来自用户，并且其取决不能超过第二个表的限制。.

As in the photo below there are 2 table table 1 has 2 columns reserve and available The in these columns come from users and the it must not excees more than the limit on second table ..

例如，用户可以输入所有金额之和列必须小于限制

As example user can enter any amount where sum of all the column must be less than the limit

从表一开始，我可以在预订列中输入值，该值可以介于0到表2中的限制之间，但是如果限制为到达则无法为该元素添加更多行

As of table one i can enter value in booked column that can be between 0 to the limit in table 2 but if the limit is reached then it cannot add more rows for that element

create table booked (room_id foreign key (room_id),
   booked datetime, booked_seats int, remaining_seats);

create table rooms ( room_id primary key
   room_size int);

预订座位是由用户完成的，为此我有单独的触发器以确保用户输入正确的
想要触发器或类似的东西，将检查第一个表中预订的所有座位的总和，并与第二个表中的room_size比较。
，如果总和小于它将编辑的大小，否则返回错误

booked seats are done by user, i have seperate trigger for that to ensure user enters corrected, in want a trigger or something similar that will check the sum of all seats booked in the firsts table and compare with the room_size in second table. if the sum is less that the size it will edit or else return error

create trigger test after insert on booked 
begin 
if sum of all (new.booked ) where the id is same > table2.limit then
    ....
end

推荐答案

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

For a quick and good answer you need more things than you provided a functioning example data for example

此触发器将阻止任何插入操作如果room_size小于总座位数。

This Trigger will block any attempts to insert if the room_size is smaller than the the sumed up seats.

请仔细阅读结尾处的内容，我解释说，您必须在其中放置一些内容

Please read the end there i explain, where you must put some work in

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               |

---

在数据库小提琴上查看

如示例所示，已插入1行，第二，给出一个例外。

As you can see in the example 1 row is inserted and the second, gives an exception.

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

you need to improve the part where i sum up the booked seats there i make a

已预订= NEW.booking

AND booked = NEW.booked

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

Because i don't know absolutely what time criteria will count to sum the complete seats number. The now timestamp makes no sense put to test the trigger i need some date.

这篇关于使用触发器确保插入的数据不会超过限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！