EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL AFTER UPDATE Trigger

MySQL AFTER UPDATE Trigger

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated June 6, 2023

MySQL AFTER UPDATE Trigger

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

AFTER UPDATE

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:

MySQL AFTER UPDATE Trigger - 2

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:

MySQL AFTER UPDATE Trigger - 3

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:

MySQL AFTER UPDATE Trigger - 4

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:

comparison operator

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:

comparison operator

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:

MySQL AFTER UPDATE Trigger - 8

Now, display the changes in the Payment_logs:

SELECT * FROM Payment_logs;

Output:

MySQL AFTER UPDATE Trigger - 9

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()
C++ PROGRAMMING Course Bundle - 9 Courses in 1 | 5 Mock Tests
37+ Hour of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Course Bundle - 28 Courses in 1 | 5 Mock Tests
123+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
204+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Course Bundle - 13 Courses in 1
53+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests
 93+ Hour of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more