Create Multiple Triggers

 Create Multiple Triggers

Summary: in this tutorial, you will learn how to create multiple triggers for a table that have the same event and action time.

This tutorial is relevant to MySQL version 5.7.2+. If you have an older version of MySQL, the statements in the tutorial will not work.

Before MySQL version 5.7.2, you can only create one trigger for an event in a table e.g., you can only create one trigger for the BEFORE UPDATE or AFTER UPDATE event. MySQL 5.7.2+ lifted this limitation and allowed you to create multiple triggers for a given table that have the same event and action time. These triggers will activate sequentially when an event occurs.

Here is the syntax for defining a trigger that will activate before or after an existing trigger in response to the same event and action time:

DELIMITER $$

CREATE TRIGGER trigger_name
{BEFORE|AFTER}{INSERT|UPDATE|DELETE} 
ON table_name FOR EACH ROW 
{FOLLOWS|PRECEDES} existing_trigger_name
BEGIN
    -- statements
END$$

DELIMITER ;

In this syntax, the FOLLOWS or PRECEDES specifies whether the new trigger should be invoked before or after an existing trigger.

• The FOLLOWS  allows the new trigger to activate after an existing trigger.
• The PRECEDES  allows the new trigger to activate before an existing trigger.

MySQL multiple triggers example

We will use the products the table in the sample database for the demonstration.

Suppose that you want to change the price of a product (column MSRP ) and log the old price in a separate table named PriceLogs .

First, create a new price_logs table using the following CREATE TABLE statement:

CREATE TABLE PriceLogs (
    id INT AUTO_INCREMENT,
    productCode VARCHAR(15) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    updated_at TIMESTAMP NOT NULL 
			DEFAULT CURRENT_TIMESTAMP 
            ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (productCode)
        REFERENCES products (productCode)
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

Second, create a new trigger that activates when the BEFORE UPDATE event of the products table occurs:

DELIMITER $$

CREATE TRIGGER before_products_update 
   BEFORE UPDATE ON products 
   FOR EACH ROW 
BEGIN
     IF OLD.msrp <> NEW.msrp THEN
         INSERT INTO PriceLOgs(product_code,price)
         VALUES(old.productCode,old.msrp);
     END IF;
END$$

DELIMITER ;

Third, check the price of the product S12_1099:

SELECT 
    productCode, 
    msrp 
FROM 
    products
WHERE 
    productCode = 'S12_1099';

Third, change the price of a product using the following UPDATE statement:

UPDATE products
SET msrp = 200
WHERE productCode = 'S12_1099';

Fourth, query data from the PriceLogs table:

SELECT * FROM PriceLogs;

It works as expected.

Suppose that you want to log the user who changed the price. To achieve this, you can add an additional column to the PriceLogs table.

However, for the purpose of multiple triggers demonstration, we will create a new separate table to store the data of users who made the changes.

Fifth, create the UserChangeLogs table:

CREATE TABLE UserChangeLogs (
    id INT AUTO_INCREMENT,
    productCode VARCHAR(15) DEFAULT NULL,
    updatedAt TIMESTAMP NOT NULL 
	DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP,
    updatedBy VARCHAR(30) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (productCode)
        REFERENCES products (productCode)
        ON DELETE CASCADE 
        ON UPDATE CASCADE
);

Sixth, create a BEFORE UPDATE trigger for the products table. This trigger activates after the before_products_update trigger.

DELIMITER $$

CREATE TRIGGER before_products_update_log_user
   BEFORE UPDATE ON products 
   FOR EACH ROW 
   FOLLOWS before_products_update
BEGIN
    IF OLD.msrp <> NEW.msrp THEN
	INSERT INTO 
            UserChangeLogs(productCode,updatedBy)
        VALUES
            (OLD.productCode,USER());
    END IF;
END$$

DELIMITER ;

Let’s do a quick test.

Seventh, update the price of a product using the following UPDATE statement:

UPDATE 
    products
SET 
    msrp = 220
WHERE 
    productCode = 'S12_1099';

Eighth, query data from both PriceLogs and UserChangeLogs tables:

SELECT * FROM PriceLogs;

SELECT * FROM UserChangeLogs;

As you can see, both triggers were activated in the sequence as expected.

Information on trigger order

If you use the SHOW TRIGGERS statement to show the triggers, you will not see the order that triggers activate for the same event and action time.

SHOW TRIGGERS 
FROM classicmodels
WHERE `table` = 'products';

To find this information, you need to query the action_order column in the triggers table of the information_schema database as follows:

SELECT 
    trigger_name, 
    action_order
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'classicmodels'
ORDER BY 
    event_object_table , 
    action_timing , 
    event_manipulation;

In this tutorial, you have learned how to create multiple triggers for a table that has the same event and action time.