Skip to content

How to Make MySQL Take a Block of Codes As a Whole

  • by
Assuming that you already have a block of codes, especially it's a stored procedure to be created like this:
1 CREATE TRIGGER test_trigger BEFORE INSERT ON table_name
2 FOR EACH ROW
3 begin
4     set new.total = new.salary + new.bonus;
5 end;

You might get the error in the console when you are trying to create the trigger:
mysql> CREATE TRIGGER test_trigger BEFORE INSERT ON table_name
    -> FOR EACH ROW
    -> begin
    ->     set new.total = new.salary + new.bonus;
ERROR 1064 (42000): 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 4
mysql> end;

You can see MySQL stopped when it saw the first semi-colon, and reported a syntax error. It's a normal behavior for MySQL itself without question, because a semi-colon is usually the termination character of a statement in SQL world by default.

To solve the error, you must explicitly set a delimiter other than a semi-colon to guide MySQL to take all the codes as a whole until the termination delimiter shows.
1 delimiter //
2 CREATE TRIGGER test_trigger BEFORE INSERT ON table_name
3 FOR EACH ROW
4 begin
5     set new.total = new.salary + new.bonus;
6 end//

You can see I declare "//" as the termination delimiter in Line 1, which will be the termination character throughout my session life time, so MySQL will wait and wait until the delimiter shows in Line 6. Let's try to execute it again.
mysql> delimiter //
mysql> CREATE TRIGGER test_trigger BEFORE INSERT ON table_name
    -> FOR EACH ROW
    -> begin
    ->     set new.total = new.salary + new.bonus;
    -> end//
Query OK, 0 rows affected (0.02 sec)

mysql>

It's pretty flexible for MySQL developers to set the termination delimiter to any other symbols as they like, for instance, "$$".

To cancel the customized delimiter and back to the default, just do:
mysql> delimiter ;
Tags:

Leave a Reply

Your email address will not be published.