troubleshooting Question

MYSQL trigger Help - syntax for updating date in another table

Avatar of TumacLumber
TumacLumber asked on
MySQL Server
8 Comments1 Solution879 ViewsLast Modified:
I have a table of employees that have a yes/no flag for whether they are technicians or not.

If i update the table and set the technician flag to YES, I have created a trigger that successfully inserts some of their data into the TECHNICIAN table

CREATE TRIGGER `employees_after_upd_tr` AFTER UPDATE ON `employees`
  FOR EACH ROW
BEGIN
INSERT INTO technicians (tech_id, techName, techEmail) VALUES(OLD.emp_id, OLD.Name, OLD.Email);
END;


If I want to set the flag back to NO, I want it to DELETE from the TECHNICIAN table, but this modified statement won't compile.
BEGIN
     IF employees.technician="YES" THEN
          INSERT INTO technicians (tech_id, techName, techEmail) VALUES (OLD.emp_id, OLD.Name, OLD.Email);
     ELSE IF employees.technician="NO" THEN
          DELETE FROM technicians WHERE tech_id=OLD.emp_id;
     END IF;
END

Note - I am using SQL Manager 2005 from EMS DB Solutions -
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros