第12章-觸發(fā)器課件

第12章觸發(fā)器計算機系軟件教研室第12章觸發(fā)器計算機系軟件教研室1第12章觸發(fā)器目標(biāo)：觸發(fā)器的基本概念及工作原理如何創(chuàng)建觸發(fā)器觸發(fā)器如何實現(xiàn)數(shù)據(jù)完整性如何查看、修改、刪除觸發(fā)器第12章觸發(fā)器目標(biāo)：2第12章觸發(fā)器12.1概述12.2觸發(fā)器的創(chuàng)建12.3觸發(fā)器實施數(shù)據(jù)完整性實例12.4查看修改和刪除觸發(fā)器12.5注意事項練習(xí)第12章觸發(fā)器12.1概述312.1概述觸發(fā)器(Trigger)是一種實施復(fù)雜數(shù)據(jù)完整性的特殊存儲過程，在對表或視圖執(zhí)行UPDATE、INSERT或DELETE語句時自動觸發(fā)執(zhí)行，以防止對數(shù)據(jù)進(jìn)行不正確、未授權(quán)或不一致的修改。觸發(fā)器是一種特殊類型的存儲過程，它不同于前面介紹過的存儲過程。觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名稱而被直接調(diào)用。觸發(fā)器是一個功能強大的工具，它使每個站點可以在有數(shù)據(jù)修改時自動強制執(zhí)行其業(yè)務(wù)規(guī)則。觸發(fā)器可以用于SQLServer約束、默認(rèn)值和規(guī)則的完整性檢查。12.1概述觸發(fā)器(Trigger)是一種實施復(fù)雜數(shù)據(jù)完整4分類：INSERT觸發(fā)器UPDATE觸發(fā)器DELETE觸發(fā)器分類：5觸發(fā)器的優(yōu)點觸發(fā)器是自動的：當(dāng)對表中的數(shù)據(jù)作了任何修改（比如手工輸入或者應(yīng)用程序采取的操作）之后立即被激活。觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進(jìn)行層疊更改。觸發(fā)器可以強制限制，這些限制比用CHECK約束所定義的更復(fù)雜。觸發(fā)器的優(yōu)點觸發(fā)器是自動的：當(dāng)對表中的數(shù)據(jù)作了任何修改（比如6觸發(fā)器的工作原理相關(guān)概念觸發(fā)器表Insert表和delete表：臨時表，結(jié)構(gòu)，內(nèi)存分類及工作原理：Insert觸發(fā)器的工作原理delete觸發(fā)器的工作原理update觸發(fā)器的工作原理觸發(fā)器的工作原理相關(guān)概念712.2觸發(fā)器的創(chuàng)建創(chuàng)建觸發(fā)器應(yīng)該考慮以下幾個問題：CREATETRIGGER語句必須是批處理中的第一個語句。創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。觸發(fā)器為數(shù)據(jù)庫對象，其名稱必須遵循標(biāo)識符的命名規(guī)則。雖然觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。雖然不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時表。當(dāng)創(chuàng)建一個觸發(fā)器時必須指定：⑴名稱；⑵在其上定義觸發(fā)器的表；⑶觸發(fā)器將何時激發(fā)；⑷激活觸發(fā)器的數(shù)據(jù)修改語句。12.2觸發(fā)器的創(chuàng)建創(chuàng)建觸發(fā)器應(yīng)該考慮以下幾個問題：8方法：企業(yè)管理器T-SQL語句方法：9一、使用T-SQL語句創(chuàng)建觸發(fā)器其語法形式如下：CREATETRIGGERtrigger_name

ON{table|view}

[WITHENCRYPTION]

FOR{[DELETE][,][INSERT][,][UPDATE]}

AS

sql_statement一、使用T-SQL語句創(chuàng)建觸發(fā)器其語法形式如下：101.創(chuàng)建insert觸發(fā)器例12-1:為student表建立insert觸發(fā)器以自動更新class表的學(xué)生人數(shù)。usejwglGo/*如果存在同名的觸發(fā)器，則刪除之*/Ifexists(selectnamefromsysobjectsWheretype=‘TR’ANDname=‘student_insert’)DropTRIGGERstudent_insertGo/*建立插入觸發(fā)器*/CreateTRIGGERstudent_insertonstudentForinsertAs1.創(chuàng)建insert觸發(fā)器例12-1:為student表建11Declare@NumOfStuentTinyintSelect@NumOfStuent=c.student_numfromclassc,insertedI wherec.class_id=I.class_idIf(@NumOfStuent>0) begin updateclasssetstudent_num=student_num+1 fromclassc,insertedIwherec.class_id=I.class_id endElse begin updateclasssetstudent_num=( selectcount(s.student_id)fromstudents,insertedI wheres.class_id=I.class_id) fromclassc,insertedIwherec.class_id=I.class_id endgoDeclare@NumOfStuentTinyint12運行Select*fromclasswhereclass_id=‘g99403’ExecspAddStudent‘g9940306’,’程濤’,‘男’,‘1981-9-22’,‘g99403’,’1999-9-1’,’南京市御道街2號’Select*fromclasswhereclass_id=‘g99403’分析執(zhí)行過程運行132.創(chuàng)建刪除觸發(fā)器例12-2:為student表建立delete觸發(fā)器以自動更新class表的學(xué)生人數(shù)。usejwglGo/*如果存在同名的觸發(fā)器，則刪除之*/Ifexists(selectnamefromsysobjectsWheretype=‘TR’ANDname=‘student_delete’)DropTRIGGERstudent_deleteGo/*建立刪除觸發(fā)器*/CreateTRIGGERstudent_deleteonstudentFordeleteAs2.創(chuàng)建刪除觸發(fā)器例12-2:為student表建立del14Declare@NumOfStuentTinyintSelect@NumOfStuent=c.student_numfromclassc,deletedd wherec.class_id=d.class_idIf(@NumOfStuent>0) begin updateclasssetstudent_num=student_num-1 fromclassc,deleteddwherec.class_id=d.class_id endElse begin updateclasssetstudent_num=( selectcount(s.student_id)fromstudents,deletedd wheres.class_id=d.class_id) fromclassc,deleteddwherec.class_id=d.class_id endgoDeclare@NumOfStuentTinyint15運行Select*fromclasswhereclass_id=‘g99403’Deletefromstudentwherestudent_id=‘g9940306’Select*fromclasswhereclass_id=‘g99403’分析執(zhí)行過程運行163.創(chuàng)建更新觸發(fā)器例12-3:為teacher表創(chuàng)建update觸發(fā)器，在教師數(shù)據(jù)變更時自動更新department表的教師人數(shù)。usejwglGo/*如果存在同名的觸發(fā)器，則刪除之*/Ifexits(selectnamefromsysobjectsWheretype=‘TR’ANDname=‘student_update’)DropTRIGGERstudent_updateGo/*建立更新觸發(fā)器*/CreateTRIGGERstudent_updateonteacherForupdateAs3.創(chuàng)建更新觸發(fā)器例12-3:為teacher表創(chuàng)建upda17 updatedepartmentsetteacher_num=( selectcount(t.teacher_id)fromteachert,insertedI wheret.department_id=I.department_id) Fromdepartmentd,insertedI whered.department_id=i.department_id updatedepartmentsetteacher_num=( selectcount(t.teacher_id)fromteachert,deletede wheret.department_id=e.department_id) Fromdepartmentd,deletede whered.department_id=e.department_id go updatedepartmentsetteacher18運行Select*fromdepartmentUpdateteachersetdepartment_id=‘dep_01’ whereteacher_name=‘潘惠’Select*fromdepartment分析執(zhí)行過程Deleted表和inserted表問題：只要對表中的數(shù)據(jù)進(jìn)行更新，這種更新無論是對表中的一行或多行，還是一列或多列，都將執(zhí)行觸發(fā)器。實際應(yīng)用中，可能只關(guān)心特定的列是否被更新。若特定的列被更新，則執(zhí)行觸發(fā)器，否則不執(zhí)行觸發(fā)器：Ifupdate<列名>運行19例12-4：根據(jù)例12-3，使用ifupdate優(yōu)化，只對特定的列更新執(zhí)行觸發(fā)器操作。

usejwglGo/*如果存在同名的觸發(fā)器，則刪除之*/Ifexits(selectnamefromsysobjectsWheretype=‘TR’ANDname=‘student_update’)DropTRIGGERstudent_updateGo/*建立更新觸發(fā)器*/CreateTRIGGERstudent_updateonstudentForupdateAs例12-4：根據(jù)例12-3，使用ifupdate優(yōu)化，只20 /*如果更新teacher表中的department_id列,則執(zhí)行對department表的teacher_num列的更新*/ ifupdate(department) begin updatedepartmentsetteacher_num=( selectcount(t.teacher_id)fromteachert,insertedI wheret.department_id=I.department_id) Fromdepartmentd,insertedI whered.department_id=i.department_id updatedepartmentsetteacher_num=( selectcount(t.teacher_id)fromteachert,deletede wheret.department_id=e.department_id) Fromdepartmentd,deletede whered.department_id=e.department_idendgo /*如果更新teacher表中的department_i21二、使用企業(yè)管理器創(chuàng)建觸發(fā)器在企業(yè)管理器中，展開指定的服務(wù)器和數(shù)據(jù)庫項，然后展開要在其上創(chuàng)建觸發(fā)器的表所在的數(shù)據(jù)庫，用右鍵單擊該表，從彈出的快捷菜單中選擇所有任務(wù)子菜單下的管理觸發(fā)器選項，則會出現(xiàn)觸發(fā)器屬性對話框。在該對話框中，在名稱文本框中選擇新建，然后在文本框中輸入觸發(fā)器文本。單擊“檢查語法”按鈕，則可以檢查語法是否正確。單擊“應(yīng)用”按鈕，則在名稱下拉列表中會有新創(chuàng)建的觸發(fā)器名稱。最后，單擊“確定”按鈕，即可關(guān)閉該對話框，成功創(chuàng)建觸發(fā)器。

舉例二、使用企業(yè)管理器創(chuàng)建觸發(fā)器在企業(yè)管理器中，展開2212.3觸發(fā)器實施數(shù)據(jù)完整性實例實現(xiàn)參照完整性[例12-5]

向teacher_course_class表中添加一行數(shù)據(jù)，檢查所插入數(shù)據(jù)的有效性。確保教師（teacher_id）存在teacher表中，課程(course_id)存在于course表中,班級（class_id）存在于class表中。Createtriggertcc_insertonteacher_course_classForinsertAsIf( (notexists(selectteacher_idfromteacherwhereteacher_idin(selectteacher_idfrominserted)) )or

12.3觸發(fā)器實施數(shù)據(jù)完整性實例實現(xiàn)參照完整性23(notexists(selectcourse_idfromcoursewherecourse_idin

(selectcourse_idfrominserted)) )or(notexists(selectclass_idfromclasswhereclass_idin

(selectclass_idfrominserted))))Begin print‘添加記錄不能完成！’ print‘輸入的教師編號、課程號或班級號有錯誤！’ rollbacktransactionend(notexists(selectcourse_idf2412.3觸發(fā)器實施數(shù)據(jù)完整性實例實施特殊業(yè)務(wù)規(guī)則

[例12-5]

定義teacher表的delete觸發(fā)器，實施“正在授課的教師數(shù)據(jù)不能刪除”這一業(yè)務(wù)規(guī)則。 createtriggerteacher_deleteonteacher fordelete as if(exists(selectteacher_idfromteacher_course_class whereteacher_idin (selectteacher_idfromdeleted) ) ) begin print’刪除記錄不能完成！該教師正為某個班級授課！’ rollbacktransaction end12.3