triggers if variables table lookuo help!
Hi there,
I am trying to get to grips with mysql triggers, if statements and variables.
I want to insert data into another table if the users domain matches. here is my code
DELIMITER $$
DROP TRIGGER IF EXISTS insert_certificate_on_testdb $$
CREATE TRIGGER insert_certificate_on_testdb
AFTER INSERT ON c1moodlelms.mdl_certificate_issues
FOR EACH ROW
BEGIN
-- Want to insert the employee email domain into the variable userdomain
insert SUBSTR(email, INSTR(email, '@') + 1) INTO userdomain FROM vUICert where userid = new.userid and code = new.code;
-- Check the variable to see if it matches
if userdomain = "myemaildomain.co.uk" then
--this insert statement work
insert into testingDB.emp_cert(empid, firstname, lastname, courseid, coursename, certificateid, emailaddress,emaildomain)
select new.userid, firstname, lastname, courseid,
coursename, new.code, email, SUBSTR(email, INSTR(email, '@') + 1) FROM vUICert where userid = new.userid and code = new.code;
--End the If Statement
end if
END $$
DELIMITER ;