MySQL AFTER UPDATE Trigger
Summary: in this tutorial, you will learn how to create a MySQL AFTER UPDATE trigger to log the changes made to a table.
Introduction to MySQL AFTER UPDATE triggers
MySQL AFTER UPDATE triggers are invoked automatically after an update event occurs on the table associated with the triggers.
The following shows the syntax of creating a MySQL AFTER UPDATE trigger:
CREATE TRIGGER trigger_name
AFTER UPDATE
ON table_name FOR EACH ROW
trigger_body
Code language: SQL (Structured Query Language) (sql)
In this syntax:
First, specify the name of the trigger that you want to create in the CREATE TRIGGER clause.
Second, use AFTER UPDATE clause to specify the time to invoke the trigger.
Third, specify the name of the table to which the trigger belongs after the ON keyword.
Finally, specify the trigger body which consists of one or more statements.
If the trigger body has more than one statement, you need to use the BEGIN END block. And, you also need to change the default delimiter as shown in the following code:
DELIMITER $$AFTER UPDATE
ON table_name FOR EACH ROW
BEGIN
— statements
END$$
Code language: SQL (Structured Query Language) (sql)
In a AFTER UPDATE trigger, you can access OLD and NEW rows but cannot update them.
MySQL AFTER UPDATE trigger example
Let’s look at an example of creating a AFTER UPDATE trigger.
Setting up a sample table
First, create a table called Sales:
DROP TABLE IF EXISTS Sales;id INT AUTO_INCREMENT,
product VARCHAR(100) NOT NULL,
quantity INT NOT NULL DEFAULT 0,
fiscalYear SMALLINT NOT NULL,
fiscalMonth TINYINT NOT NULL,
CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),
CHECK(fiscalYear BETWEEN 2000 and 2050),
CHECK (quantity >=0),
UNIQUE(product, fiscalYear, fiscalMonth),
PRIMARY KEY(id)
);
Code language: SQL (Structured Query Language) (sql)
Second, insert sample data into the Sales table:
INSERT INTO Sales(product, quantity, fiscalYear, fiscalMonth)
VALUES
('2001 Ferrari Enzo',140, 2021,1),
('1998 Chrysler Plymouth Prowler', 110,2021,1),
('1913 Ford Model T Speedster', 120,2021,1);
Code language: SQL (Structured Query Language) (sql)
Third, query data from the Sales table to display its contents:
SELECT * FROM Sales;
Code language: SQL (Structured Query Language) (sql)
Finally, create a table that stores the changes in the quantity column from the sales table:
DROP TABLE IF EXISTS SalesChanges;id INT AUTO_INCREMENT PRIMARY KEY,
salesId INT,
beforeQuantity INT,
afterQuantity INT,
changedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
Code language: SQL (Structured Query Language) (sql)
Creating AFTER UPDATE trigger example
The following statement creates an AFTER UPDATE trigger on the sales table:
DELIMITER $$AFTER UPDATE
ON sales FOR EACH ROW
BEGIN
IF OLD.quantity <> new.quantity THEN
INSERT INTO SalesChanges(salesId,beforeQuantity, afterQuantity)
VALUES(old.id, old.quantity, new.quantity);
END IF;
END$$
Code language: SQL (Structured Query Language) (sql)
This after_sales_update trigger is automatically fired before an update event occurs for each row in the sales table.
If you update the value in the quantity column to a new value the trigger insert a new row to log the changes in the SalesChanges table.
Let’s examine the trigger in detail:
First, the name of the trigger is after_sales_update specified in the CREATE TRIGGER clause:
CREATE TRIGGER after_sales_update
Code language: SQL (Structured Query Language) (sql)
Second, the triggering event is:
AFTER UPDATE
Code language: SQL (Structured Query Language) (sql)
Third, the table that the trigger associated with is sales:
ON Sales FOR EACH ROW
Code language: SQL (Structured Query Language) (sql)
Finally, use the IF-THEN 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 table:
IF OLD.quantity <> new.quantity THEN
INSERT INTO SalesChanges(salesId,beforeQuantity, afterQuantity)
VALUES(old.id, old.quantity, new.quantity);
END IF;
Code language: SQL (Structured Query Language) (sql)
Testing the MySQL AFTER UPDATE trigger
First, update the quantity of the row with id 1 to 350:
UPDATE Sales
SET quantity = 350
WHERE id = 1;
Code language: SQL (Structured Query Language) (sql)
The after_sales_update was invoked automatically.
Second, query data from the SalesChanges table:
SELECT * FROM SalesChanges;
Code language: SQL (Structured Query Language) (sql)
Third, increase the sales quantity of all rows to 10%:
UPDATE Sales
SET quantity = CAST(quantity * 1.1 AS UNSIGNED);
Code language: SQL (Structured Query Language) (sql)
Fourth, query data from the SalesChanges table:
SELECT * FROM SalesChanges;
Code language: SQL (Structured Query Language) (sql)
The trigger fired three times because of the updates of the three rows.
In this tutorial, you have learned how to create a MySQL AFTER UPDATE trigger to validate data before it is updated to a table.