MySQL BEFORE INSERT TRIGGER
Before Insert Trigger in MySQL is invoked automatically whenever an insert operation is executed. In this article, we are going to learn how to create a before insert trigger with its syntax and example.
The following is the syntax to create a BEFORE INSERT trigger in MySQL:
The BEFORE INSERT trigger syntax parameter can be explained as below:
If we want to execute multiple statements, we will use the BEGIN END block that contains a set of queries to define the logic for the trigger. See the below syntax:
BEFORE INSERT Trigger Example
Suppose we have created a table named employee as follows:
Next, we will insert some records into the employee table and then execute the SELECT statement to see the table data as follows:
Next, we will use a CREATE TRIGGER statement to create a BEFORE INSERT trigger. This trigger is invoked automatically that inserts the occupation = 'Leader' if someone tries to insert the occupation = 'Scientist'.
If the trigger is created successfully, we will get the output as follows:
How to call the BEFORE INSERT trigger?
We can use the following statements to invoke the above-created trigger:
After execution of the above statement, we will get the output as follows:
Execute the SELECT statement to verify the output:
In this output, we can see that on inserting the occupation column values as 'Scientist', the table will automatically fill the 'Doctor' value by invoking a trigger.
How to create BEFORE INSERT Trigger in MySQL workbench?
To create a before insert trigger using this tool, we first need to launch the MySQL Workbench and log in using the username and password we created earlier. We will get the screen as follows:
Now do the following steps for creating BEFORE INSERT trigger:
1. Go to the Navigation tab and click on the Schema menu that contains all the databases available in the MySQL server.
2. Select the database (for example, mysqltestdb), double click on it. It will show the sub-menu containing Tables, Views, Functions, and Stored Procedures. See the below screen.
3. Expand the Tables sub-menu and select the table on which you want to create a trigger. After selecting a table, right-click on the selected table (for example, employee), and then click on the Alter Table option. See the below image:
4. Clicking on the Alter Table option gives the screen as below:
5. Now, click on the Trigger tab shown in the previous section's red rectangular box, then select the Timing/Event BEFORE INSERT. We will notice that there is a (+) icon button to add a trigger. Clicking on that button, we will get a default code on the trigger based on choosing Timing/Event:
6. Now, complete the trigger code, review them once again, and no error found, click on the Apply button.
7. After clicking on the Apply button, click on the Finish button for completion.
8. If we take at the schema menu, we will see employee_BEFORE_INSERT trigger under the employee table as follows: