MySQL AFTER UPDATE TRIGGER
The AFTER UPDATE trigger in MySQL is invoked automatically whenever an UPDATE event is fired on the table associated with the triggers. In this article, we are going to learn how to create an AFTER UPDATE trigger with its syntax and example.
The following is the syntax to create an AFTER UPDATE trigger in MySQL:
We can explain the parameters of AFTER UPDATE trigger syntax as below:
If we want to execute more than one statement, we will use the BEGIN END block that contains a set of SQL queries to define the logic for the trigger. See the below syntax:
AFTER UPDATE Trigger Example
Let us understand how to create an AFTER UPDATE trigger using the CREATE TRIGGER statement in MySQL with an example.
Suppose we have created a table named students to store the student's information as follows:
Next, we will insert some records into this table using the below statement:
Execute the SELECT query to see the table data.
Third, we will create another table named students_log that keeps the updated information in the selected user.
We will then create an AFTER UPDATE trigger that promotes all students in the next class, i.e., 6 will be 7, 7 will be 8, and so on. Whenever an updation is performed on a single row in the "students" table, a new row will be inserted in the "students_log" table. This table keeps the current user id and a description regarding the current update. See the below trigger code.
In this trigger, we have first specified the trigger name after_update_studentsInfo. Then, specify the triggering event. Third, we have specified the table name on which the trigger is associated. Finally, we have written the trigger logic inside the trigger body that performs updation in the "students" table and keeps the log information in the "students_log" table.
How to call the AFTER UPDATE trigger?
First, we will update the "students" table using the following statements that invoke the above-created trigger:
Next, we will query data from the students and students_log table. We can see that table has been updated after the execution of the query. See the below output:
Again, we will query data from the students_log table that keeps the current user id and a description regarding the current update. See the below output:
How to create AFTER UPDATE trigger in MySQL workbench?
To create an AFTER UPDATE trigger in workbench, we first launch the MySQL Workbench and log in using the username and password. We will get the UI as follows:
Now do the following steps to create an AFTER UPDATE trigger:
1. Go to the Navigation tab and click on the Schema menu. It will display all databases available in the MySQL database server.
2. Select the database (for example, mystudentdb). Then, double click on the selected schema. It displays the sub-menu containing Tables, Views, Functions, and Stored Procedures. See the below screen.
3. Expand the Tables sub-menu and select a table on which you want to create a trigger. Then, right-click on the selected table (for example, students), and 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 AFTER 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 the 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 to complete the process.
8. If we look at the schema menu, we can see student_update_trigger under the "students" table as follows: