mysql - 尝试编写 MySQL 触发器以替换 phpmyadmin 上的 CHECK 约束时出现语法错误

Question

我正在尝试创建一个 MySQL 触发器作为 CHECK 约束，因为我正在使用的 MySQL 版本不支持它。

基本上我正在为图书馆做一个数据库，其中表 bookloan 是所有曾经借出的贷款的记录。

bookloan 有 4 列:
- borrowerNo: 借款人编号
- copyNo: 租借图书的编号
- dateOut:图书的租借日期
- dateDue:图书到期的日期，总是在 dateOut 之后 14 天

我正在尝试限制借书人不能同时租用超过 3 本书。这是我的触发器代码:

CREATE TRIGGER maxbooks 
BEFORE INSERT OR UPDATE
ON bookloan FOR EACH ROW
BEGIN
IF COUNT(SELECT * FROM bookloan WHERE borrowerNo = NEW.borrowerNo AND dateOut <= NEW.dateOut AND dateDue > NEW.dateOut) >= 3 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Maximum number of book rentals has been reached';
END IF;
END;

这是我从 MySQL 得到的错误信息:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF COUNT(SELECT * FROM bookloan WHERE borrowerNo = NEW.borrowerNo AND dateOut <=' at line 4

我猜错误来自 Count() 函数，但不知道如何格式化它以便获得我想要的信息。

谢谢!

Answer 1

尝试使用 count(*) 而不是在返回的查询结果上使用它。就像下面这样:

CREATE TRIGGER maxbooks 
BEFORE INSERT OR UPDATE
ON bookloan FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM bookloan WHERE borrowerNo = NEW.borrowerNo AND dateOut <= 
NEW.dateOut AND dateDue > NEW.dateOut) >= 3 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Maximum number of book rentals has been reached';
END IF;
END;