Creation of mysql Trigger Analysis of Multiple Trigger Operation Examples

This article example describes the creation of multiple triggers for mysql triggers. Share it for your reference, as follows:

The content of this record mysql version must be 5.7. 2 + ha, the previous will not work well. Needless to say, let's start the text.

Prior to mysql 5.7. 2 +, we could only create one trigger for events in the table, for example, only one trigger for BEFORE UPDATE or AFTER UPDATE events. mysql 5.7. 2 + addresses this limitation and allows us to create multiple triggers for the same event and action time in the table. When an event occurs, triggers are activated in turn. Let's refer to the syntax in creating the first trigger. If there are multiple triggers for the same event in the table, mysql calls the triggers in the order in which they were created. To change the order of triggers, you need to specify FOLLOWS or PRECEDES after the FOR EACH ROW clause. Let's take a look at the description of these two words:

The FOLLOWS option allows a new trigger to be activated after an existing trigger. The PRECEDES option allows new triggers to be activated before existing triggers.

Let's take a look at the syntax for creating new additional triggers in 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 ;

Then, 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. After that, whenever the price of the product is changed (MSRP column), record the old price in a table named price_logs. First, think about 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
);

Once done, when the BEFORE UPDATE event of the table occurs, a new trigger is created. The trigger is named before_products_update and is implemented 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 the product and use the following update statement, we finally query the price_logs table:

UPDATE products
SET msrp = 95.1
WHERE productCode = 'S10_1678';
--  Query Result Price Record 
SELECT * FROM price_logs;

After the above query statement is executed, 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

You can see that in the results, it works as we expected.

Let's assume that we should not only see the old price, but also record who changed it when it changed. To do this, we can add additional columns to the price_logs table, but for the demonstration of multiple triggers, we will create a new table to store the data of the user who made the change. The new table is named user_change_logs and has the following structure:

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 is triggered:

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

Then query the 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 seen above, the two triggers are activated to perform related operations in the expected order. Let's take a look at the sequence in which the same 1 events and actions are triggered and activated in the column action_order in the triggers table of the information_schema database:

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

Ok, that's all for this record.

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.