Create Multiple Triggers

Created with Sketch.
MySQL Tutorial

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

Code language: SQL (Structured Query Language) (sql)

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

Code language: SQL (Structured Query Language) (sql)

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(productCode,price)
VALUES(old.productCode,old.msrp);
END IF;
END$$

DELIMITER ;

Code language: SQL (Structured Query Language) (sql)

Third, check the price of the product S12_1099:

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

Code language: SQL (Structured Query Language) (sql)

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

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

Code language: SQL (Structured Query Language) (sql)

Fourth, query data from the PriceLogs table:

SELECT * FROM PriceLogs;

Code language: SQL (Structured Query Language) (sql)

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

Code language: SQL (Structured Query Language) (sql)

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 ;

Code language: HTML, XML (xml)

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

Code language: SQL (Structured Query Language) (sql)

Eighth, query data from both PriceLogs and UserChangeLogs tables:

SELECT * FROM PriceLogs;

Code language: SQL (Structured Query Language) (sql)

SELECT * FROM UserChangeLogs;

Code language: SQL (Structured Query Language) (sql)

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

Code language: SQL (Structured Query Language) (sql)

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;

Code language: SQL (Structured Query Language) (sql)

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

0

Leave a Reply

Your email address will not be published. Required fields are marked *

©  2024 python tutorials.  python-tutorials.in