劉麗娟　吳達勝

摘 要： 觸發(fā)器是一種特殊的存儲過程，是數(shù)據(jù)庫強制業(yè)務(wù)規(guī)則和控制數(shù)據(jù)完整性的一種主要方法。探討了在MySQL數(shù)據(jù)庫中通過觸發(fā)器技術(shù)實現(xiàn)數(shù)據(jù)完整性控制機制，重點討論了MySQL數(shù)據(jù)庫中觸發(fā)器的工作原理與實現(xiàn)過程，結(jié)合實例分析設(shè)計觸發(fā)器的基本步驟。針對觸發(fā)器這一數(shù)據(jù)庫學(xué)習(xí)中的重點和難點，給出了在MySQL數(shù)據(jù)庫中對觸發(fā)器的理解和使用方法。

關(guān)鍵詞： 數(shù)據(jù)庫； 完整性； 觸發(fā)器； MySQL

中圖分類號：TP311 文獻標志碼：A 文章編號：1006-8228（2014）04-66-03

Abstract： The trigger is a special storing procedure， and a major method to force the business rules and control the data integrity. The control mechanism of data integrity according to trigger technology in MySQL is discussed， mainly work principle and implementation process with examples of analysis and design. In connection with the emphasis and difficulty of trigger in database learning， the understanding and using introduction of trigger in MySQL are given.

Key words： database； integrity； trigger； MySQL

0 引言

隨著計算機技術(shù)的發(fā)展，數(shù)據(jù)已經(jīng)滲透到當今每一個行業(yè)和業(yè)務(wù)職能領(lǐng)域，成為重要的生產(chǎn)因素。數(shù)據(jù)庫技術(shù)已經(jīng)成為計算機信息系統(tǒng)與應(yīng)用系統(tǒng)的核心技術(shù)和重要基礎(chǔ)。數(shù)據(jù)庫是一門理論與實踐緊密結(jié)合的課程，它作為計算機及相關(guān)專業(yè)的骨干課程，在教學(xué)中占有重要的位置[1]。觸發(fā)器是這門課程中的一個重點，觸發(fā)器的靈活性增加了學(xué)生學(xué)習(xí)的難度，是該門課程的難點之一[2]。

觸發(fā)器是與表事件相關(guān)聯(lián)的特殊存儲過程，它們不能直接執(zhí)行，只在發(fā)生針對表的insert、update、delete事件時觸發(fā)。用戶不能繞過觸發(fā)器，除非觸發(fā)器向客戶端發(fā)送消息，否則最終用戶將不會意識到觸發(fā)器的存在。

觸發(fā)器的開發(fā)涉及多種SQL技術(shù)。要開發(fā)出良好的觸發(fā)器，必須理解事務(wù)的流程和鎖定、SQL以及存儲過程。觸發(fā)器包含幾個獨特的元素，開發(fā)人員必須對其進行仔細規(guī)劃，并通過觸發(fā)器實現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則和數(shù)據(jù)驗證功能。

有些DBA反對使用觸發(fā)器，因為它們是專用的。如果將數(shù)據(jù)庫移植到其他平臺，必須重寫大部分觸發(fā)器代碼。另外，觸發(fā)器還被指責(zé)影響性能。然而，如果規(guī)則過于復(fù)雜，無法使用約束來實現(xiàn)，則只能使用觸發(fā)器來實現(xiàn)。業(yè)務(wù)規(guī)則在服務(wù)器外實現(xiàn)就不是規(guī)則了，而只是建議。設(shè)計糟糕的觸發(fā)器將影響性能，但設(shè)計良好的觸發(fā)器不但可以確保數(shù)據(jù)完整性，還能提供良好的性能[3]。

1 基本概念

觸發(fā)發(fā)器是一種特殊的存儲過程，它在插入、刪除或修改特定表中的數(shù)據(jù)時觸發(fā)執(zhí)行，它比數(shù)據(jù)庫本身標準的功能有更精細和更復(fù)雜的數(shù)據(jù)控制能力。與存儲過程不同的是，存儲過程通過其他程序來啟動運行，而觸發(fā)器由一個事件來啟動運行，并且觸發(fā)器不能接收參數(shù)[4]。

數(shù)據(jù)庫觸發(fā)器有以下作用。

⑴ 安全性：可以基于數(shù)據(jù)庫的值使用戶具有操作數(shù)據(jù)庫的某種權(quán)利。

⑵ 審計：可以跟蹤用戶對數(shù)據(jù)庫的操作，審計用戶操作數(shù)據(jù)庫的語句，把用戶對數(shù)據(jù)庫的更新寫入審計表。

⑶ 實現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則：實現(xiàn)非標準的數(shù)據(jù)完整性檢查和約束，觸發(fā)器可產(chǎn)生比規(guī)則更為復(fù)雜的限制，與規(guī)則不同，觸發(fā)器可以引用列或數(shù)據(jù)庫對象。例如，觸發(fā)器可以回退任何借出去的書超過庫存的數(shù)量。

⑷ 實現(xiàn)復(fù)雜的非標準的數(shù)據(jù)庫相關(guān)完整性規(guī)則。觸發(fā)器可以對數(shù)據(jù)庫中相關(guān)的表進行連環(huán)更新。

⑸ 同步實時地復(fù)制表中的數(shù)據(jù)。

⑹ 自動計算數(shù)據(jù)值。如果數(shù)據(jù)的值達到了一定的要求，則進行特定的處理。例如，某本書的庫存低于一定指標，則立刻向圖書管理員發(fā)出警告數(shù)據(jù)。

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

只有具備super權(quán)限的MySQL用戶才能執(zhí)行創(chuàng)建觸發(fā)器的命令。創(chuàng)建觸發(fā)器命令格式如下：

CREATE TRIGGER trigger_name BEFORE|AFTER

INSERT|UPDATE|DELETE ON table_name FOR

EACH ROW CODE.

觸發(fā)器是與表有關(guān)的命名數(shù)據(jù)庫對象，當表上出現(xiàn)特定事件時，將激活該對象。

觸發(fā)器與命名為table_name的表相關(guān)，table_name必須引用永久性表，MySQL中不能將觸發(fā)器與TYEMPORARY表或視圖關(guān)聯(lián)起來。

{ BEFORE | AFTER }：觸發(fā)器有執(zhí)行的時間設(shè)置，可以設(shè)置為事件發(fā)生前或后。

{ INSERT | UPDATE | DELETE }：同樣也能設(shè)定觸發(fā)的事件，它們可以在執(zhí)行INSERT、UPDATE或DELETE的過程中觸發(fā)。

FOR EACH ROW：觸發(fā)器的執(zhí)行間隔，F(xiàn)OR EACH ROW子句通知觸發(fā)器 每隔一行執(zhí)行一次動作，而不是對整個表執(zhí)行一次。

觸發(fā)器的觸發(fā)事件可以是下列三種之一。

⑴ INSERT：將新行插入表時激活觸發(fā)器程序，例如，通過INSERT、LOAD DATA和REPLACE語句。

⑵ UPDATE：更改某一行時激活觸發(fā)器，例如，通過UPDATE語句。

⑶ DELETE：從表中刪除某一行時激活觸發(fā)器，例如，通過DELETE和REPLACE語句。

請注意，觸發(fā)事件與表操作方式激活觸發(fā)程序的SQL語句并不很類似，這點很重要。例如，關(guān)于INSERT的BEFRORE觸發(fā)程序不僅能被INSERT語句激活，也能被LOAD DATA語句激活。

對于具有相同觸發(fā)程序動作時間和事件的給定表，不能有兩個觸發(fā)程序。例如，對于某一張表，不能有兩個BEFORE UPDATE觸發(fā)程序。但可以有一個BEFORE UPDATE觸發(fā)程序和一個BEFORE INSERT觸發(fā)程序，或一個BEFORE UPDATE觸發(fā)程序和一個AFTER UPDATE觸發(fā)程序。

CODE是當觸發(fā)程序激活時執(zhí)行的語句。如果打算執(zhí)行多個語句，可使用BEGIN…END復(fù)合語句結(jié)構(gòu)。這樣，就能使用存儲子程序中允許的相同語句了。

另外，觸發(fā)器執(zhí)行的語句有以下兩個限制。

限制⑴：觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶端的存儲程序，也不能使用采用CALL語句的動態(tài)SQL語句，但是允許存儲程序通過參數(shù)將數(shù)據(jù)返回觸發(fā)程序。也就是存儲過程或者函數(shù)通過OUT或者INOUT類型的參數(shù)將數(shù)據(jù)返回觸發(fā)器是可以的，但是不能調(diào)用直接返回數(shù)據(jù)的過程。

限制⑵：不能在觸發(fā)器中使用以顯式或隱式方式開始或結(jié)束事務(wù)的語句，如START TRANSACTION、COMMIT或者ROLLBACK。

3 刪除觸發(fā)器

目前，并沒有ALTER TRIGGER語句，不過可以先使用DROP TRIGGER語句（刪除觸發(fā)器），然后再使用CREATE TRIGGERA語句創(chuàng)建一個新的觸發(fā)器。

刪除觸發(fā)器的命令格式如下：

DROP TRIGGER[IF EXISTS] trigger_name

這條語句用于刪除觸發(fā)器。當觸發(fā)器不存在時，使用關(guān)鍵字IF EXISTS可以避免出現(xiàn)出錯信息。若不使用該關(guān)鍵字，它會生成一個注釋，在執(zhí)行SHOW WARNINGS語句后，可以顯示該注釋?？梢灾付ㄅc觸發(fā)器關(guān)聯(lián)的數(shù)據(jù)庫或模式。如果不指定，將使用當前默認數(shù)據(jù)庫。從MySQL5.1.6版本開始，該語句需要具有與給定觸發(fā)器相關(guān)的表的TRIGGER權(quán)限，方能執(zhí)行。在以前的MySQL版本中，它僅需要SUPER權(quán)限即可執(zhí)行該語句。如果對MySQL5.0.10版本或更老版本升級時，請確保刪除它的所有觸發(fā)器，因為使用舊版本的觸發(fā)器會帶來問題。

4 OLD和NEW

NEW.column_name或者OLD.column_name，這樣在技術(shù)上處理（NEW|OLD.column_name）新和舊的列名屬于創(chuàng)建了過渡變量（"transition variables"）。對于INSERT語句，只有NEW是合法的；對于DELETE語句，只有OLD才合法；而UPDATE語句可以同時使用NEW和OLD。

5 觸發(fā)器在圖書管理系統(tǒng)中的應(yīng)用

觸發(fā)器程序?qū)⒂玫揭韵氯龔埍恚?/p>

⑴ Publiser（pub_id，pub_name，pub_tel，pub_web， pub_addr）

出版社（出版社_id，出版社名，電話，網(wǎng)址，地址）

⑵ Borrow_book（borrow_id，out_date，in_date，renew_count，renew_date，over_time，over_payment）

借閱（借閱_id，借出日期，歸還日期，續(xù)借次數(shù)，續(xù)借日期，超期時間，超期費用）

⑶ Reader（reader_id，user，password，create_date，name，borrow_count）

讀者（借書證號，用戶名，密碼，注冊日期，姓名，借閱數(shù)量）

例1 當從publiser表中更新數(shù)據(jù)時，希望向另兩個表中寫入數(shù)據(jù)，保存更新前后的信息。

新建兩張與publiser結(jié)構(gòu)一樣的表old_publiser與new_publisher。

DELIMITER |

CREATE TRIGGER tri_publisher_update

AFTER UPDATE

ON publisher FOR EACH ROW

/*保存更新前的信息*/

BEGIN

INSERT INTO old_publisher

（pub_id，pub_name，pub_tel，pub_web， pub_addr）

VALUES

（OLD.pub_id，OLD.pub_name，OLD.pub_tel，OLD.pub_web，

OLD.pub_addr）；

/*保存更新后的信息*/

INSERT INTO new_publisher

（pub_id，pub_name，pub_tel，pub_web， pub_addr）

VALUES

（NEW.pub_id，NEW.pub_name，NEW.pub_tel，NEW.pub_web，

NEW.pub_addr）；

END |

DELIMITER；

驗證該觸發(fā)器：

UPDATE publisher SET pub_tel=010-6832629

4 WHERE pub_id='P00002'；

SELECT * FROM old_publisher；

SELECT * FROM new_publisher；

例2 借書觸發(fā)器

DELIMITER |

CREATE TRIGGER tri_borrow_book_insertli

AFTER INSERT ON borrow_book

FOR EACH ROW

/*借書在borrow_book表插入新紀錄時，自動更新讀者表的已借數(shù)量*/

BEGIN

UPDATE reader SET borrow_count=borrow_count+1

WHERE reader_id=NEW.reader_id；

END |

DELIMITER；

例3 還書觸發(fā)器

DELIMITER |

CREATE TRIGGER tri_borrow_book_update

AFTER UPDATE ON borrow_book

FOR EACH ROW

/*還書時更新borrow_book 表時，自動更新讀者表的已借數(shù)量*/

BEGIN

IF ISNULL（OLD.return_date） AND NOT ISNULL

（NEW.return_date） THEN

UPDATE reader SET borrow_count=borrow_count-1

WHERE reader_id=NEW.reader_id；

END IF；

END |

DELIMITER；

6 結(jié)束語

本文從理論教學(xué)和實踐教學(xué)兩個方面對MySQL數(shù)據(jù)庫中觸發(fā)器的實現(xiàn)進行了探討，并應(yīng)用到了浙江農(nóng)林大學(xué)數(shù)據(jù)庫類課程的教學(xué)當中。實踐表明該方法提高了學(xué)生對MySQL觸發(fā)器學(xué)習(xí)的積極性，使他們能輕松掌握觸發(fā)器的實現(xiàn)過程。教學(xué)效果和學(xué)生的學(xué)習(xí)效率得到了提高。

參考文獻：

[1] 吳達勝，劉麗娟.《數(shù)據(jù)庫原理與技術(shù)》的理論與實踐教學(xué)的整體優(yōu)化研究[J].計算機時代，2005.11：31-32

[2] 劉麗娟，吳達勝.滾動式分層教學(xué)在數(shù)據(jù)庫課程中的應(yīng)用[J].科技信息，2009.36：127-130

[3] 呂剛，蔣勇銘，馬少恒.淺析SQL Server觸發(fā)器的應(yīng)用[J].信息與電腦，2012.10：109-110

[4] 蘭萍.SQLServer觸發(fā)器在數(shù)據(jù)完整性和安全方面的應(yīng)用[J].電腦編程技巧與維護，2013.2：23-37