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.

Syntax

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:

  • First, we will specify the trigger name that we want to create. It should be unique within the schema.
  • Second, we will specify the trigger action time, which should be AFTER UPDATE. This trigger will be invoked after each row of alterations occurs on the table.
  • Third, we will specify the table name to which the trigger is associated. It must be written after the ON If we did not specify the table name, a trigger would not exist.
  • Finally, we will specify the trigger body that contains a statement for execution when the trigger is activated.

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:

Restrictions

  • We can access the OLD rows but cannot update them.
  • We can access the NEW rows but cannot update them.
  • We cannot create an AFTER UPDATE trigger on a VIEW.

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.

MySQL AFTER UPDATE TRIGGER

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.


MySQL AFTER UPDATE TRIGGER

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:

MySQL AFTER UPDATE TRIGGER

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:

MySQL AFTER UPDATE TRIGGER

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:

MySQL AFTER UPDATE TRIGGER

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.

MySQL AFTER UPDATE TRIGGER

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.

MySQL AFTER UPDATE TRIGGER

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:

MySQL AFTER UPDATE TRIGGER

4. Clicking on the Alter Table option gives the screen as below:

MySQL AFTER UPDATE TRIGGER

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:

MySQL AFTER UPDATE TRIGGER

6. Now, complete the trigger code, review them once again, and if no error is found, click on the Apply button.

MySQL AFTER UPDATE TRIGGER

7. After clicking on the Apply button, click on the Finish button to complete the process.

MySQL AFTER UPDATE TRIGGER

8. If we look at the schema menu, we can see student_update_trigger under the "students" table as follows:

MySQL AFTER UPDATE TRIGGER




Latest Courses