MySQL AFTER UPDATE Trigger

Introduction to MySQL AFTER UPDATE Trigger

MySQL AFTER UPDATE Trigger is a MySQL Trigger that is responsible to be invoked spontaneously every after an update happens on a database table that is related to the trigger. We need to understand how to build up a MySQL AFTER UPDATE Trigger so that the MySQL can log whatever alterations are made to an associated trigger table in the database. As a trigger in MySQL is known to be a group of actions that perform automatically when a particular modification operation is carried out in the specific table linked with it. These operations can be any out of 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 the syntax above:

• Initially, we need to give a specific name to the trigger that is to be created in the clause: CREATE TRIGGER.
• Then, we will include the time required to call the MySQL trigger by applying the AFTER UPDATE trigger clause.
• After this, using the ON keyword we will also state the name of the table available 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 then, you are required to apply the BEGIN END code block.

Also, for this you need to alter the default delimiter is 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 rows either NEW or OLD ones but is not able to alter them.

How AFTER UPDATE Trigger work in MySQL?

• The MySQL AFTER UPDATE Trigger is a stored code program that is triggered instantly to respond in the occurrence of MySQL events such as CRUD operations taking place on the table linked to the trigger applied.
• This MySQL AFTER UPDATE Trigger provides a record of 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 as:

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

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 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 as:

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 occurring of an update event for every row in the room table.

To examine in detail, when you update any value in the amount column then, a fresh row will be inserted to 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 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:

Again, creating log table named Payment_logs to save the changes made to table Payment on update and whenever the trigger is fired:

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 be helpful in conditions like imposing business guidelines, possession of an audit trace, and authorizing input information.
• Therefore, MySQL AFTER UPDATE Trigger is summoned for an alert each time to provide the information that an update command has been executed in that specific database table.

Recommended Articles

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

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