Resolve Delimiter Issue While Creating Trigger Or Stored Procedure In MySQL phpMyAdmin

I was creating a trigger using Delimiter in MySQL phpMyAdmin and I faced a #1064 error  i.e.

#1064 - You have an error in your SQL syntax; check the  manual that corresponds to your MySQL server version for the right  syntax to use   near '' at line 6

I have searched at many places but I didn’t find any solution, so I decided to post the solution here after resolving that error. The problem was that I was applying Delimiter at the end of every statement after declaring it. Thus, in phpMyAdmin, whenever we define a Trigger or stored procedure, we have to follow these steps:

  1. Define delimiter that your want to be treated as actual delimiter by MySQL during execution. e.g : DELIMITER $$. Purpose for defining this delimiter is to instruct MySQL not to consider semicolons in our code as delimiters.
  2. In you code use semicolons as delimiter and use new defined delimiter when you want to end your trigger (or stored procedure code) i.e. END, e.g. END $$
  3. Again declare delimiter with semicolon so that MySQL can use semicolons as default delimiter again..

I have written both my wrong code and corrected code below , so that everyone can get idea about how the delimiter is actually implemented.

My wrong code was like this :

DELIMITER $$
CREATE TRIGGER WpafDbDefaultTableRenameTrigger
    BEFORE UPDATE
    ON wp_options
      FOR EACH ROW BEGIN
        IF NEW.option_name = "wpaf_db_table_name" AND NEW.option_value != OLD.option_value         THEN
          SET NEW.option_value = CONCAT("wp_",NEW.option_value)$$
        END IF$$
      END$$
DELIMITER ;

The correct code is:

DELIMITER $$
CREATE TRIGGER WpafDbDefaultTableRenameTrigger
    BEFORE UPDATE ON wp_options
    FOR EACH ROW BEGIN
        IF NEW.option_name = "wpaf_db_table_name" AND NEW.option_value != OLD.option_value
        THEN
          SET NEW.option_value = CONCAT("wp_",NEW.option_value);
        END IF;
    END$$

DELIMITER ;

Leave a Reply

Your email address will not be published.