MySQL AFTER UPDATE Trigger

 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

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 $$

CREATE TRIGGER trigger_name
    AFTER UPDATE
    ON table_name FOR EACH ROW
BEGIN
    -- statements
END$$    

DELIMITER ;

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;

CREATE TABLE 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)
);

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);

Third, query data from the Sales table to display its contents:

SELECT * FROM Sales;

Finally, create a table that stores the changes in the quantity column from the sales table:

DROP TABLE IF EXISTS SalesChanges;

CREATE TABLE SalesChanges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salesId INT,
    beforeQuantity INT,
    afterQuantity INT,
    changedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Creating AFTER UPDATE trigger example

The following statement creates an AFTER UPDATE trigger on the sales table:

DELIMITER $$

CREATE TRIGGER after_sales_update
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$$

DELIMITER ;

This after_sales_update the 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

Second, the triggering event is:

AFTER UPDATE

Third, the table that the trigger is associated with is sales:

ON Sales FOR EACH ROW

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;

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;

The after_sales_update was invoked automatically.

Second, query data from the SalesChanges table:

SELECT * FROM SalesChanges;

Third, increase the sales quantity of all rows to 10%:

UPDATE Sales 
SET quantity = CAST(quantity * 1.1 AS UNSIGNED);

Fourth, query data from the SalesChanges table:

SELECT * FROM SalesChanges;

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.