劉麗娟　吳達(dá)勝

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

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

中圖分類(lèi)號(hào)：TP311 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：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 引言

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

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

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

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

1 基本概念

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

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

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

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

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

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

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

⑹ 自動(dòng)計(jì)算數(shù)據(jù)值。如果數(shù)據(jù)的值達(dá)到了一定的要求，則進(jìn)行特定的處理。例如，某本書(shū)的庫(kù)存低于一定指標(biāo)，則立刻向圖書(shū)管理員發(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ù)庫(kù)對(duì)象，當(dāng)表上出現(xiàn)特定事件時(shí)，將激活該對(duì)象。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3 刪除觸發(fā)器

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

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

DROP TRIGGER[IF EXISTS] trigger_name

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

4 OLD和NEW

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

5 觸發(fā)器在圖書(shū)管理系統(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ù)借日期，超期時(shí)間，超期費(fèi)用）

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

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

例1 當(dāng)從publiser表中更新數(shù)據(jù)時(shí)，希望向另兩個(gè)表中寫(xiě)入數(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；

驗(yàn)證該觸發(fā)器：

UPDATE publisher SET pub_tel=010-6832629

4 WHERE pub_id='P00002'；

SELECT * FROM old_publisher；

SELECT * FROM new_publisher；

例2 借書(shū)觸發(fā)器

DELIMITER |

CREATE TRIGGER tri_borrow_book_insertli

AFTER INSERT ON borrow_book

FOR EACH ROW

/*借書(shū)在borrow_book表插入新紀(jì)錄時(shí)，自動(dòng)更新讀者表的已借數(shù)量*/

BEGIN

UPDATE reader SET borrow_count=borrow_count+1

WHERE reader_id=NEW.reader_id；

END |

DELIMITER；

例3 還書(shū)觸發(fā)器

DELIMITER |

CREATE TRIGGER tri_borrow_book_update

AFTER UPDATE ON borrow_book

FOR EACH ROW

/*還書(shū)時(shí)更新borrow_book 表時(shí)，自動(dòng)更新讀者表的已借數(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é)束語(yǔ)

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

參考文獻(xiàn)：

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

[2] 劉麗娟，吳達(dá)勝.滾動(dòng)式分層教學(xué)在數(shù)據(jù)庫(kù)課程中的應(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].電腦編程技巧與維護(hù)，2013.2：23-37