關(guān)于MySQL數(shù)據(jù)庫中觸發(fā)器的學(xué)習(xí)探討

劉麗娟　吳達(dá)勝

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

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

中圖分類號(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ù)庫技術(shù)已經(jīng)成為計(jì)算機(jī)信息系統(tǒng)與應(yīng)用系統(tǒng)的核心技術(shù)和重要基礎(chǔ)。數(shù)據(jù)庫是一門理論與實(shí)踐緊密結(jié)合的課程，它作為計(jì)算機(jī)及相關(guān)專業(yè)的骨干課程，在教學(xué)中占有重要的位置[1]。觸發(fā)器是這門課程中的一個(gè)重點(diǎn)，觸發(fā)器的靈活性增加了學(xué)生學(xué)習(xí)的難度，是該門課程的難點(diǎn)之一[2]。

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

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

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

1 基本概念

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

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

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

⑵ 審計(jì)：可以跟蹤用戶對(duì)數(shù)據(jù)庫的操作，審計(jì)用戶操作數(shù)據(jù)庫的語句，把用戶對(duì)數(shù)據(jù)庫的更新寫入審計(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ù)庫對(duì)象。例如，觸發(fā)器可以回退任何借出去的書超過庫存的數(shù)量。

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

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

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

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

{ BEFORE | AFTER }：觸發(fā)器有執(zhí)行的時(shí)間設(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í)行一次動(dòng)作，而不是對(duì)整個(gè)表執(zhí)行一次。

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

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

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

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

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

對(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í)行的語句。如果打算執(zhí)行多個(gè)語句，可使用BEGIN…END復(fù)合語句結(jié)構(gòu)。這樣，就能使用存儲(chǔ)子程序中允許的相同語句了。

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

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

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

3 刪除觸發(fā)器

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

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

DROP TRIGGER[IF EXISTS] trigger_name

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

4 OLD和NEW

NEW.column_name或者OLD.column_name，這樣在技術(shù)上處理（NEW|OLD.column_name）新和舊的列名屬于創(chuàng)建了過渡變量（"transition variables"）。對(duì)于INSERT語句，只有NEW是合法的；對(duì)于DELETE語句，只有OLD才合法；而UPDATE語句可以同時(shí)使用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ù)借日期，超期時(shí)間，超期費(fèi)用）

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

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

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

DELIMITER |

CREATE TRIGGER tri_borrow_book_insertli

AFTER INSERT ON borrow_book

FOR EACH ROW

/*借書在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 還書觸發(fā)器

DELIMITER |

CREATE TRIGGER tri_borrow_book_update

AFTER UPDATE ON borrow_book

FOR EACH ROW

/*還書時(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é)束語

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

參考文獻(xiàn)：

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

[2] 劉麗娟，吳達(dá)勝.滾動(dòng)式分層教學(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].電腦編程技巧與維護(hù)，2013.2：23-37