MySQL建立多個觸發器

在本教學中，您將學習如何為MySQL中相同的事件和動作時間建立多個觸發器。

本教學與MySQL5.7.2+版本相關。如果您有一個較舊版本的MySQL，本教學中的語句將無法正常工作。

在MySQL5.7.2+版本之前，您只能為表中的事件建立一個觸發器，例如，只能為BEFORE UPDATE或AFTER UPDATE事件建立一個觸發器。 MySQL 5.7.2+版本解決了這樣限制，並允許您為表中的相同事件和動作時間建立多個觸發器。當事件發生時，觸發器將依次啟用。

參考建立第一個觸發器中的語法。如果表中有相同事件有多個觸發器，MySQL將按照建立的順序呼叫觸發器。要更改觸發器的順序，需要在FOR EACH ROW子句之後指定FOLLOWS或PRECEDES。如下說明 -

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多重觸發器範例

我們來看如何一個在表中的同一個事件和動作上，建立多個觸發器的例子。

下面將使用範例資料庫(yiibaidb)中的products表進行演示。假設，每當更改產品的價格(MSRP列)時，要將舊的價格記錄在一個名為price_logs的表中。

首先，使用CREATE TABLE語句建立一個新的price_logs表，如下所示：

USE yiibaidb;
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事件發生時，建立一個新的觸發器。觸發器名稱為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 | 2017-08-03 02:46:42 |
+----+--------------+-------+---------------------+
1 row in set

可以看到結果中，它按我們預期那樣工作了。

假設不僅要看到舊的價格，改變的時候，還要記錄是誰修改了它。我們可以向price_logs表新增其他列。但是，為了實現多個觸發器的演示，我們將建立一個新錶來儲存進行更改的使用者的資料。這個新錶的名稱為user_change_logs，結構如下：

USE yiibaidb;
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;

上面查詢語句執行後，得到以下結果 -

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

SELECT * FROM user_change_logs;

上面查詢語句執行後，得到以下結果 -

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

如上所見，兩個觸發器按照預期的順序啟用執行相關操作了。

觸發器順序

如果使用SHOW TRIGGERS語句，則不會在表中看到觸發啟用同一事件和操作的順序。

SHOW TRIGGERS FROM yiibaidb;

要查詢此資訊，需要如下查詢information_schema資料庫的triggers表中的action_order列，如下查詢語句 -

SELECT 
    trigger_name, action_order
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'yiibaidb'
ORDER BY event_object_table , 
         action_timing , 
         event_manipulation;

上面查詢語句執行後，得到以下結果 -

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

在本教學中，我們向您展示了如何在MySQL的表中為同一事件建立多個觸發器。