DELIMITER // What role does it play in MySQL triggers?

DELIMITER // Can be used to change the semicolon (;) of a statement to //. Now you can write multiple statements using semicolons in triggers.

This is a demonstration of the trigger. In this example, whenever you enter an EmployeeSalary less than 1000, it will default to 10000.

First, let's create a table. The query to create the 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 the table, you need to create a trigger on the insert command. The query to create the 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 use the insert command to check the trigger. If the EmployeeSalary inserted is less than 1000, no error will appear but it will store a default value, which I gave is 10000.

The query to insert records is as follows -

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

Now use the select statement to check all the records in the table. 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 enter 1000 or greater, only your number will be displayed. I have used truncate command to delete previous records from the table.

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

Query to insert records into 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)

This is a query that uses a select statement to check all the records in the table.

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)

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

The above is the detailed content of DELIMITER // What role does it play in MySQL triggers?. For more information, please follow other related articles on the PHP Chinese website!