{"id":8498,"date":"2019-09-06T23:36:55","date_gmt":"2019-09-07T06:36:55","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=8498"},"modified":"2025-09-24T17:57:50","modified_gmt":"2025-09-25T00:57:50","slug":"mysql-after-update-trigger","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-triggers\/mysql-after-update-trigger\/","title":{"rendered":"MySQL AFTER UPDATE Trigger"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a MySQL AFTER UPDATE<\/code> trigger to log the changes made to a table.<\/p>\n\n\n\n

Introduction to MySQL AFTER UPDATE triggers<\/h2>\n\n\n\n

MySQL AFTER UPDATE<\/code> triggers<\/a> are invoked automatically after an update<\/a> event occurs on the table associated with the triggers.<\/p>\n\n\n\n

The following shows the syntax of creating a MySQL AFTER UPDATE<\/code> trigger:<\/p>\n\n\n

CREATE<\/span> TRIGGER<\/span> trigger_name\nAFTER<\/span> UPDATE<\/span>\nON<\/span> table_name FOR<\/span> EACH<\/span> ROW<\/span>\ntrigger_body<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
Second, use AFTER UPDATE<\/code> clause to specify the time to invoke the trigger.<\/p>\n\n\n\n
Third, specify the name of the table to which the trigger belongs after the ON<\/code> keyword. The FOR EACH ROW<\/code> instructs the trigger to fire each time a row is updated.<\/p>\n\n\n\n
Finally, define the trigger body which consists of one or more statements.<\/p>\n\n\n\n
If the trigger body has more than one statement, you need to use the BEGIN END<\/code> block. And, you also need to change the default delimiter<\/a> as shown in the following code:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> TRIGGER<\/span> trigger_name\n    AFTER<\/span> UPDATE<\/span>\n    ON<\/span> table_name FOR<\/span> EACH<\/span> ROW<\/span>\nBEGIN<\/span>\n    -- statements<\/span>\nEND<\/span>$$    \n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In a AFTER UPDATE<\/code> trigger, you can access OLD<\/code> and NEW<\/code> rows but cannot update them.<\/p>\n\n\n\n
Note that the OLD<\/code> represents the values of columns before the UPDATE<\/code> whereas the NEW<\/code> represents the values of columns after the UPDATE<\/code>.<\/p>\n\n\n
\n
\"MySQL<\/figure>\n<\/div>\n\n\n
MySQL AFTER UPDATE trigger example<\/h2>\n\n\n\n
Let’s look at an example of creating a AFTER UPDATE<\/code> trigger.<\/p>\n\n\n\n
Setting up a sample table<\/h3>\n\n\n\n
First, create a table<\/a> called Sales<\/code>:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> Sales;\n\nCREATE<\/span> TABLE<\/span> Sales (\n    id<\/span> INT<\/span> AUTO_INCREMENT,\n    product VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n    quantity INT<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> 0<\/span>,\n    fiscalYear SMALLINT<\/span> NOT<\/span> NULL<\/span>,\n    fiscalMonth TINYINT<\/span> NOT<\/span> NULL<\/span>,\n    CHECK<\/span>(fiscalMonth >= 1<\/span> AND<\/span> fiscalMonth <= 12<\/span>),\n    CHECK<\/span>(fiscalYear BETWEEN<\/span> 2000<\/span> and<\/span> 2050<\/span>),\n    CHECK<\/span> (quantity >=0<\/span>),\n    UNIQUE<\/span>(product, fiscalYear, fiscalMonth),\n    PRIMARY KEY<\/span>(id<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> Sales(product, quantity, fiscalYear, fiscalMonth)\nVALUES<\/span>\n    ('2001 Ferrari Enzo'<\/span>,140<\/span>, 2021<\/span>,1<\/span>),\n    ('1998 Chrysler Plymouth Prowler'<\/span>, 110<\/span>,2021<\/span>,1<\/span>),\n    ('1913 Ford Model T Speedster'<\/span>, 120<\/span>,2021<\/span>,1<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * FROM<\/span> Sales;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Finally, create a table<\/a> that stores the changes in the quantit<\/code>y column from the sales<\/code> table:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> SalesChanges;\n\nCREATE<\/span> TABLE<\/span> SalesChanges (\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    salesId INT<\/span>,\n    beforeQuantity INT<\/span>,\n    afterQuantity INT<\/span>,\n    changedAt TIMESTAMP<\/span> NOT<\/span> NULL<\/span> DEFAULT<\/span> CURRENT_TIMESTAMP<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating AFTER UPDATE trigger example<\/h3>\n\n\n\n
The following statement creates an AFTER UPDATE<\/code> trigger on the sales<\/code> table:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> TRIGGER<\/span> after_sales_update\nAFTER<\/span> UPDATE<\/span>\nON<\/span> sales FOR<\/span> EACH<\/span> ROW<\/span>\nBEGIN<\/span>\n    IF<\/span> OLD.quantity <> new.quantity THEN<\/span>\n        INSERT<\/span> INTO<\/span> SalesChanges(salesId,beforeQuantity, afterQuantity)\n        VALUES<\/span>(old.id, old.quantity, new.quantity);\n    END<\/span> IF<\/span>;\nEND<\/span>$$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This after_sales_update<\/code> trigger is automatically fired before an update event occurs for each row in the sales<\/code> table.<\/p>\n\n\n\n
If you update the value in the quantity<\/code> column to a new value the trigger inserts a new row to log the changes in the SalesChanges<\/code> table.<\/p>\n\n\n\n
Let’s examine the trigger in detail:<\/p>\n\n\n\n
CREATE<\/span> TRIGGER<\/span> after_sales_update<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, the triggering event is:<\/p>\n\n\n
AFTER UPDATE<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, the table that the trigger is associated with is sales<\/code>:<\/p>\n\n\n
ON Sales FOR EACH ROW<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
IF OLD.quantity <> new.quantity THEN\n    INSERT<\/span> INTO<\/span> SalesChanges(salesId,beforeQuantity, afterQuantity)\n    VALUES<\/span>(old.id, old.quantity, new.quantity);\nEND<\/span> IF<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Testing the MySQL AFTER UPDATE trigger<\/h3>\n\n\n\n
First, update<\/a> the quantity of the row with id 1 to 350:<\/p>\n\n\n
UPDATE<\/span> Sales \nSET<\/span> quantity = 350<\/span>\nWHERE<\/span> id<\/span> = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The after_sales_update<\/code> was invoked automatically.<\/p>\n\n\n\n
Second, query data from the SalesChanges<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> SalesChanges;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
Third, increase the sales quantity of all rows to 10%:<\/p>\n\n\n
UPDATE<\/span> Sales \nSET<\/span> quantity = CAST<\/span>(quantity * 1.1<\/span> AS<\/span> UNSIGNED<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, query data from the SalesChanges<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> SalesChanges;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The trigger fired three times because of the updates of the three rows.<\/p>\n\n\n\n
In this tutorial, you have learned how to create a MySQL AFTER UPDATE<\/code> trigger to validate data before it is updated to a table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Finally, use the IF-THEN<\/a><\/code> statement inside the trigger body to check if the new value is not the same as the old one, then insert the changes into the SalesChanges<\/code> table:<\/p>\n\n\n
First, the name of the trigger is after_sales_update<\/code> specified in the CREATE TRIGGER<\/a><\/code> clause:<\/p>\n\n\n
Third, query data<\/a> from the Sales<\/code> table to display its contents:<\/p>\n\n\n
Second, insert sample data<\/a> into the Sales<\/code> table:<\/p>\n\n\n
First, specify the name of the trigger that you want to create in the CREATE TRIGGER<\/a><\/code> clause.<\/p>\n\n\n\n