MySQL 创建多个触发器

---

简介：在本教程中，您将学习如何在MySQL中为相同的事件和操作时间创建多个触发器。

本教程与MySQL 5.7.2+版本相关。如果你有一个较旧版本的MySQL，教程中的语句将不起作用。

在MySQL 5.7.2版之前，您只能为表中的事件创建一个触发器，例如，您只能为BEFORE UPDATE或AFTER UPDATE事件创建一个触发器。MySQL 5.7.2+解除了这一限制，允许您为表中的相同事件和操作时间创建多个触发器。事件发生时，触发器将按顺序激活。 创建第一个触发器的语法保持不变。如果您在表中有相同事件的多个触发器，MySQL将按创建顺序调用触发器。要更改触发器的顺序，您需要指定FOLLOWS或PRECEDES在FOR EACH ROW子句之后。

•   FOLLOWS   选项允许在现有触发器之后激活新触发器。
•   PRECEDES  选项允许在现有触发器之前激活新触发器。

以下是使用显式顺序创建新的附加触发器的语法：

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

---

MySQL多重触发器示例

让我们看一个在表中为同一事件和动作时间创建多个触发器的示例，以便更好地理解。 我们将使用示例数据库中的products表进行演示。假设，每当我们更改产品（列）的价格时，我们都希望将旧价格记录在一个名为MSRPprice_logs的单独表中。 首先，我们使用CREATE TABLE语句创建一个price_logs新表，如下所示：

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

其次，我们创建一个新的触发器，BEFORE UPDATE在products表的事件发生时激活。触发器的名称是before_products_update：

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 ;

第三，我们使用以下UPDATE语句更改产品的价格并查询price_logs表：

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

SELECT * FROM price_logs;

+----+--------------+-------+---------------------+
| id | product_code | price | updated_at          |
+----+--------------+-------+---------------------+
|  1 | S10_1678     |  95.7 | 2019-08-23 17:38:28 |
+----+--------------+-------+---------------------+
1 row in set (0.00 sec)

它按预期工作。 假设我们不仅希望看到旧价格以及何时更改，还要更改它。我们可以在price_logs表中添加其他列。但是，为了进行多个触发器演示，我们将创建一个新表来存储进行更改的用户的数据。新表的名称user_change_logs具有以下结构：

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

现在，我们创建第二个触发器，激活products表的 BEFORE UPDATE事件。此触发器将user_change_logs使用进行更改的用户的数据更新表。它在before_products_update触发后被激活。

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 ;

我们来做快速测试吧。 首先，我们使用UPDATE语句更新产品价格，如下所示：

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

其次，我们从两个表price_logs和user_change_logs表中查询数据：

SELECT * FROM price_logs;

+----+--------------+-------+---------------------+
| id | product_code | price | updated_at          |
+----+--------------+-------+---------------------+
|  1 | S10_1678     |  95.7 | 2019-08-23 17:38:28 |
|  2 | S10_1678     |  95.1 | 2019-08-23 17:46:28 |
+----+--------------+-------+---------------------+
2 rows in set (0.00 sec)

SELECT * FROM user_change_logs;

+----+--------------+---------------------+----------------+
| id | product_code | updated_at          | updated_by     |
+----+--------------+---------------------+----------------+
|  1 | S10_1678     | 2019-08-23 17:46:28 | root@localhost |
+----+--------------+---------------------+----------------+

如您所见，两个触发器都按预期顺序激活。

---

触发器顺序查看

如果使用SHOW TRIGGERS语句，则不会在表中看到针对相同事件和操作时序触发激活的顺序。

SHOW TRIGGERS FROM mysqldemo;

要查找此信息，您需要查询information_schema数据库triggers表中的action_order列，如下所示：

SELECT 
    trigger_name, action_order
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'mysqldemo'
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 (0.01 sec)

在本教程中，我们向您展示了如何在MySQL的表中为同一事件创建多个触发器。

---