Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of creating multiple triggers for mysql triggers. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

The content of this record mysql version must be 5.7.2 + ha, the previous one will not work well. Don't talk too much nonsense, let's start with 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 version 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, the trigger is 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 a new trigger to be activated before an existing trigger.

Let's take a look at the syntax for creating new additional triggers using explicit order:

DELIMITER $$CREATE TRIGGER trigger_ name [be | AFTER] [INSERT | UPDATE | DELETE] ON table_nameFOR EACH ROW [FOLLOWS | PRECEDES] existing_trigger_nameBEGIN... 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, let's create a new price_logs table. When finished, whenever you change the price of a product (MSRP column), record the old price in a table called 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)

When finished, create a new trigger when the BEFORE UPDATE event for the table occurs. The name of the trigger is before_products_update and the 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 the product and use the following update statement, we finally query the price_logs table:

UPDATE productsSET msrp = 95.1WHERE productCode = 'S10, 1678, price record-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 | S1011678 | | 2017 | 08-03 02:46:42 | +-+ 1 row in set |

As you can see in the result, it works as we expected.

When we're done, let's assume that we not only have to see the old price, but also record who modified it when it changes. To do this, we can add additional columns to the price_logs table, but for a demonstration of multiple triggers, we will create a new table to store the data of the user who made the change. The name of the new table is user_change_logs and the 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 let's 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 the before_products_update is triggered:

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

Then let's use the update statement to update the price of the specified product:

UPDATE productsSET msrp = 95.3WHERE 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 | S1011678 | | 95.7 | 2017-08-03 02:46:42 | 2 | S101678 | 95.1 | 2017-08-03 02:47:21 | +-+ 2 rows in setmysql > SELECT * FROM user_change_logs | +-+ | id | product_code | updated_at | updated_by | +-+- -+ | 1 | S101678 | 2017-08-03 02:47:21 | root@localhost | +-+ 1 row in set

As you can see, the two triggers activate and perform the relevant operations in the expected order. When we're done, 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_orderFROM information_schema.triggersWHERE 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

All right, that's all for this record.

This is the end of this article on "sample analysis of creating multiple triggers for mysql triggers". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.