• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 851
  • Last Modified:

MYSQL trigger Help - syntax for updating date in another table

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 -
0
TumacLumber
Asked:
TumacLumber
  • 5
  • 3
1 Solution
 
todd_farmerCommented:
Probably:

BEGIN
     IF NEW.technician="YES" THEN
          INSERT INTO technicians (tech_id, techName, techEmail) VALUES (OLD.emp_id, OLD.Name, OLD.Email);
     ELSE IF NEW.technician="NO" THEN
          DELETE FROM technicians WHERE tech_id=OLD.emp_id;
     END IF;
END
0
 
TumacLumberAuthor Commented:
Sorry - it is throwing a syntax error on the compile.  Saying to "check the manual for syntax to use near " at line 9.

I believe this is the line at the END IF;

I t
0
 
TumacLumberAuthor Commented:
This is what is currently compiled and working.  
------------------------
CREATE TRIGGER `employees_after_upd_tr` AFTER UPDATE ON `employees`
  FOR EACH ROW
BEGIN
     IF employees.technician='YES' THEN
          INSERT INTO technicians (tech_id, techName, techEmail) VALUES (OLD.emp_id, OLD.Name, OLD.Email);

END IF;
END;
------------------------

Is there something wrong with the ELSE IF?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
TumacLumberAuthor Commented:
Sorry that is not working - this works
-----------------------------------------
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;
-------------------------------------------

The above throws a SQL error - will try changing to NEW.technician="yes"
0
 
todd_farmerCommented:
Try without the space between ELSE and IF:

BEGIN
     IF NEW.technician="YES" THEN
          INSERT INTO technicians (tech_id, techName, techEmail) VALUES (OLD.emp_id, OLD.Name, OLD.Email);
     ELSEIF NEW.technician="NO" THEN
          DELETE FROM technicians WHERE tech_id=OLD.emp_id;
     END IF;
END
0
 
TumacLumberAuthor Commented:
Bingo on the compile - I though I tried that once or twice.....

Bingo on the function.......

THANKS!
0
 
TumacLumberAuthor Commented:
BAH - I looked at the MYSQL example 10 times and still saw the space between ELSE and IF - just looked at it again - no space........  Gremlins must be messin with my head.
0
 
todd_farmerCommented:
:)  Thanks for the points!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now