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:

CREATE TRIGGER trigger_name   

AFTER UPDATE  

ON table_name FOR EACH ROW  

trigger_body ;

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:

DELIMITER $$   

CREATE TRIGGER trigger_name AFTER UPDATE  

ON table_name FOR EACH ROW  

BEGIN  

   variable declarations  

   trigger code  

END$$  

DELIMITER ;

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:

mysql> CREATE TABLE students(    

    id int NOT NULL AUTO_INCREMENT,    

    name varchar(45) NOT NULL,    

    class int NOT NULL,    

    email_id varchar(65) NOT NULL,    

    PRIMARY KEY (id)    

);

Next, we will insert some records into this table using the below statement:

INSERT INTO students (name, class, email_id)     

VALUES ('Stephen', 6, '[email protected]'),   

('Bob', 7, '[email protected]'),   

('Steven', 8, '[email protected]'),   

('Alexandar', 7, '[email protected]');

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.

mysql> CREATE TABLE students_log(    

    user varchar(45) NOT NULL,    

    descreptions varchar(65) NOT NULL  

);

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.

DELIMITER $$  

  

CREATE TRIGGER after_update_studentsInfo  

AFTER UPDATE  

ON students FOR EACH ROW  

BEGIN  

    INSERT into students_log VALUES (user(),   

    CONCAT('Update Student Record ', OLD.name, ' Previous Class :',  

    OLD.class, ' Present Class ', NEW.class));  

END $$  

  

DELIMITER ;

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:

mysql> UPDATE students SET class = class + 1;

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: