摘 要:觸發(fā)器能夠解決數(shù)據(jù)庫復雜的業(yè)務規(guī)則或要求,從而保證數(shù)據(jù)庫的完整性、正確性和一致性。本文介紹了觸發(fā)器的概念、用途、創(chuàng)建、使用等,旨在幫助數(shù)據(jù)庫編程人員正確使用觸發(fā)器。
關鍵詞:觸發(fā)器;trigger;MySQL
中圖分類號:TP309
1 觸發(fā)器的概念
觸發(fā)器是一個被指定關聯(lián)到一個表的數(shù)據(jù)庫對象,當對一個表的特定事件出現(xiàn)時,它將被激活。觸發(fā)器可以看作是特殊的存儲過程,不同的是,執(zhí)行存儲過程要使用call語句來調(diào)用,只要當一個預定義的事件發(fā)生的時候,觸發(fā)主體動作就會被MySQL自動調(diào)用。具體而言,觸發(fā)器就是MySQL響應insert、update和delete語句而自動執(zhí)行的一條或一組MySQL語句。
2 觸發(fā)器的用途
(1)它可用于檢查插入到表中的值。例如:每當增加一個學生到數(shù)據(jù)庫的學生基本信息表時,都檢查其電話號碼的格式是否正確。(2)它能對更新涉及的值進行計算。例如:每當客戶訂購一個產(chǎn)品時,都從產(chǎn)品庫存表中將庫存量減去訂購的數(shù)量。(3)用于創(chuàng)建審計跟蹤,也就是可使用觸發(fā)器把表的更改狀態(tài)以及之前和之后的狀態(tài)記錄到另外一張數(shù)據(jù)表中。正確的使用觸發(fā)器,這能保障數(shù)據(jù)庫中數(shù)據(jù)的完整性正確性,以及多個表之間數(shù)據(jù)的一致性。
3 觸發(fā)器的創(chuàng)建
語法格式:Create [DEFINER={user|CURRENT_USER}] TRIGGER t_name t_time t_event ON tbl_name FOR EACH ROW t_body
其說明如下:DEFINER從句:指定當觸發(fā)器被觸發(fā)時滿足事件執(zhí)行權限的安全上下文,為可選項。t_name:觸發(fā)器的名稱,觸發(fā)器在當前數(shù)據(jù)庫中必須具有唯一的名稱。t_time:觸發(fā)器被觸發(fā)的時機,有before和after兩個選項。t_event:觸發(fā)事件,包括insert、update和delete,指定激活觸發(fā)器的語句種類。tbl_name:標識建立觸發(fā)器的表名。FOR EACH ROW:表示任何一條記錄上的操作滿足觸發(fā)事件都會觸發(fā)該觸發(fā)器。t_body:指定觸發(fā)器被觸發(fā)后執(zhí)行的語句,稱為觸發(fā)器主體。當觸發(fā)器主體有多條語句時,將要執(zhí)行的多條語句放begin和end語句之間,各語句之間要用分隔符“;”隔開。
4 觸發(fā)器的查看
查看觸發(fā)器是指查看數(shù)據(jù)庫中已存在的觸發(fā)器的定義、狀態(tài)和語法信息等。查看當前數(shù)據(jù)庫中的觸發(fā)器語句為:“show TRIGGERS”,但執(zhí)行該語句顯示的觸發(fā)器信息較為混亂,可以在show TRIGGERS命令后加“\G”,這樣顯示的信息就相對更有條理。在MySQL數(shù)據(jù)庫中,所有的觸發(fā)器定義都存放在系統(tǒng)自帶的數(shù)據(jù)庫information_schema的triggers表中,所以可以通過語句“select * from information_schema.triggers where trigger_name=觸發(fā)器名稱”查看滿足指定條件的觸發(fā)器,該方法更為實用。
5 觸發(fā)器的使用
觸發(fā)器是與表有關的命名數(shù)據(jù)庫對象,當表上出現(xiàn)特定事件時,將激活該對象。根據(jù)觸發(fā)的事件,觸發(fā)器可分為insert、delete和update觸發(fā)器。
5.1 insert觸發(fā)器。insert觸發(fā)器可在insert語句執(zhí)行之前或之后執(zhí)行。需要注意以下幾點:在insert觸發(fā)器代碼內(nèi),可引用一個名為new的虛擬表,來訪問被插入的行。在before insert觸發(fā)器中,new中的值也可以被更新,即允許更改被插入的值(只要具有對應的操作權限)。對于auto_increment列,new在insert執(zhí)行之前包含的是0值,在insert執(zhí)行之后將包含新的自動生成值。
5.2 delete觸發(fā)器。delete觸發(fā)器可在delete語句執(zhí)行之前或之后執(zhí)行。在delete觸發(fā)器代碼內(nèi),可以引用一個名為old的虛擬表,來訪問被刪除的行。但old中的值全部是只讀的,不能被更新。
5.3 update觸發(fā)器。相比insert觸發(fā)器和delete觸發(fā)器,update觸發(fā)器既可使用new虛擬表,也可以使用old虛擬表。new虛擬表可以訪問更新后的值,而old虛擬表可以訪問(update語句執(zhí)行前)的值。同樣,old中的值全部是只讀的,不能被更新。特別要注意的是,當觸發(fā)器涉及對觸發(fā)表自身的更新操作時,只能使用before update觸發(fā)器,而after update觸發(fā)器將不被允許。
5.4 觸發(fā)器應用舉例。某進銷存數(shù)據(jù)庫(db_jxc)中有業(yè)務統(tǒng)計表(persons)和銷售額表(sales)等表。表person有銷售人姓名、銷售數(shù)量字段。表sales有姓名、銷售金額字段。要求每更新一次person表后,都要更新sales表對應的銷售金額字段(銷售金額=銷售數(shù)量*20)。
經(jīng)分析,可用觸發(fā)器來實現(xiàn),操作步驟如下:(1)創(chuàng)建數(shù)據(jù)庫:create database db_jxc;(2)選擇數(shù)據(jù)庫: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ā)器指刪除原來已經(jīng)在某個數(shù)據(jù)庫中創(chuàng)建的觸發(fā)器,與MySQL中刪除數(shù)據(jù)庫的命令相似。
格式:drop TRIGGER [if exists] [schema_name.]trigger_name;
If exists為可選項,用于避免刪除沒有的數(shù)據(jù)庫??蛇x項schema_name用于指定觸發(fā)器所在的數(shù)據(jù)庫的名稱,若沒有指定,則默認為當前數(shù)據(jù)庫。trigger_name指定要刪除的觸發(fā)器的名稱。當刪除一個表的同時,也會自動地刪除該表上的觸發(fā)器。
另外,觸發(fā)器不能更新或覆蓋,為了修改一個觸發(fā)器,必須先刪除它,然后再重新創(chuàng)建。在應用完觸發(fā)器后,切記一定要將觸發(fā)器刪除,否則在執(zhí)行某些數(shù)據(jù)庫操作時,會造成數(shù)據(jù)的變化。
7 使用觸發(fā)器的注意事項
在使用觸發(fā)器的時候需要注意:(1)在觸發(fā)器的創(chuàng)建過程中,每個表每個事件每次只允許一個觸發(fā)器。因此,每個表最多支持6個觸發(fā)器,即insert、update和delete的之前與之后。(2)及時刪除不再需要的觸發(fā)器。觸發(fā)器定義之后,每次執(zhí)行觸發(fā)事件,都會激活觸發(fā)器并執(zhí)行觸發(fā)的主體動作。如果需求發(fā)生變化,而觸發(fā)器沒有進行相應的改變或刪除,則觸發(fā)器仍然會執(zhí)行舊的語句,從而影響新的數(shù)據(jù)的完整性。
8 結束語
本文介紹了觸發(fā)器的用途、創(chuàng)建、使用方法等,案例代碼經(jīng)多次調(diào)試運行無錯誤。觸發(fā)器能有效地保護數(shù)據(jù)庫中的數(shù)據(jù),能解決一些復雜的問題。正確使用觸發(fā)器是MySQL數(shù)據(jù)庫編程人員必備的素質(zhì)。
參考文獻:
[1]MySQL數(shù)據(jù)庫程序設計[M].北京:高等教育出版社,2013.
[2]劉增杰,張少軍.MySQL5.5從零開始學[M].北京:清華大學出版社,2012.
作者簡介:徐安令(1979-),云南宣威人,講師,計算機應用技術工程碩士,研究方向:數(shù)據(jù)庫應用與程序設計。
作者單位:德宏師范高等專科學校 計科系,云南德宏 678400