Create Stored Procedure
Assuming that you already have a block of codes, especially it's a stored procedure (e.g. CREATE FUNCTION, CREATE PROCEDURE, CREATE TRIGGER) 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;
Delimiter Semicolon
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.
Delimiter Double Slashes
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. Here we use double slashes to be the delimiter of a block of code.
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, "$$".
Go Back to Default
To cancel the customized delimiter and back to the default, just do:
mysql> delimiter ;