What does DELIMITER // do in a Trigger in MySQL?

The DELIMITER // can be used to change the statement from semicolon (;) to //. Now you can write multiple statements with semi-colon in a trigger.

Here is the demo of triggers. In this example whenever you enter the EmployeeSalary less than 1000 then it will by default set to 10000.

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

mysql> create table EmployeeTable
-> (
-> EmployeeId int,
-> EmployeeName varchar(100),
-> EmployeeSalary float
-> );
Query OK, 0 rows affected (0.76 sec)

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

mysql> delimiter //
mysql> create trigger CheckSalary before insert on EmployeeTable
-> for each row if new.EmployeeSalary < 1000 then set
-> new.EmployeeSalary=10000;
-> end if;
-> //
Query OK, 0 rows affected (0.40 sec)
mysql> delimiter ;

Now you can check the trigger using insert command. 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 10000.

The query to insert record is as follows -

mysql> insert into EmployeeTable values(1,'Carol',500);
Query OK, 1 row affected (0.25 sec)

Now check all the records from the table using select statement. The query is as follows.

mysql> select *from EmployeeTable;

The following is the output.

+------------+--------------+----------------+
| EmployeeId | EmployeeName | EmployeeSalary |
+------------+--------------+----------------+
| 1          | Carol        | 10000          |
+------------+--------------+----------------+
1 row in set (0.00 sec)

If you insert 1000 or greater than 1000 then it will show your number only. I have deleted the previous record from the table using truncate command.

mysql> truncate table EmployeeTable;
Query OK, 0 rows affected (1.44 sec)

The query to insert records in the table.

mysql> insert into EmployeeTable values(2,'Bob',1000);
Query OK, 1 row affected (0.14 sec)

mysql> insert into EmployeeTable values(3,'Carol',2500);
Query OK, 1 row affected (0.19 sec)

Here is the query to check all records from the table using select statement.

mysql> select *from EmployeeTable;

The following is the output.

+------------+--------------+----------------+
| EmployeeId | EmployeeName | EmployeeSalary |
+------------+--------------+----------------+
| 2          | Bob          | 1000           |
| 3          | Carol        | 2500           |
+------------+--------------+----------------+
2 rows in set (0.00 sec)

Look at the above sample output, EmployeeSalary is greater than or equal to 1000. This will give your salary. Remember, if it is less than 1000 then the default value is set to 10000.