首页 新闻 会员 周边

mysql触发器

0
悬赏园豆:50 [已解决问题] 解决于 2022-06-06 19:27

DELIMITER //

CREATE TRIGGER borrow_insert_2
AFTER INSERT ON borrow
FOR EACH ROW
BEGIN
DECLARE num INT;
SET num=SELECT num FROM book WHERE bookId =NEW.bookId;
IF num=0 THEN
ROLLBACK ;
ELSE
UPDATE book SET num=num-1 WHERE bookId=NEW.bookId ;
END IF ;
END //

DELIMITER ;

为什么一直报错
1 queries executed, 0 success, 1 errors, 0 warnings

查询:set num=SELECT num FROM book WHERE bookId =NEW.bookId; IF num=0 THEN ROLLBACK ; ELSE UPDATE book SET num=num-1 WHERE bookId=NEW....

错误代码: 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 'SELECT num FROM book WHERE bookId =NEW.bookId;
IF num=0 THEN
ROLLBACK ;
EL' at line 1

执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec

问题补充:

用的sqlyog社区版

zqhwmy的主页 zqhwmy | 初学一级 | 园豆:29
提问于:2022-05-26 15:36
< >
分享
最佳答案
0

Mysql的触发器不支持rollback,如果你的borrow表有唯一性标识的话,可以把rollback改成

delete from borrow where id = new.id
收获园豆:50
foreversag | 菜鸟二级 |园豆:267 | 2022-06-01 09:14

DELIMITER //

CREATE TRIGGER borrow_insert_2
AFTER INSERT ON borrow
FOR EACH ROW
BEGIN
DECLARE @num INT ;
SET @num=(SELECT num FROM book WHERE bookId =NEW.bookId);
IF @num<=0 THEN
DELETE FROM borrow WHERE bookId=NEW.bookId ;
ELSE
UPDATE book SET num=num-1 WHERE bookId=NEW.bookId ;
END IF ;
END //

DELIMITER ;
还是报错了
1 queries executed, 0 success, 1 errors, 0 warnings

查询:SET @num=(SELECT num FROM book WHERE bookId =NEW.bookId); IF @num<=0 THEN delete from borrow where bookId=NEW.bookId ; ELSE UPDA...

错误代码: 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 @num<=0 THEN
delete from borrow where bookId=NEW.bookId ;
ELSE
UPDATE ' at line 2

执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
这个是borrow表
CREATE TABLE borrow(
studentId CHAR(7) NOT NULL,
bookId INT PRIMARY KEY,
borrowDate DATE,
state VARCHAR(3)CHECK (state IN('已还','未还')),
FOREIGN KEY (studentId) REFERENCES students(studentId),
FOREIGN KEY (bookId)REFERENCES book(bookId)
)

zqhwmy | 园豆:29 (初学一级) | 2022-06-05 13:37

@zqhwmy:

声明的时候,变量前面别加@

还有你这里用bookId定位删除不对吧,把之前的记录都删了

DELIMITER //

CREATE TRIGGER borrow_insert_2
AFTER INSERT ON borrow
FOR EACH ROW
BEGIN
DECLARE num INT ;
SET @num=(SELECT num FROM book WHERE bookId =NEW.bookId);
IF @num<=0 THEN
DELETE FROM borrow WHERE bookId=NEW.bookId ;
ELSE
UPDATE book SET num=num-1 WHERE bookId=NEW.bookId ;
END IF ;
END //

DELIMITER ;
foreversag | 园豆:267 (菜鸟二级) | 2022-06-06 08:51

@foreversag: 可以了,非常感谢

zqhwmy | 园豆:29 (初学一级) | 2022-06-06 19:26
其他回答(1)
0

SET num= (SELECT num FROM book WHERE bookId =NEW.bookId);

talentzemin | 园豆:763 (小虾三级) | 2022-05-26 16:49

DELIMITER //

CREATE TRIGGER borrow_insert_2
AFTER INSERT ON borrow
FOR EACH ROW
BEGIN
DECLARE @num INT;
SET @num=(SELECT num FROM book WHERE bookId =NEW.bookId);
IF @num=0 THEN
ROLLBACK ;
ELSE
UPDATE book SET num=num-1 WHERE bookId=NEW.bookId ;
END IF ;
END //

DELIMITER ;
改了()和@
但是还是报错
1 queries executed, 0 success, 1 errors, 0 warnings

查询:SET @num=(SELECT num FROM book WHERE bookId =NEW.bookId); IF @num=0 THEN ROLLBACK ; ELSE UPDATE book SET num=num-1 WHERE bookId=...

错误代码: 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 @num=0 THEN
ROLLBACK ;
ELSE
UPDATE book SET num=num-1 WHERE bookId=NEW' at line 2

执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec

支持(0) 反对(0) zqhwmy | 园豆:29 (初学一级) | 2022-05-26 17:17
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册