一个表mysql错误的多个具有相同操作时间和事件的触发器 [英] multiple triggers with the same action time and event for one table mysql error

问题描述

我是触发器的新手，并且收到针对一个表的具有相同动作时间和事件的多个触发器"错误.

I'm new to triggers and am getting "multiple triggers with the same action time and event for one table" error.

我创建了AFTER Update和AFTER Delete，它们是两个单独的动作时间/事件，因此我不确定为什么会出错.

I have created an AFTER Update and an AFTER Delete which are two separate action time/events so I am not really sure why I would be getting the error.

这是我的查询:

CREATE TRIGGER `new_enrolment` AFTER INSERT ON `mdl_user_enrolments` FOR EACH ROW BEGIN
INSERT INTO c_master (
ud,
firstname,
lastname,
email,
username,
cid,
course
)
SELECT 
mdl_user.id AS uid, 
mdl_user.firstname, 
mdl_user.lastname, 
mdl_user.email, 
mdl_user.suspended, 
mdl_user.username, 
mdl_enrol.courseid AS cid,
mdl_course.fullname AS course
FROM mdl_user_enrolments INNER JOIN mdl_enrol ON mdl_user_enrolments.enrolid = mdl_enrol.id
 INNER JOIN mdl_course ON mdl_enrol.courseid = mdl_course.id
 INNER JOIN mdl_user ON mdl_user.id = mdl_user_enrolments.userid
WHERE userid = NEW.userid;
END;

CREATE TRIGGER `remove_enrolment` AFTER DELETE ON `mdl_user_enrolments` FOR EACH ROW BEGIN
SELECT mdl_enrol.courseid, 
mdl_user_enrolments.userid, 
mdl_user_enrolments.enrolid
FROM mdl_user_enrolments INNER JOIN mdl_enrol ON mdl_user_enrolments.enrolid = mdl_enrol.id
WHERE mdl_user_enrolments.enrolid = OLD.enrolid

DELETE FROM c_master
WHERE uid = OLD.userid AND mdl_enrol.courseid;
END;

由于我是新手，所以我可能缺少一些简单的东西.

Since I am new to all of this I am probably missing something simple.

推荐答案

布莱恩(Brian)，可能您已经在表中多次添加和删除了相同的触发器，并且您忘记了最后一次删除它，然后再次创建它.确保使用

Brian, probably you've been adding and removing the same trigger a few times in your table and you have forgotten to remove it last time before create it again. Get sure you've removed it before create again using

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

然后

CREATE TRIGGER ....

文档此处.

这篇关于一个表mysql错误的多个具有相同操作时间和事件的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！