如何修复此 MySQL 触发器语句

Question

找不到它的问题，现在已经尝试了一段时间

CREATE
    TRIGGER `checkin_enforcement` BEFORE INSERT
    ON `booking` 
    FOR EACH ROW BEGIN
        IF (NEW.checkinDate < NEW.checkoutDate) THEN
            INSERT INTO booking (bookingID, hotelNo, guestNo, checkinDate, checkoutDate, roomNo) VALUES (NEW.bookingID, NEW.hotelNo, NEW.guestNo, NEW.checkinDate, NEW.checkoutDate, NEW.roomNo);
        ELSE
            signal sqlstate '45000' set message_text = 'The checkin date must be less than the checkout date!';
        END IF;

    END$$

#1064 - 您的 SQL 语法有误;请查看与您的 MySQL 服务器版本对应的手册，了解在第 7 行的 '' 附近使用的正确语法

Answer 1

如果条件通过，则无需插入行。你只需要在它没有时抛出错误。

此外，我on insert更改为on insert or update，因为即使在将数据插入表中后，您可能也希望强制实施此规则。如果没有，只需将其更改为 on insert .

试试这个：

delimiter $$
create trigger checkin_enforcement 
before insert or update on booking
for each row 
begin
    if (new.checkindate >= new.checkoutdate) then
        signal sqlstate '45000' set message_text = 'the checkin date must be less than the checkout date!';
    end if;
end$$

Answer 2

CREATE TRIGGER `checkin_enforcement` 
BEFORE INSERT ON `booking` 
FOR EACH ROW
IF NEW.checkinDate >= NEW.checkoutDate THEN 
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The check-in date must be before the check-out date!'; 
END IF

之后不需要开始 对于每一行