第十七章觸發(fā)器學習目標:理解觸發(fā)器的工作機制了解觸發(fā)器的類型掌握觸發(fā)器的創(chuàng)建、查看和刪除方法掌握利用觸發(fā)器實現(xiàn)數(shù)據(jù)一致性的基本操作17.1觸發(fā)器的工作原理

觸發(fā)器由“INSERT”“UPDATE”和“DELETE”等事件來觸發(fā)某種特定操作。滿足觸發(fā)器的觸發(fā)條件時,數(shù)據(jù)庫系統(tǒng)就會執(zhí)行觸發(fā)器中定義的程序語句。這樣做可以保證某些操作之間的一致性。例如,當學生表中增加了一個學生的成績記錄時,或者對成績記錄做更新與刪除操作時,學生的總學分就必須根據(jù)成績情況同時改變。我們可以在這里創(chuàng)建一個觸發(fā)器,保證學生總學分與成績記錄數(shù)是一致的。 MySQL中,創(chuàng)建觸發(fā)器的基本語法格式如下:觸發(fā)器的工作原理 ①trigger_name是要創(chuàng)建的觸發(fā)器對象名稱,觸發(fā)器是與表有關的數(shù)據(jù)庫對象,當表上出現(xiàn)特定的事件時,將激活該對象。 ②tbl_name是觸發(fā)器關聯(lián)的表,tbl_name只能是永久表(PermanentTable),不能是臨時表。 ③trigger_time是觸發(fā)器的觸發(fā)時間,可以是BEFORE或者AFTER。BEFORE的含義是指在檢查約束前觸發(fā),而AFTER是在檢查約束后觸發(fā)。注意:不是觸發(fā)它的語句執(zhí)行前或后,因為一條語句影響多少行數(shù)據(jù),就會觸發(fā)多少次。 ④trigger_event就是觸發(fā)器的觸發(fā)事件,就是激活觸發(fā)程序的語句類型,可以是INSERT(包括INSERT,LOADDATA,REPLACE語句)、UPDATE或者DELETE(包括DELETE和REPLACE語句)。觸發(fā)器的工作原理

⑤FOREACHROW表示每行受影響,觸發(fā)器都執(zhí)行,稱為行級觸發(fā)器。Oracle觸發(fā)器中分行級觸發(fā)器和語句級觸發(fā)器,語句級可不寫foreachrow,無論影響多少行都只執(zhí)行一次。但MySQL不支持語句觸發(fā)器,所以必須寫FOREACHROW。

⑥trigger_stmt是觸發(fā)器的執(zhí)行語句,可以是一條語句,也可以是多條語句,如果需要執(zhí)行多條語句,就用BENGIN…END定義成語句塊,里面可以使用像存儲過程中的流程控制語句。

⑦在觸發(fā)器的執(zhí)行語句中,經(jīng)常使用別名OLD和NEW來引用觸發(fā)器關聯(lián)的表中正在發(fā)生變化的記錄內(nèi)容。

⑧對同一個表相同觸發(fā)時間的相同觸發(fā)事件,只能定義一個觸發(fā)器。17.2創(chuàng)建觸發(fā)器 【例17．1】創(chuàng)建一個觸發(fā)器tr_student_ins,實現(xiàn)當向學生表students中插入一條新記錄后,同時在學生總學分表credit中,插入該學生的總學分記錄,學分值為0。檢驗執(zhí)行效果。創(chuàng)建觸發(fā)器

【例17．2】創(chuàng)建一個觸發(fā)器tr_cno_upd,當更改表course中某門課的課程號時,同時將score表該課程號全部更新。并檢驗執(zhí)行效果。創(chuàng)建觸發(fā)器

【例17．3】創(chuàng)建一個觸發(fā)器tr_student_del,當刪除students表中某個人的記錄時,刪除score表和credit表的相應的成績記錄。并檢驗執(zhí)行效果。創(chuàng)建觸發(fā)器 【例17．4】創(chuàng)建一個存儲過程pro_compute_credit,用于計算學生的總學分,再創(chuàng)建3個觸發(fā)器tr_insert_credit,tr_update_credit,tr_delete_credit,分別實現(xiàn)對學生成績表score進行插入、更新、刪除操作時,調(diào)用存儲過程pro_compute_credit完成學生總學分的再計算。創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器 MySQL數(shù)據(jù)庫不支持在同一個觸發(fā)器中,同時實現(xiàn)INSERT,UPDATE,DELETE事件觸發(fā),因此在這里要用到3個觸發(fā)器,分別實現(xiàn)學生總學分的計算與更新。 MySQL數(shù)據(jù)庫也不支持對TRIGGER的修改操作,無論是ALTER語句,或者是CREATEORREPLACE語句,都沒有針對觸發(fā)器的操作。17.3查看觸發(fā)器

可以通過執(zhí)行SHOWTRIGGERS命令查看觸發(fā)器的狀態(tài)、語法等信息,但是因為不能查詢指定的觸發(fā)器,所以每次都返回所有的觸發(fā)器的信息,使用起來不是很方便。

【例17．5】使用SHOWTRIGGERS命令查看當前數(shù)據(jù)庫的所有觸發(fā)器的信息。

另一個查看方式是查詢系統(tǒng)表的information_schema.triggers表,這個方式可以查詢指定觸發(fā)器的指定信息,操作起來明顯方便很多。查看觸發(fā)器

【例17．6】利用系統(tǒng)表information_schema.triggers,查看tr_insert_credit觸發(fā)器的信息。17.4刪除觸發(fā)器 刪除觸發(fā)器指刪除數(shù)據(jù)庫中已經(jīng)存在的觸發(fā)器。MySQL中使用“DROPTRIGGER”語句來刪除觸發(fā)器。其基本形式如下:

【例17．7】刪除觸發(fā)器tr_student_del。17.5觸發(fā)器的使用 觸發(fā)器執(zhí)行的語句有以下兩個限制。 ①觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶端的存儲過程,也不能使用CALL語句的動態(tài)SQL語句,但是允許存儲過程通過參數(shù)將數(shù)據(jù)返回觸發(fā)程序。也就是存儲過程通過OUT或者INOUT類型的參數(shù)將數(shù)據(jù)返回觸發(fā)器是可以的,但是不能調(diào)用直接返回數(shù)據(jù)的存儲過程。 ②不能在觸發(fā)器中使用以顯式或隱式方式開始或結束事務的語句,如STARTTRANSACTION,COMMIT或ROLLBACK等事務操作語句。MySQL的觸發(fā)器是按照BEFORE觸發(fā)器、行操作(INSERT,UPDATE,DELETE)、AFTER觸發(fā)器的順序執(zhí)行的,其中任何一步操作發(fā)生錯誤都不會繼續(xù)執(zhí)行剩下的操作。本章小結 本章介紹了MySQL數(shù)據(jù)庫的觸發(fā)器,包括觸發(fā)器的定義和作用、創(chuàng)建觸發(fā)器、查看觸發(fā)器、使用觸發(fā)器和刪除觸發(fā)器等內(nèi)容。創(chuàng)建觸發(fā)器和使用觸發(fā)器是本章的重點。讀者在創(chuàng)建