觸發器在MySQL數據庫中的應用

摘 要：觸發器能夠解決數據庫復雜的業務規則或要求，從而保證數據庫的完整性、正確性和一致性。本文介紹了觸發器的概念、用途、創建、使用等，旨在幫助數據庫編程人員正確使用觸發器。

關鍵詞：觸發器；trigger；MySQL

中圖分類號：TP309

1 觸發器的概念

觸發器是一個被指定關聯到一個表的數據庫對象，當對一個表的特定事件出現時，它將被激活。觸發器可以看作是特殊的存儲過程，不同的是，執行存儲過程要使用call語句來調用，只要當一個預定義的事件發生的時候，觸發主體動作就會被MySQL自動調用。具體而言，觸發器就是MySQL響應insert、update和delete語句而自動執行的一條或一組MySQL語句。

2 觸發器的用途

（1）它可用于檢查插入到表中的值。例如：每當增加一個學生到數據庫的學生基本信息表時，都檢查其電話號碼的格式是否正確。（2）它能對更新涉及的值進行計算。例如：每當客戶訂購一個產品時，都從產品庫存表中將庫存量減去訂購的數量。（3）用于創建審計跟蹤，也就是可使用觸發器把表的更改狀態以及之前和之后的狀態記錄到另外一張數據表中。正確的使用觸發器，這能保障數據庫中數據的完整性正確性，以及多個表之間數據的一致性。

3 觸發器的創建

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

其說明如下：DEFINER從句：指定當觸發器被觸發時滿足事件執行權限的安全上下文，為可選項。t_name：觸發器的名稱，觸發器在當前數據庫中必須具有唯一的名稱。t_time：觸發器被觸發的時機，有before和after兩個選項。t_event：觸發事件，包括insert、update和delete，指定激活觸發器的語句種類。tbl_name：標識建立觸發器的表名。FOR EACH ROW：表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器。t_body：指定觸發器被觸發后執行的語句，稱為觸發器主體。當觸發器主體有多條語句時，將要執行的多條語句放begin和end語句之間，各語句之間要用分隔符“；”隔開。

4 觸發器的查看

查看觸發器是指查看數據庫中已存在的觸發器的定義、狀態和語法信息等。查看當前數據庫中的觸發器語句為：“show TRIGGERS”，但執行該語句顯示的觸發器信息較為混亂，可以在show TRIGGERS命令后加“＼G”，這樣顯示的信息就相對更有條理。在MySQL數據庫中，所有的觸發器定義都存放在系統自帶的數據庫information_schema的triggers表中，所以可以通過語句“select * from information_schema.triggers where trigger_name=觸發器名稱”查看滿足指定條件的觸發器，該方法更為實用。

5 觸發器的使用

觸發器是與表有關的命名數據庫對象，當表上出現特定事件時，將激活該對象。根據觸發的事件，觸發器可分為insert、delete和update觸發器。

5.1 insert觸發器。insert觸發器可在insert語句執行之前或之后執行。需要注意以下幾點：在insert觸發器代碼內，可引用一個名為new的虛擬表，來訪問被插入的行。在before insert觸發器中，new中的值也可以被更新，即允許更改被插入的值（只要具有對應的操作權限）。對于auto_increment列，new在insert執行之前包含的是0值，在insert執行之后將包含新的自動生成值。

5.2 delete觸發器。delete觸發器可在delete語句執行之前或之后執行。在delete觸發器代碼內，可以引用一個名為old的虛擬表，來訪問被刪除的行。但old中的值全部是只讀的，不能被更新。

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

5.4 觸發器應用舉例。某進銷存數據庫（db_jxc）中有業務統計表（persons）和銷售額表（sales）等表。表person有銷售人姓名、銷售數量字段。表sales有姓名、銷售金額字段。要求每更新一次person表后，都要更新sales表對應的銷售金額字段（銷售金額=銷售數量*20）。

經分析，可用觸發器來實現，操作步驟如下：（1）創建數據庫：create database db_jxc；（2）選擇數據庫：use db_jxc；（3）創建表：Create table persons（name char（10），nun int）；Create table sales（name char（10），sum int）；（4）創建觸發器：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）檢查觸發器的執行情況，如圖1所示。

6 觸發器的刪除

刪除觸發器指刪除原來已經在某個數據庫中創建的觸發器，與MySQL中刪除數據庫的命令相似。

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

If exists為可選項，用于避免刪除沒有的數據庫?？蛇x項schema_name用于指定觸發器所在的數據庫的名稱，若沒有指定，則默認為當前數據庫。trigger_name指定要刪除的觸發器的名稱。當刪除一個表的同時，也會自動地刪除該表上的觸發器。

另外，觸發器不能更新或覆蓋，為了修改一個觸發器，必須先刪除它，然后再重新創建。在應用完觸發器后，切記一定要將觸發器刪除，否則在執行某些數據庫操作時，會造成數據的變化。

7 使用觸發器的注意事項

在使用觸發器的時候需要注意：（1）在觸發器的創建過程中，每個表每個事件每次只允許一個觸發器。因此，每個表最多支持6個觸發器，即insert、update和delete的之前與之后。（2）及時刪除不再需要的觸發器。觸發器定義之后，每次執行觸發事件，都會激活觸發器并執行觸發的主體動作。如果需求發生變化，而觸發器沒有進行相應的改變或刪除，則觸發器仍然會執行舊的語句，從而影響新的數據的完整性。

8 結束語

本文介紹了觸發器的用途、創建、使用方法等，案例代碼經多次調試運行無錯誤。觸發器能有效地保護數據庫中的數據，能解決一些復雜的問題。正確使用觸發器是MySQL數據庫編程人員必備的素質。

參考文獻：

[1]MySQL數據庫程序設計[M].北京：高等教育出版社，2013.

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

作者簡介：徐安令（1979-），云南宣威人，講師，計算機應用技術工程碩士，研究方向：數據庫應用與程序設計。

作者單位：德宏師范高等?？茖W校 計科系，云南德宏 678400