MySQL BEFORE UPDATE Trigger
BEFORE UPDATE Trigger in MySQL is invoked automatically whenever an update operation is fired on the table associated with the trigger. In this article, we are going to learn how to create a before update trigger with its syntax and example.
The following is the syntax to create a BEFORE UPDATE trigger in MySQL:
The BEFORE UPDATE trigger syntax parameter are 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 UPDATE Trigger Example
Let us understand how to create a BEFORE UPDATE trigger using the CREATE TRIGGER statement in MySQL with an example.
Suppose we have created a table named sales_info as follows:
Next, we will insert some records into the sales_info table as follows:
Then, execute the SELECT statement to see the table data as follows:
Next, we will use a CREATE TRIGGER statement to create a BEFORE UPDATE trigger. This trigger is invoked automatically before an update event occurs in the table.
If the trigger is created successfully, we will get the output as follows:
The trigger produces an error message and stops the updation if we update the value in the quantity column to a new value two times greater than the current value.
Let us understand the created trigger in details:
First, we have specified the trigger name as befor_update_salesInfo in the CREATE TRIGGER clause. Second, specify the triggering event and then the table name on which the trigger is associated. Third, we have declared a variable and set its value. Finally, we have specified the trigger body that checks if the new value is two times greater than the old value and then raises an error.
How to call the BEFORE UPDATE trigger?
First, we can use the following statements that update the quantity of the row whose id = 2:
This statement works well because it does not violate the rule. Next, we will execute the below statements that update the quantity of the row as 600 whose id = 2
It will give the error as follows because it violates the rule. See the below output.
How to create BEFORE UPDATE Trigger in MySQL workbench?
To create a BEFORE UPDATE trigger using MySQL workbench, we first need to launch it and then 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 UPDATE 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, employeedb), double click on it, and display 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, sales_info), 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 UPDATE. We will notice that there is a (+) icon button to add a trigger. Clicking on that button, we will get a default code on trigger based on choosing Timing/Event:
6. Now, complete the trigger code, review them once again, and if no error is 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 the trigger sales_info_before_update under the sales_info table as follows: