觸發(fā)器在MySQL數(shù)據(jù)庫(kù)中的應(yīng)用

摘 要：觸發(fā)器能夠解決數(shù)據(jù)庫(kù)復(fù)雜的業(yè)務(wù)規(guī)則或要求，從而保證數(shù)據(jù)庫(kù)的完整性、正確性和一致性。本文介紹了觸發(fā)器的概念、用途、創(chuàng)建、使用等，旨在幫助數(shù)據(jù)庫(kù)編程人員正確使用觸發(fā)器。

關(guān)鍵詞：觸發(fā)器；trigger；MySQL

中圖分類(lèi)號(hào)：TP309

1 觸發(fā)器的概念

觸發(fā)器是一個(gè)被指定關(guān)聯(lián)到一個(gè)表的數(shù)據(jù)庫(kù)對(duì)象，當(dāng)對(duì)一個(gè)表的特定事件出現(xiàn)時(shí)，它將被激活。觸發(fā)器可以看作是特殊的存儲(chǔ)過(guò)程，不同的是，執(zhí)行存儲(chǔ)過(guò)程要使用call語(yǔ)句來(lái)調(diào)用，只要當(dāng)一個(gè)預(yù)定義的事件發(fā)生的時(shí)候，觸發(fā)主體動(dòng)作就會(huì)被MySQL自動(dòng)調(diào)用。具體而言，觸發(fā)器就是MySQL響應(yīng)insert、update和delete語(yǔ)句而自動(dòng)執(zhí)行的一條或一組MySQL語(yǔ)句。

2 觸發(fā)器的用途

（1）它可用于檢查插入到表中的值。例如：每當(dāng)增加一個(gè)學(xué)生到數(shù)據(jù)庫(kù)的學(xué)生基本信息表時(shí)，都檢查其電話號(hào)碼的格式是否正確。（2）它能對(duì)更新涉及的值進(jìn)行計(jì)算。例如：每當(dāng)客戶(hù)訂購(gòu)一個(gè)產(chǎn)品時(shí)，都從產(chǎn)品庫(kù)存表中將庫(kù)存量減去訂購(gòu)的數(shù)量。（3）用于創(chuàng)建審計(jì)跟蹤，也就是可使用觸發(fā)器把表的更改狀態(tài)以及之前和之后的狀態(tài)記錄到另外一張數(shù)據(jù)表中。正確的使用觸發(fā)器，這能保障數(shù)據(jù)庫(kù)中數(shù)據(jù)的完整性正確性，以及多個(gè)表之間數(shù)據(jù)的一致性。

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

語(yǔ)法格式：Create [DEFINER={user|CURRENT_USER}] TRIGGER t_name t_time t_event ON tbl_name FOR EACH ROW t_body

其說(shuō)明如下：DEFINER從句：指定當(dāng)觸發(fā)器被觸發(fā)時(shí)滿(mǎn)足事件執(zhí)行權(quán)限的安全上下文，為可選項(xiàng)。t_name：觸發(fā)器的名稱(chēng)，觸發(fā)器在當(dāng)前數(shù)據(jù)庫(kù)中必須具有唯一的名稱(chēng)。t_time：觸發(fā)器被觸發(fā)的時(shí)機(jī)，有before和after兩個(gè)選項(xiàng)。t_event：觸發(fā)事件，包括insert、update和delete，指定激活觸發(fā)器的語(yǔ)句種類(lèi)。tbl_name：標(biāo)識(shí)建立觸發(fā)器的表名。FOR EACH ROW：表示任何一條記錄上的操作滿(mǎn)足觸發(fā)事件都會(huì)觸發(fā)該觸發(fā)器。t_body：指定觸發(fā)器被觸發(fā)后執(zhí)行的語(yǔ)句，稱(chēng)為觸發(fā)器主體。當(dāng)觸發(fā)器主體有多條語(yǔ)句時(shí)，將要執(zhí)行的多條語(yǔ)句放begin和end語(yǔ)句之間，各語(yǔ)句之間要用分隔符“；”隔開(kāi)。

4 觸發(fā)器的查看

查看觸發(fā)器是指查看數(shù)據(jù)庫(kù)中已存在的觸發(fā)器的定義、狀態(tài)和語(yǔ)法信息等。查看當(dāng)前數(shù)據(jù)庫(kù)中的觸發(fā)器語(yǔ)句為：“show TRIGGERS”，但執(zhí)行該語(yǔ)句顯示的觸發(fā)器信息較為混亂，可以在show TRIGGERS命令后加“＼G”，這樣顯示的信息就相對(duì)更有條理。在MySQL數(shù)據(jù)庫(kù)中，所有的觸發(fā)器定義都存放在系統(tǒng)自帶的數(shù)據(jù)庫(kù)information_schema的triggers表中，所以可以通過(guò)語(yǔ)句“select * from information_schema.triggers where trigger_name=觸發(fā)器名稱(chēng)”查看滿(mǎn)足指定條件的觸發(fā)器，該方法更為實(shí)用。

5 觸發(fā)器的使用

觸發(fā)器是與表有關(guān)的命名數(shù)據(jù)庫(kù)對(duì)象，當(dāng)表上出現(xiàn)特定事件時(shí)，將激活該對(duì)象。根據(jù)觸發(fā)的事件，觸發(fā)器可分為insert、delete和update觸發(fā)器。

5.1 insert觸發(fā)器。insert觸發(fā)器可在insert語(yǔ)句執(zhí)行之前或之后執(zhí)行。需要注意以下幾點(diǎn)：在insert觸發(fā)器代碼內(nèi)，可引用一個(gè)名為new的虛擬表，來(lái)訪問(wèn)被插入的行。在before insert觸發(fā)器中，new中的值也可以被更新，即允許更改被插入的值（只要具有對(duì)應(yīng)的操作權(quán)限）。對(duì)于auto_increment列，new在insert執(zhí)行之前包含的是0值，在insert執(zhí)行之后將包含新的自動(dòng)生成值。

5.2 delete觸發(fā)器。delete觸發(fā)器可在delete語(yǔ)句執(zhí)行之前或之后執(zhí)行。在delete觸發(fā)器代碼內(nèi)，可以引用一個(gè)名為old的虛擬表，來(lái)訪問(wèn)被刪除的行。但old中的值全部是只讀的，不能被更新。

5.3 update觸發(fā)器。相比insert觸發(fā)器和delete觸發(fā)器，update觸發(fā)器既可使用new虛擬表，也可以使用old虛擬表。new虛擬表可以訪問(wèn)更新后的值，而old虛擬表可以訪問(wèn)（update語(yǔ)句執(zhí)行前）的值。同樣，old中的值全部是只讀的，不能被更新。特別要注意的是，當(dāng)觸發(fā)器涉及對(duì)觸發(fā)表自身的更新操作時(shí)，只能使用before update觸發(fā)器，而after update觸發(fā)器將不被允許。

5.4 觸發(fā)器應(yīng)用舉例。某進(jìn)銷(xiāo)存數(shù)據(jù)庫(kù)（db_jxc）中有業(yè)務(wù)統(tǒng)計(jì)表（persons）和銷(xiāo)售額表（sales）等表。表person有銷(xiāo)售人姓名、銷(xiāo)售數(shù)量字段。表sales有姓名、銷(xiāo)售金額字段。要求每更新一次person表后，都要更新sales表對(duì)應(yīng)的銷(xiāo)售金額字段（銷(xiāo)售金額=銷(xiāo)售數(shù)量*20）。

經(jīng)分析，可用觸發(fā)器來(lái)實(shí)現(xiàn)，操作步驟如下：（1）創(chuàng)建數(shù)據(jù)庫(kù)：create database db_jxc；（2）選擇數(shù)據(jù)庫(kù)：use db_jxc；（3）創(chuàng)建表：Create table persons（name char（10），nun int）；Create table sales（name char（10），sum int）；（4）創(chuàng)建觸發(fā)器：Create TRIGGER nun_sum after insert on persons FOR EACH ROW INSERT into sales values（new.name，20*new.nun）；（5）向表persons中插入記錄：Insert into persons values（‘jack，50），（‘luck，47）；（6）檢查觸發(fā)器的執(zhí)行情況，如圖1所示。

6 觸發(fā)器的刪除

刪除觸發(fā)器指刪除原來(lái)已經(jīng)在某個(gè)數(shù)據(jù)庫(kù)中創(chuàng)建的觸發(fā)器，與MySQL中刪除數(shù)據(jù)庫(kù)的命令相似。

格式：drop TRIGGER [if exists] [schema_name.]trigger_name；

If exists為可選項(xiàng)，用于避免刪除沒(méi)有的數(shù)據(jù)庫(kù)?？蛇x項(xiàng)schema_name用于指定觸發(fā)器所在的數(shù)據(jù)庫(kù)的名稱(chēng)，若沒(méi)有指定，則默認(rèn)為當(dāng)前數(shù)據(jù)庫(kù)。trigger_name指定要?jiǎng)h除的觸發(fā)器的名稱(chēng)。當(dāng)刪除一個(gè)表的同時(shí)，也會(huì)自動(dòng)地刪除該表上的觸發(fā)器。

另外，觸發(fā)器不能更新或覆蓋，為了修改一個(gè)觸發(fā)器，必須先刪除它，然后再重新創(chuàng)建。在應(yīng)用完觸發(fā)器后，切記一定要將觸發(fā)器刪除，否則在執(zhí)行某些數(shù)據(jù)庫(kù)操作時(shí)，會(huì)造成數(shù)據(jù)的變化。

7 使用觸發(fā)器的注意事項(xiàng)

在使用觸發(fā)器的時(shí)候需要注意：（1）在觸發(fā)器的創(chuàng)建過(guò)程中，每個(gè)表每個(gè)事件每次只允許一個(gè)觸發(fā)器。因此，每個(gè)表最多支持6個(gè)觸發(fā)器，即insert、update和delete的之前與之后。（2）及時(shí)刪除不再需要的觸發(fā)器。觸發(fā)器定義之后，每次執(zhí)行觸發(fā)事件，都會(huì)激活觸發(fā)器并執(zhí)行觸發(fā)的主體動(dòng)作。如果需求發(fā)生變化，而觸發(fā)器沒(méi)有進(jìn)行相應(yīng)的改變或刪除，則觸發(fā)器仍然會(huì)執(zhí)行舊的語(yǔ)句，從而影響新的數(shù)據(jù)的完整性。

8 結(jié)束語(yǔ)

本文介紹了觸發(fā)器的用途、創(chuàng)建、使用方法等，案例代碼經(jīng)多次調(diào)試運(yùn)行無(wú)錯(cuò)誤。觸發(fā)器能有效地保護(hù)數(shù)據(jù)庫(kù)中的數(shù)據(jù)，能解決一些復(fù)雜的問(wèn)題。正確使用觸發(fā)器是MySQL數(shù)據(jù)庫(kù)編程人員必備的素質(zhì)。

參考文獻(xiàn)：

[1]MySQL數(shù)據(jù)庫(kù)程序設(shè)計(jì)[M].北京：高等教育出版社，2013.

[2]劉增杰，張少軍.MySQL5.5從零開(kāi)始學(xué)[M].北京：清華大學(xué)出版社，2012.

作者簡(jiǎn)介：徐安令（1979-），云南宣威人，講師，計(jì)算機(jī)應(yīng)用技術(shù)工程碩士，研究方向：數(shù)據(jù)庫(kù)應(yīng)用與程序設(shè)計(jì)。

作者單位：德宏師范高等專(zhuān)科學(xué)校 計(jì)科系，云南德宏 678400