快捷導(dǎo)航

mysql觸發(fā)器之創(chuàng)建多個(gè)觸發(fā)器操作實(shí)例分析

更新時(shí)間：2019年12月17日 08:56:52 作者：luyaran

這篇文章主要介紹了mysql觸發(fā)器之創(chuàng)建多個(gè)觸發(fā)器操作,結(jié)合實(shí)例形式分析了mysql創(chuàng)建及使用多個(gè)觸發(fā)器的相關(guān)操作技巧,需要的朋友可以參考下

本文實(shí)例講述了mysql觸發(fā)器之創(chuàng)建多個(gè)觸發(fā)器操作。分享給大家供大家參考，具體如下：

這次記錄的內(nèi)容mysql 版本必須得是5.7.2+的哈，之前的會(huì)不好使的。廢話不多說(shuō)，咱們開(kāi)始正文哈。

在mysql 5.7.2+版本之前，我們只能為表中的事件創(chuàng)建一個(gè)觸發(fā)器，例如，只能為BEFORE UPDATE或AFTER UPDATE事件創(chuàng)建一個(gè)觸發(fā)器。 mysql 5.7.2+版本解決了這樣限制，并允許我們?yōu)楸碇械南嗤录蛣?dòng)作時(shí)間創(chuàng)建多個(gè)觸發(fā)器。當(dāng)事件發(fā)生時(shí)，觸發(fā)器將依次激活。我們來(lái)參考創(chuàng)建第一個(gè)觸發(fā)器中的語(yǔ)法。如果表中有相同事件有多個(gè)觸發(fā)器，mysql 將按照創(chuàng)建的順序調(diào)用觸發(fā)器。要更改觸發(fā)器的順序，需要在FOR EACH ROW子句之后指定FOLLOWS或PRECEDES。我們來(lái)看下這兩個(gè)詞的說(shuō)明：

FOLLOWS選項(xiàng)允許新觸發(fā)器在現(xiàn)有觸發(fā)器之后激活。
PRECEDES選項(xiàng)允許新觸發(fā)器在現(xiàn)有觸發(fā)器之前激活。

完事來(lái)看下使用顯式順序創(chuàng)建新的附加觸發(fā)器的語(yǔ)法：

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

然后，我們來(lái)看一個(gè)在表中的同一個(gè)事件和動(dòng)作上，創(chuàng)建多個(gè)觸發(fā)器的例子。我們來(lái)基于products表進(jìn)行演示，首先來(lái)創(chuàng)建一個(gè)新的price_logs表，完事呢，每當(dāng)更改產(chǎn)品的價(jià)格(MSRP列)時(shí)，要將舊的價(jià)格記錄在一個(gè)名為price_logs的表中，先來(lái)看想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
);

完事，當(dāng)表的BEFORE UPDATE事件發(fā)生時(shí)，創(chuàng)建一個(gè)新的觸發(fā)器。觸發(fā)器名稱為before_products_update，具體實(shí)現(xiàn)如下所示：

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 ;

然后，當(dāng)我們更改產(chǎn)品的價(jià)格，并使用以下update語(yǔ)句，最后查詢price_logs表：

UPDATE products
SET msrp = 95.1
WHERE productCode = 'S10_1678';
-- 查詢結(jié)果價(jià)格記錄
SELECT * FROM price_logs;

上面查詢語(yǔ)句執(zhí)行后，得到以下結(jié)果：

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

可以看到結(jié)果中，它按我們預(yù)期那樣工作了。

完事我們?cè)賮?lái)假設(shè)不僅要看到舊的價(jià)格，改變的時(shí)候，還要記錄是誰(shuí)修改了它。要實(shí)現(xiàn)這個(gè)，我們可以向price_logs表添加其他列，但是，為了實(shí)現(xiàn)多個(gè)觸發(fā)器的演示，我們將創(chuàng)建一個(gè)新表來(lái)存儲(chǔ)進(jìn)行更改的用戶的數(shù)據(jù)。這個(gè)新表的名稱為user_change_logs，結(jié)構(gòu)如下：

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

現(xiàn)在，我們創(chuàng)建一個(gè)在products表上的BEFORE UPDATE事件上激活的第二個(gè)觸發(fā)器。此觸發(fā)器將更改的用戶信息更新到user_change_logs表。它在before_products_update觸發(fā)后被激活：

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 ;

然后我們來(lái)使用update語(yǔ)句更新指定產(chǎn)品的價(jià)格：

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

再來(lái)分別從price_logs和user_change_logs表查詢數(shù)據(jù)：

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

如上所見(jiàn)，兩個(gè)觸發(fā)器按照預(yù)期的順序激活執(zhí)行相關(guān)操作了。完事我們來(lái)在information_schema數(shù)據(jù)庫(kù)的triggers表中的action_order列，看下觸發(fā)激活同一事件和操作的順序：

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

好啦，本次記錄就到這里了。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》、《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。

您可能感興趣的文章: