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
INSERT INTO technicians (tech_id, techName, techEmail) VALUES(OLD.emp_id, OLD.Name, OLD.Email);
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.
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;
Note - I am using SQL Manager 2005 from EMS DB Solutions -