此触发器有什么问题(要在''附近使用正确的语法) [英] What is the issue with this trigger (for the right syntax to use near '' )
本文介绍了此触发器有什么问题(要在''附近使用正确的语法)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当表中已经插入2列(id_prof,id_etud)但每次出现此错误
Please I did this trigger to update table rating
after each insert when 2 columns (id_prof,id_etud) inserted are already in the table but it gives mi this error
> #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 6
触发器:
CREATE TRIGGER Before_Insert_Rate
BEFORE INSERT ON rating
FOR EACH ROW
BEGIN
IF (EXISTS(SELECT * FROM rating WHERE id_prof=NEW.id_prof and id_etud = NEW.id_etud)) THEN
UPDATE `rating` SET `rate` = NEW.rate WHERE `id_prof` = NEW.id_prof and `id_etud` = NEW.id_etud;
ELSE INSERT INTO rating VALUES (NEW.idprof,New.rate,New.id_etud);
END IF
END
DELIMITER ;
推荐答案
我在查询而不是触发器上使用 ON DUPLICATE KEY
解决了它
I solved it using ON DUPLICATE KEY
on a query instead of trigger
CREATE TABLE `rating` (
`id_prof` int(11) NOT NULL,
`rate` float NOT NULL,
`id_etud` int(11) NOT NULL,
UNIQUE (id_prof,id_etud)
)
查询:
INSERT INTO rating (id_prof,rate,id_etud) VALUES (1,2,5)
ON DUPLICATE KEY UPDATE rate=2
这篇关于此触发器有什么问题(要在''附近使用正确的语法)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文