1. 觸發(fā)器概述

MySQL從 5.0.2 版本開(kāi)始支持觸發(fā)器。

MySQL的觸發(fā)器和存儲(chǔ)過(guò)程一樣，都是嵌入到MySQL服務(wù)器的一段程序。

觸發(fā)器是由 事件來(lái)觸發(fā) 某個(gè)操作，這些事件包括 INSERT 、 UPDATE 、 DELETE 事件。

所謂事件就是指用戶的動(dòng)作或者觸發(fā)某項(xiàng)行為。

如果定義了觸發(fā)程序，當(dāng)數(shù)據(jù)庫(kù)執(zhí)行這些語(yǔ)句時(shí)候，就相當(dāng)于事件發(fā)生了，就會(huì) 自動(dòng) 激發(fā)觸發(fā)器執(zhí)行相應(yīng)的操作。

當(dāng)對(duì)數(shù)據(jù)表中的數(shù)據(jù)執(zhí)行插入、更新和刪除操作，需要自動(dòng)執(zhí)行一些數(shù)據(jù)庫(kù)邏輯時(shí)，可以使用觸發(fā)器來(lái)實(shí)現(xiàn)。

2. 觸發(fā)器的創(chuàng)建

2.1 創(chuàng)建觸發(fā)器語(yǔ)法

創(chuàng)建觸發(fā)器的語(yǔ)法結(jié)構(gòu)是：

CREATE TRIGGER 觸發(fā)器名稱
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
觸發(fā)器執(zhí)行的語(yǔ)句塊;

說(shuō)明：

• 表名 ：表示觸發(fā)器監(jiān)控的對(duì)象。
• BEFORE|AFTER ：表示觸發(fā)的時(shí)間。BEFORE 表示在事件之前觸發(fā)；AFTER 表示在事件之后觸發(fā)。
• INSERT|UPDATE|DELETE ：表示觸發(fā)的事件。
  • INSERT 表示插入記錄時(shí)觸發(fā)；
  • UPDATE 表示更新記錄時(shí)觸發(fā)；
  • DELETE 表示刪除記錄時(shí)觸發(fā)。
• 觸發(fā)器執(zhí)行的語(yǔ)句塊 ：可以是單條SQL語(yǔ)句，也可以是由BEGIN…END結(jié)構(gòu)組成的復(fù)合語(yǔ)句塊。

2.2 代碼舉例

舉例1：

1、創(chuàng)建數(shù)據(jù)表：

CREATE TABLE test_trigger (
	id INT PRIMARY KEY AUTO_INCREMENT,
	t_note VARCHAR(30)
);
CREATE TABLE test_trigger_log (
	id INT PRIMARY KEY AUTO_INCREMENT,
	t_log VARCHAR(30)
);

2、創(chuàng)建觸發(fā)器：創(chuàng)建名稱為before_insert的觸發(fā)器，向test_trigger數(shù)據(jù)表插入數(shù)據(jù)之前，向test_trigger_log數(shù)據(jù)表中插入before_insert的日志信息。

DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
	INSERT INTO test_trigger_log (t_log)
	VALUES('before_insert');
END //
DELIMITER ;

3、向test_trigger數(shù)據(jù)表中插入數(shù)據(jù)

INSERT INTO test_trigger (t_note) VALUES ('測(cè)試 BEFORE INSERT 觸發(fā)器');

4、查看test_trigger_log數(shù)據(jù)表中的數(shù)據(jù)

mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log		 |		
+----+---------------+
| 1  | before_insert |
+----+---------------+
1 row in set (0.00 sec)

舉例2：

1.創(chuàng)建名稱為after_insert的觸發(fā)器，向test_trigger數(shù)據(jù)表插入數(shù)據(jù)之后，向test_trigger_log數(shù)據(jù)表中插入after_insert的日志信息。

DELIMITER //
CREATE TRIGGER after_insert
AFTER INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log (t_log)
VALUES('after_insert');
END //
DELIMITER ;

2.向test_trigger數(shù)據(jù)表中插入數(shù)據(jù)。

INSERT INTO test_trigger (t_note) VALUES ('測(cè)試 AFTER INSERT 觸發(fā)器');

3.查看test_trigger_log數(shù)據(jù)表中的數(shù)據(jù)

mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log 		 |
+----+---------------+
| 1  | before_insert |
| 2  | before_insert |
| 3  | after_insert  |
+----+---------------+
3 rows in set (0.00 sec)

舉例3：

定義觸發(fā)器“salary_check_trigger”，基于員工表“employees”的INSERT事件，在INSERT之前檢查將要添加的新員工薪資是否大于他領(lǐng)導(dǎo)的薪資，如果大于領(lǐng)導(dǎo)薪資，則報(bào)sqlstate_value為’HY000’的錯(cuò)誤，從而使得添加失敗。

DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
	DECLARE mgrsalary DOUBLE;
	SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
	IF NEW.salary > mgrsalary 
		THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪資高于領(lǐng)導(dǎo)薪資錯(cuò)誤';
	END IF;
END //
DELIMITER ;

上面觸發(fā)器聲明過(guò)程中的NEW關(guān)鍵字代表INSERT添加語(yǔ)句的新記錄。

3. 查看、刪除觸發(fā)器

3.1 查看觸發(fā)器

查看觸發(fā)器是查看數(shù)據(jù)庫(kù)中已經(jīng)存在的觸發(fā)器的定義、狀態(tài)和語(yǔ)法信息等。

方式1：查看當(dāng)前數(shù)據(jù)庫(kù)的所有觸發(fā)器的定義

SHOW TRIGGERS\G

方式2：查看當(dāng)前數(shù)據(jù)庫(kù)中某個(gè)觸發(fā)器的定義

SHOW CREATE TRIGGER 觸發(fā)器名

方式3：從系統(tǒng)庫(kù)information_schema的TRIGGERS表中查詢“salary_check_trigger”觸發(fā)器的信息。

SELECT * FROM information_schema.TRIGGERS;

3.2 刪除觸發(fā)器

觸發(fā)器也是數(shù)據(jù)庫(kù)對(duì)象，刪除觸發(fā)器也用DROP語(yǔ)句，語(yǔ)法格式如下：

DROP TRIGGER IF EXISTS 觸發(fā)器名稱;

4. 觸發(fā)器的優(yōu)缺點(diǎn)

4.1 優(yōu)點(diǎn)

觸發(fā)器可以確保數(shù)據(jù)的完整性

假設(shè)我們用 進(jìn)貨單頭表 （demo.importhead）來(lái)保存進(jìn)貨單的總體信息，包括進(jìn)貨單編號(hào)、供貨商編號(hào)、倉(cāng)庫(kù)編號(hào)、總計(jì)進(jìn)貨數(shù)量、總計(jì)進(jìn)貨金額和驗(yàn)收日期。

用 進(jìn)貨單明細(xì)表  （demo.importdetails）來(lái)保存進(jìn)貨商品的明細(xì)，包括進(jìn)貨單編號(hào)、商品編號(hào)、進(jìn)貨數(shù)量、進(jìn)貨價(jià)格和進(jìn)貨金額。

每當(dāng)我們錄入、刪除和修改一條進(jìn)貨單明細(xì)數(shù)據(jù)的時(shí)候，進(jìn)貨單明細(xì)表里的數(shù)據(jù)就會(huì)發(fā)生變動(dòng)。這個(gè)時(shí)候，在進(jìn)貨單頭表中的總計(jì)數(shù)量和總計(jì)金額就必須重新計(jì)算，否則，進(jìn)貨單頭表中的總計(jì)數(shù)量和總計(jì)金額就不等于進(jìn)貨單明細(xì)表中數(shù)量合計(jì)和金額合計(jì)了，這就是數(shù)據(jù)不一致。

為了解決這個(gè)問(wèn)題，我們就可以使用觸發(fā)器，規(guī)定每當(dāng)進(jìn)貨單明細(xì)表有數(shù)據(jù)插入、修改和刪除的操作時(shí)，自動(dòng)觸發(fā) 2 步操作：

1. 重新計(jì)算進(jìn)貨單明細(xì)表中的數(shù)量合計(jì)和金額合計(jì)；
2. 用第一步中計(jì)算出來(lái)的值更新進(jìn)貨單頭表中的合計(jì)數(shù)量與合計(jì)金額。

這樣一來(lái)，進(jìn)貨單頭表中的合計(jì)數(shù)量與合計(jì)金額的值，就始終與進(jìn)貨單明細(xì)表中計(jì)算出來(lái)的合計(jì)數(shù)量與合計(jì)金額的值相同，數(shù)據(jù)就是一致的，不會(huì)互相矛盾。

觸發(fā)器可以幫助我們記錄操作日志

利用觸發(fā)器，可以具體記錄什么時(shí)間發(fā)生了什么。比如，記錄修改會(huì)員儲(chǔ)值金額的觸發(fā)器，就是一個(gè)很好的例子。這對(duì)我們還原操作執(zhí)行時(shí)的具體場(chǎng)景，更好地定位問(wèn)題原因很有幫助。

觸發(fā)器還可以用在操作數(shù)據(jù)前，對(duì)數(shù)據(jù)進(jìn)行合法性檢查

比如，超市進(jìn)貨的時(shí)候，需要庫(kù)管錄入進(jìn)貨價(jià)格。但是，人為操作很容易犯錯(cuò)誤，比如說(shuō)在錄入數(shù)量的時(shí)候，把條形碼掃進(jìn)去了；錄入金額的時(shí)候，看串了行，錄入的價(jià)格遠(yuǎn)超售價(jià)，導(dǎo)致賬面上的巨虧……這些都可以通過(guò)觸發(fā)器，在實(shí)際插入或者更新操作之前，對(duì)相應(yīng)的數(shù)據(jù)進(jìn)行檢查，及時(shí)提示錯(cuò)誤，防止錯(cuò)誤數(shù)據(jù)進(jìn)入系統(tǒng)。

4.2 缺點(diǎn)

觸發(fā)器最大的一個(gè)問(wèn)題就是可讀性差

因?yàn)橛|發(fā)器存儲(chǔ)在數(shù)據(jù)庫(kù)中，并且由事件驅(qū)動(dòng)，這就意味著觸發(fā)器有可能 不受應(yīng)用層的控制 。這對(duì)系統(tǒng) 維護(hù)是非常有挑戰(zhàn)的。

比如，創(chuàng)建觸發(fā)器用于修改會(huì)員儲(chǔ)值操作。如果觸發(fā)器中的操作出了問(wèn)題，會(huì)導(dǎo)致會(huì)員儲(chǔ)值金額更新失敗。我用下面的代碼演示一下：

mysql> update demo.membermaster set memberdeposit=20 where memberid = 2;
ERROR 1054 (42S22): Unknown column 'aa' in 'field list'

結(jié)果顯示，系統(tǒng)提示錯(cuò)誤，字段“aa”不存在。

這是因?yàn)?，觸發(fā)器中的數(shù)據(jù)插入操作多了一個(gè)字段，系統(tǒng)提示錯(cuò)誤。可是，如果你不了解這個(gè)觸發(fā)器，很可能會(huì)認(rèn)為是更新語(yǔ)句本身的問(wèn)題，或者是會(huì)員信息表的結(jié)構(gòu)出了問(wèn)題。說(shuō)不定你還會(huì)給會(huì)員信息表添加一個(gè)叫“aa”的字段，試圖解決這個(gè)問(wèn)題，結(jié)果只能是白費(fèi)力。

相關(guān)數(shù)據(jù)的變更，可能會(huì)導(dǎo)致觸發(fā)器出錯(cuò)

特別是數(shù)據(jù)表結(jié)構(gòu)的變更，都可能會(huì)導(dǎo)致觸發(fā)器出錯(cuò)，進(jìn)而影響數(shù)據(jù)操作的正常運(yùn)行。這些都會(huì)由于觸發(fā)器本身的隱蔽性，影響到應(yīng)用中錯(cuò)誤原因排查的效率。

4.3 注意點(diǎn)

聯(lián)動(dòng)（非直接操作）修改或刪除，并不會(huì)激活觸發(fā)器。

注意，如果在子表中定義了外鍵約束，并且外鍵指定了 ON UPDATE/DELETE CASCADE/SET NULL 子句，此時(shí)修改父表被引用的鍵值或刪除父表被引用的記錄行時(shí)，也會(huì)引起子表的修改和刪除操作，此時(shí)基于子表的 UPDATE 和 DELETE 語(yǔ)句定義的觸發(fā)器并不會(huì)被激活。

例如：基于子表員工表（t_employee）的DELETE語(yǔ)句定義了觸發(fā)器t1，而子表的部門(mén)編號(hào)（did）字段定義了外鍵約束引用了父表部門(mén)表（t_department）的主鍵列部門(mén)編號(hào)（did），并且該外鍵加了“ ONDELETE SET NULL ”子句，那么如果此時(shí)刪除父表部門(mén)表（t_department）在子表員工表（t_employee）有匹配記錄的部門(mén)記錄時(shí)，會(huì)引起子表員工表（t_employee）匹配記錄的部門(mén)編號(hào)（did）修改為NULL，但是此時(shí)不會(huì)激活觸發(fā)器t1。只有直接對(duì)子表員工表（t_employee）執(zhí)行DELETE語(yǔ)句時(shí)才會(huì)激活觸發(fā)器t1。

到此這篇關(guān)于MySQL中的觸發(fā)器trigger用法解析的文章就介紹到這了,更多相關(guān)MySQL觸發(fā)器trigger內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論