Delimiter in MySQL Triggers

DELIMITER in MySQL Triggers

In MySQL, a DELIMITER command changes the delimiter from its default value of semicolon (;) to another string like //. This is really useful when creating stored procedures or triggers that contain multiple SQL statements, which may also include semicolons.

Why do we Change the Delimiter in MySQL

When you declare a trigger, you have to write a few SQL statements that may also involve control flow statements like IF conditions, which include semicolons. Unless you change the delimiter, MySQL will consider the first semicolon to be the end of the declaration of the trigger, thus causing syntax errors. The change in the delimiter will allow you to enclose the entire logic of the trigger until the new delimiter is encountered.

What Happens if you don't Use the Delimiter

In case if you do not specify the DELIMITER while creating the trigger, MySQL might interpret the body incorrectly, especially if it contains many SQL statements. If the default delimiter (;) is used to terminate SQL statements, MySQL might end up misinterpreting the end of the creation process for the trigger.

Let us create a trigger without using delimiter

CREATE TRIGGER CheckSalary 
BEFORE INSERT ON EmployeeTable
FOR EACH ROW 
BEGIN
    IF NEW.EmployeeSalary < 1000 THEN
        SET NEW.EmployeeSalary = 10000;
    END IF;
END;

When you try to execute this code, you may encounter with the following error -

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

This error occurred because when the MYSQL found the first semicolon it thought the logic or code ends here instead of knowing the trigger logic. Thus causing syntax error. So it is important to change the delimiter when we have multiple SQL statements at one block of the code.

Creating Trigger using Delimiter

Now, Let us see the usage of delimiter in the trigger with an example - where the trigger will activate whenever you enter the EmployeeSalary less than 1000 then it will be set to 10000 by default.

Firstly, let us create table. The query to create a table is as follows -

CREATE TABLE EmployeeTable (
    EmployeeId INT NOT NULL,
    EmployeeName VARCHAR(100) NOT NULL,
    EmployeeSalary FLOAT,
    PRIMARY KEY (EmployeeId)
);

Creating Trigger

After creating a table, you need to create a trigger on insert command. The query to create a trigger is as follows.

DELIMITER //
CREATE TRIGGER CheckSalary 
BEFORE INSERT ON EmployeeTable
FOR EACH ROW 
BEGIN
    IF NEW.EmployeeSalary < 1000 THEN
        SET NEW.EmployeeSalary = 10000;
    END IF;
END //
DELIMITER ;

Let us break down the above code to understand its purpose and functionality.

• DELIMITER // - This statement allows semicolons to be used within the body of the trigger without causing MySQL to misinterpret them as end statements. It sets the delimiter to // just for the definition of this trigger temporarily.
• Delimiter ; - This will reset the delimiter back to the default semicolon (;), which is important if you're running other SQL commands after the trigger definition.
• BEFORE INSERT - As its name suggests, this trigger is executed right before a value is being inserted into a database table.

Inserting values into table

Now you can activate the trigger by inserting records into a table. If you insert EmployeeSalary less than 1000, then it does not give any error, but it will store a default value which i have given as 10000.

The query to INSERT record is as follows -

INSERT INTO EmployeeTable values(1,'Carol',500);

Verification

Now check all the records in the Employee table using SELECT statement. The query is as follows -

SELECT * FROM EmployeeTable;

Following is the output of the above code ?

EmployeeId	EmployeeName	EmployeeSalary
1	Carol	10000

From the above output it is clear that the trigger got activated as we have entered the salary less than 1000. If you insert salary greater than 1000 or 1000 then it will show the number you have entered.

Deleting Records Using TRUNCATE command

The TRUNCATE command is used to delete all records from a table quickly and efficiently. This means that all the data is cleared, and the table is empty after execution.

TRUNCATE TABLE EmployeeTable;

Now, after truncating the table, you can insert new data using the INSERT INTO command. This command allows you to add rows to the table with specific values. The query to insert records in the table is as follows -

INSERT INTO EmployeeTable 
values
(2,'Bob',1000),
(3,'Carol',2500);

To view the data in the table after insertion, use the SELECT statement.

 SELECT * FROM EmployeeTable;

Following is the output of the above code ?

EmployeeId	EmployeeName	EmployeeSalary
2	Bob	1000
3	Carol	2500

Look at the above sample output, here the EmployeeSalary is greater than or equal to 1000. This will give you your salary.

Thus the DELIMITER // command allows multiple statements to be executed within a single trigger specification, so you can create complex triggers.

Key Points to Remember When Using Delimiter:

• Change the delimiter temporarily this will allow you to use semicolons inside the trigger body.
• Once the delimiter has been defined, it is always a good practice to reset the delimiter back to the default semicolon, Otherwise you could get problems in running subsequent SQL statements.
• DELIMITER is not an SQL statement. It is used exclusively by the MySQL command-line client, so it might not be declared if you run the SQL script outside of MySQL's CLI, for example, using a GUI application or API.