MySQL triggers: creating multiple triggers operation example analysis

This article uses an example to describe the creation of multiple trigger operations in MySQL. Share with you for your reference, the details are as follows:

The MySQL version recorded this time must be 5.7.2+, and earlier ones will not work. Without further ado, let’s get started.

Prior to MySQL 5.7.2+, we could only create a trigger for an event in a table, for example, we could only create a trigger for a BEFORE UPDATE or AFTER UPDATE event. MySQL 5.7.2+ versions resolve this limitation and allow us to create multiple triggers for the same event and action time in a table. When events occur, triggers are activated in sequence. Let's refer to the syntax in Creating the First Trigger. If there are multiple triggers for the same event on a table, MySQL will call the triggers in the order they are created. To change the order of the triggers, specify FOLLOWS or PRECEDES after the FOR EACH ROW clause. Let’s look at the explanation of these two words:

• The FOLLOWS option allows new triggers to activate after existing triggers.
• The PRECEDES option allows new triggers to activate before existing triggers.

Finally, let's look at the syntax for creating a new additional trigger using an explicit order:

DELIMITER $$
CREATE TRIGGER trigger_name
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON table_name
FOR EACH ROW [FOLLOWS|PRECEDES] existing_trigger_name
BEGIN
…
END$$
DELIMITER ;

Next, let's look at an example of creating multiple triggers on the same event and action in a table. Let's demonstrate based on the products table. First, create a new price_logs table. When the price of a product (MSRP column) is changed, the old price should be recorded in a table called price_logs. Let's take a look at the SQL:

CREATE TABLE price_logs (
 id INT(11) NOT NULL AUTO_INCREMENT,
 product_code VARCHAR(15) NOT NULL,
 price DOUBLE NOT NULL,
 updated_at TIMESTAMP NOT NULL DEFAULT 
       CURRENT_TIMESTAMP 
       ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (id),
 KEY product_code (product_code),
 CONSTRAINT price_logs_ibfk_1 FOREIGN KEY (product_code) 
 REFERENCES products (productCode) 
 ON DELETE CASCADE 
 ON UPDATE CASCADE
);

That's it. Create a new trigger when the BEFORE UPDATE event of the table occurs. The trigger name is before_products_update, and the specific implementation is as follows:

DELIMITER $$
CREATE TRIGGER before_products_update 
  BEFORE UPDATE ON products 
  FOR EACH ROW 
BEGIN
   INSERT INTO price_logs(product_code,price)
   VALUES(old.productCode,old.msrp);
END$$
DELIMITER ;

Then, when we change the price of a product, we use the following update statement and finally query the price_logs table:

UPDATE products
SET msrp = 95.1
WHERE productCode = 'S10_1678';
--Query result price records SELECT * FROM price_logs;

After executing the above query statement, the following results are obtained:

+----+--------------+-------+---------------------+
| id | product_code | price | updated_at |
+----+--------------+-------+---------------------+
| 1 | S10_1678 | 95.7 | 2017-08-03 02:46:42 |
+----+--------------+-------+---------------------+
1 row in set

As you can see from the results, it works as we expected.

Now let's assume that we not only want to see the old price, but also record who modified it when it was changed. To achieve this, we could add additional columns to the price_logs table, but, for the sake of demonstrating multiple triggers, we will create a new table to store data about the user who made the changes. The name of this new table is user_change_logs and its structure is as follows:

CREATE TABLE user_change_logs (
 id int(11) NOT NULL AUTO_INCREMENT,
 product_code varchar(15) DEFAULT NULL,
 updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 
 ON UPDATE CURRENT_TIMESTAMP,
 updated_by varchar(30) NOT NULL,
 PRIMARY KEY (id),
 KEY product_code (product_code),
 CONSTRAINT user_change_logs_ibfk_1 FOREIGN KEY (product_code) 
 REFERENCES products (productCode) 
 ON DELETE CASCADE ON UPDATE CASCADE
);

Now, we create a second trigger that is activated on the BEFORE UPDATE event on the products table. This trigger updates the changed user information to the user_change_logs table. It is activated after before_products_update fires:

DELIMITER $$
CREATE TRIGGER before_products_update_2 
  BEFORE UPDATE ON products 
  FOR EACH ROW FOLLOWS before_products_update
BEGIN
  INSERT INTO user_change_logs(product_code,updated_by)
  VALUES(old.productCode,user());
END$$
DELIMITER ;

Then we use the update statement to update the price of the specified product:

UPDATE products
SET msrp = 95.3
WHERE productCode = 'S10_1678';

Next, query data from the price_logs and user_change_logs tables respectively:

mysql> SELECT * FROM price_logs;
+----+--------------+-------+---------------------+
| id | product_code | price | updated_at |
+----+--------------+-------+---------------------+
| 1 | S10_1678 | 95.7 | 2017-08-03 02:46:42 |
| 2 | S10_1678 | 95.1 | 2017-08-03 02:47:21 |
+----+--------------+-------+---------------------+
2 rows in set
mysql> SELECT * FROM user_change_logs;
+----+--------------+---------------------+----------------+
| id | product_code | updated_at | updated_by |
+----+--------------+---------------------+----------------+
| 1 | S10_1678 | 2017-08-03 02:47:21 | root@localhost |
+----+--------------+---------------------+----------------+
1 row in set

As you can see above, the two triggers are activated in the expected order to perform the related operations. Now let's look at the action_order column in the triggers table of the information_schema database to see the order in which the same event and action are triggered:

mysql> SELECT 
  trigger_name, action_order
FROM
  information_schema.triggers
WHERE
  trigger_schema = 'yiibaidb'
ORDER BY event_object_table , 
     action_timing , 
     event_manipulation;
+--------------------------+--------------+
| trigger_name | action_order |
+--------------------------+--------------+
| before_employee_update | 1 |
| before_products_update | 1 |
| before_products_update_2 | 2 |
+--------------------------+--------------+
3 rows in set

Okay, that’s all for this record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.