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_bodyCode 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. The FOR EACH ROW instructs the trigger to fire each time a row is updated.

Finally, define 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 ;Code language: SQL (Structured Query Language) (sql)

In a AFTER UPDATE trigger, you can access OLD and NEW rows but cannot update them.

Note that the OLD represents the values of columns before the UPDATE whereas the NEW represents the values of columns after the UPDATE.

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

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

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 ;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 inserts 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_updateCode language: SQL (Structured Query Language) (sql)

Second, the triggering event is:

AFTER UPDATECode language: SQL (Structured Query Language) (sql)

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

ON Sales FOR EACH ROWCode 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.