MySQL Forums
Forum List  »  Triggers

triggers if variables table lookuo help!
Posted by: Jamie Sutherland-West
Date: November 28, 2015 04:01PM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
triggers if variables table lookuo help!
1921
November 28, 2015 04:01PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.