MySQL trigger example after update

In this article, we will see how to create the AFTER UPDATE trigger with a practical example.

Table of Contents:-

• Syntax of AFTER UPDATE trigger
• Example of AFTER UPDATE trigger

READ MORE ABOUT TRIGGERS: What are triggers in MySQL

Let us get started by making the sample data. We will be creating two tables, patient_food_allergies, patient_allergy_modification_history, and inserting some rows into patient_food_allergies.

#create the table patient_food_allergies
CREATE TABLE patient_food_allergies (
   patient_id INT,
   patient_food_allergy VARCHAR(255)
);
#inserting rows into patient_food_allergies
 INSERT INTO patient_food_allergies (patient_id, patient_food_allergy) 
 VALUES(1,"Milk"),
 (2,"Nuts"),
 (3,"Wheat"),
 (4,"Nuts"),
 (5,"Nuts"),
 (6,"Pumpkin");
#create the table patient_allergy_modification_history
 CREATE TABLE patient_allergy_modification_history (
   patient_id INT,
   patient_food_allergy VARCHAR(255),
   patient_food_allergy_changed VARCHAR(255),
   date_of_change DATE
);

We will now take a glance at the patient_food_allergies table by executing:

SELECT * FROM patient_food_allergies;

Output:-

Syntax of AFTER UPDATE trigger

CREATE TRIGGER name_of_your_trigger
AFTER UPDATE
ON name_of_your_table FOR EACH ROW
trigger_body;

• name_of_your_trigger is the name given to your trigger.
• AFTER is the action time modifier; that is, the trigger will activate after the event.
• name_of_your_table is the table linked with the trigger. Updation of a row from this table will cause the trigger to run.
• trigger_body is the code that will be applied when the trigger executes.
• In MySQL Triggers, OLD and NEW keywords are used within the trigger body to distinguish between the columns Before and After the DML execution.

Example of AFTER UPDATE trigger

Create AFTER UPDATE trigger- whenever patient_food_allergy gets updated on the table patient_food_allergies, the trigger should save the old and new values of patient_food_allergy and the date of modification in the patient_allergy_modification_history table.

Observe the below trigger code for the solution.

DELIMITER //
CREATE TRIGGER after_update_patient_food_allergies
AFTER UPDATE
ON patient_food_allergies FOR EACH ROW
BEGIN
    IF  (OLD.patient_food_allergy <> new.patient_food_allergy)
    THEN
        INSERT INTO patient_allergy_modification_history(patient_id, 
         patient_food_allergy, patient_food_allergy_changed, date_of_change)
        VALUES(old.patient_id, old.patient_food_allergy, new.patient_food_allergy, sysdate());
    END IF;
END//
DELIMITER ;
 

Explanation:-

• Here, we are creating a AFTER UPDATE trigger named after_update_patient_food_allergies on table patient_food_allergies.
• If the update happens on the table patient_food_allergies and patient_food_allergy is modified, then the trigger will insert a new row to the table patient_allergy_modification_history with values old patient_food_allergy, new patient_food_allergy, and the modification date.
• The date _of_change value gets pulled by the function sysdate().

Testing the Trigger:-

Let us now test the trigger by updating a record in the patient_food_allergies table.

 UPDATE patient_food_allergies SET patient_food_allergy = 'Peanuts' WHERE patient_id = 4;

Action Output:-

The row with patient_id =4 is updated as shown in image_2. We can take a peek into the patient_food_allergies table.

 SELECT * FROM patient_food_allergies;

Let us now check if the trigger worked correctly or not.

SELECT * FROM patient_allergy_modification_history;

The output in image_4 shows that the record is inserted into the patient_allergy_modification_history table. 

READ MORE:

• What are triggers in MySQL
• Create trigger on delete
• MySQL trigger example after insert
• MySQL trigger example before insert
• MySQL trigger example before update
• Trigger with if condition in mysql

We hope this article helped you to understand how to create AFTER UPDATE triggers in MySQL. Good Luck!!!