MySQL AFTER UPDATE Trigger

Updated June 6, 2023

Introduction to MySQL AFTER UPDATE Trigger

The MySQL AFTER UPDATE Trigger is a MySQL Trigger that spontaneously invokes every time an update occurs on a database table related to the trigger. We need to understand how to build up a MySQL AFTER UPDATE Trigger so that MySQL can log whatever alterations are made to an associated trigger table in the database. A trigger in MySQL performs a group of actions automatically when a specific modification operation is carried out on the table linked to it. These operations can be from MySQL INSERT, UPDATE, DELETE, or ALTER query statements.

Syntax

The succeeding syntax structure displays the method of creating the MySQL AFTER UPDATE Triggering the MySQL database table:

CREATE TRIGGER TriggerName AFTER UPDATE
ON TABLE TableName FOR EACH ROW TriggerBody

Here, we will elaborate on the syntax above:

• Initially, in the CREATE TRIGGER clause, we need to assign a specific name to the trigger being created.
• Then, we will include the time required to call the MySQL trigger by applying the AFTER UPDATE trigger clause.
• After this, we will use the ON keyword to state the table’s name in the database on which we want to add the trigger to act with events.
• The term FOR EACH ROW denotes that the trigger applies for each row if the update query takes place effectively.
• Finally, we will implement the program code for the AFTER UPDATE trigger that comprises one or more query statements specified by the trigger body in the syntax above.

You need to note that if you state more than a single statement, you must apply the BEGIN END code block.

Also, for this, you need to alter the default delimiter given in the below code structure:

DELIMITER $$
CREATE TRIGGER TriggerName AFTER UPDATE
ON TABLE TableName FOR EACH ROW
BEGIN
--TriggerBody statements
END$$
DELIMITER;

In this AFTER UPDATE Trigger in MySQL, the user can access either NEW or OLD rows but cannot alter them.

How does AFTER UPDATE Trigger work in MySQL?

• The MySQL AFTER UPDATE Trigger is a stored code program that is triggered instantly to respond to MySQL events, such as CRUD operations on the table linked to the trigger applied.
• This records the modifications that undergo a table when update commands are executed in the MySQL database.
• Also, the MySQL AFTER UPDATE Trigger is created to validate or confirm data records before the rows are updated to the table provided.

There are some restrictions for this explained:

• On a MySQL view, an AFTER Trigger program cannot be created.
• Using the AFTER trigger, we cannot modify the NEW data values.
• Also, we cannot modify the OLD data values using the AFTER trigger.

Examples to Implement MySQL AFTER UPDATE Trigger

Let us consider and demonstrate the following examples to explain the uses and working of this AFTER UPDATE Trigger in MySQL. Firstly, we will set up demo data tables in the database. Suppose we have created the below tables shown in the examples to use MySQL AFTER the UPDATE trigger:

1. Using AFTER UPDATE Trigger Query

We will create a sample table named Room where we can use the AFTER UPDATE trigger to view the results as logs in the associated table as Room_logs. The SQL statements are as follows:

Code:

DROP TABLE IF EXISTS Room;
CREATE TABLE Room( Room_Num INT PRIMARY KEY AUTO_INCREMENT,Room_Name VARCHAR(255) NOT NULL, Build_Num INT NOT NULL);

Also, we will enter some records using the INSERT query:

INSERT INTO Room(Room_Num, Room_Name, Build_Num) VALUES('1','pawan Vihar','1');

Querying data from Room tables to see its contents:

SELECT * FROM Room;

Output:

Again, creating the next table to store the modifications that occurred on the parent table Room after any update command as follows:

CREATE TABLE Room_logs( User_ID VARCHAR(255), Update_Info VARCHAR(255));

View the table at present:

SELECT * FROM Room_logs;

Output:

Now, we will move towards creating the statement to build up an AFTER UPDATE trigger on the Room table:

DELIMITER $$
CREATE TRIGGER room_update
AFTER UPDATE ON Room FOR EACH ROW
BEGIN
INSERT into Room_logs(User_ID,Update_Info) VALUES (user(), CONCAT('Updated Room  Record (',OLD.Room_Num,' ',OLD.Room_Name,' ',OLD.Build_Num,') to (',NEW.Room_Num,' ',NEW.Room_Name,' ',NEW.Build_Num,')'));
END$$
DELIMITER ;

After room_updatetrigger is created, this will be repeatedly triggered before an update event occurs for every row in the room table.

To examine in detail, when you update any value in the amount column, a fresh row will be inserted into the room_logs table to record the changes made.

UPDATE Room SETBuild_Num=Build_Num+ 2;

Output:

Again, go to your Room_logs table and see the difference and result of the AFTER UPDATE trigger using the following query:

SELECT * FROM Room_logs;

Output:

2. Using AFTER UPDATE Trigger Query with comparison operator and WHERE clause statements

We will create another sample table named Payment as follows:

DROP TABLE IF EXISTS Payment;
CREATE TABLE Payment (CustomerID INT PRIMARY KEY, CustomerNameVARCHAR(255) NOT NULL, PAmount INT NOT NULL);

Also, we will insert a few records into the table above:

INSERT INTO Payement(CustomerID, CustomerName, PAmount) VALUES('101','Anita', '3400');

Display contents of Payment:

SELECT * from Payment;

Output:

To save any changes made to the Payment table during updates and trigger fires, please create a log table named Payment_logs.

CREATE TABLE Payment_logs(User_ID VARCHAR(255), Before_PAmount INT NOT NULL, After_PAmount INT NOT NULL);

Viewing Payment_logs:

SELECT * FROM Payment_logs;

Output:

Now, building up the trigger below:

DELIMITER $$
CREATE TRIGGER `payment_update`
AFTER UPDATE ON `payment` FOR EACH ROW
BEGIN
IF OLD.PAmount<>new.PAmount THEN
INSERT INTO Payment_logs(User_ID,Before_PAmount, After_PAmount)
VALUES(user(), old.PAmount, new.PAmount);
END IF;
END$$
DELIMITER ;

To test the AFTER UPDATE trigger, let us perform an update command:

UPDATE Payment SET PAmount = 7000 WHERE CustomerName = 'Sahil';

Output:

Now, display the changes in the Payment_logs:

SELECT * FROM Payment_logs;

Output:

Conclusion

• In MySQL, the triggers are significant to work and helpful in conditions like imposing business guidelines, possessing an audit trace, and authorizing input information.
• Therefore, the AFTER UPDATE Trigger is invoked to alert each time an update command is executed in that specific database table, providing information about the update.

Recommended Articles

This is a guide to MySQL AFTER UPDATE Trigger. Here we discuss an introduction to MySQL AFTER UPDATE Trigger with appropriate syntax and how it works with query examples. You can also go through our other related articles to learn more –

1. MySQL REGEXP_REPLACE()
2. TRUNCATE() in MySQL
3. MySQL DECODE()
4. MySQL count()