Tell me where is the error?

CREATE TRIGGER `updateBonusTable` AFTER UPDATE ON `character_subclasses` FOR EACH ROW BEGIN IF(NEW.level = 85) AND (OLD.level != 85) THEN SET @charName = (select `char_name` from `characters` WHERE `obj_Id` = NEW.char_obj_id LIMIT 1); INSERT into ttgBonus.bonus_85_lvl (`charName`) VALUES(@charName); END IF; END;

Writes an Error SQL Error (1064): You have an error in your SQL syntax; check the syntax for your right syntax to use at line 4

  • 2
    Most likely you did not redefine the end of the query; for example, in the console client mysql this is done using the DELIMITER command. For example, DELIMITER //, then after the END you should not specify a semicolon, but //, internal semicolons will not conflict with a sign of the end of the query. - cheops
  • @cheops thanks, answer the question I recognize it as correct - Winteriscoming
  • Strange trigger text. It requires an assumption that the subquery will return 1 record char_name , or that the char_name the same in all records. And in principle, everything is easily minimized into one request, so DELIMITER will not have to redefine it. - Akina

1 answer 1

Typically, such an error occurs in the event that the end of the query attribute is not redefined - a semicolon. In this case, it conflicts with a comma in the body of the trigger.

If the query is executed in the console mysql utility, it is best to override the end of the query using the DELIMITER command (graphical clients, as a rule, also provide this feature). This allows you to circumvent the conflict.

 DELIMITER // CREATE TRIGGER `updateBonusTable` AFTER UPDATE ON `character_subclasses` FOR EACH ROW BEGIN IF(NEW.level = 85) AND (OLD.level != 85) THEN SET @charName = (select `char_name` from `characters` WHERE `obj_Id` = NEW.char_obj_id LIMIT 1); INSERT into ttgBonus.bonus_85_lvl (`charName`) VALUES(@charName); END IF; END//

After creating the trigger, you can restore the old sign of the end of the query using the command

 DELIMITER ;