Re: after insert check value from query and add record to table if necessary

[Bob Field]

Statements in MySQL need semicolons after them. The syntax of the IF statement requires a THEN keyword on the end:

DELIMITER $$
CREATE TRIGGER ai_hires AFTER INSERT ON hires for each row 
BEGIN
  declare citizen varchar(5);

  SELECT US_Citizen INTO citizen 
  FROM applicants 
  JOIN applications ON applicants.ID=applications.appID 
  JOIN hires ON applications.ID=hires.appl_ID AND hires.ID=NEW.ID;

  IF citizen = 'yes' THEN
    INSERT INTO FN (hire_ID) values (NEW.ID);
  END IF;
END$$
DELIMITER ;