CODEWITHSUNDEEP
mysqltriggers
kisonay
kisonay

Reputation: 361

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.

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.

Here is my query:

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.

Upvotes: 2

Views: 10867

Answers (1)

medina
medina

Reputation: 8159

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

and then

CREATE TRIGGER ....

Documentation here.

Upvotes: 4

Related Questions