第12章觸發(fā)器

1、第第1212章章 觸發(fā)器觸發(fā)器 第第1212章章 觸發(fā)器觸發(fā)器l案例案例1 1工號工號姓名姓名級別級別工資工資1喬布斯A 100002比爾A 100003張三B 10004李四C 1005王五C 100級別級別工資工資備注備注A 10000B 1000C 100工號工號姓名姓名級別級別工資工資1喬布斯A 100002比爾A 100003張三B 10004李四B B 1005王五C 100李四晉級后，薪水也需調(diào)整觸發(fā)器可以完成自動調(diào)整第第1212章章 觸發(fā)器觸發(fā)器l案例案例2 2學號學號姓名姓名是否是否畢業(yè)畢業(yè)備注備注1喬布斯否2比爾否3張三否4李四否5王五否學號學號選修課程選修課程成績成績1數(shù)

2、據(jù)庫原理892高數(shù)833數(shù)據(jù)庫原理76刪除學生時，一并刪除學生的成績信息；觸發(fā)器可以完成自動判斷12.112.1觸發(fā)器概述觸發(fā)器概述l觸發(fā)器就像一枚地雷，被特定事件觸發(fā)時，就會發(fā)生一系觸發(fā)器就像一枚地雷，被特定事件觸發(fā)時，就會發(fā)生一系列動作。列動作。l觸發(fā)器觸發(fā)器實際上就是一種特殊類型的存儲過程實際上就是一種特殊類型的存儲過程只有在系統(tǒng)只有在系統(tǒng)執(zhí)行某些特定的執(zhí)行某些特定的T-SQLT-SQL語句或操作時，觸發(fā)器才會自動執(zhí)行語句或操作時，觸發(fā)器才會自動執(zhí)行。12.112.1觸發(fā)器概述觸發(fā)器概述功能功能l觸發(fā)器的常用功能如下：觸發(fā)器的常用功能如下：l1 1完成更復雜的數(shù)據(jù)約束完成更復雜的數(shù)據(jù)約束

3、l2 2檢查所做的檢查所做的SQLSQL所作的操作是否允許所作的操作是否允許l3 3修改其它數(shù)據(jù)表里的數(shù)據(jù)修改其它數(shù)據(jù)表里的數(shù)據(jù)l4 4調(diào)用更多的存儲過程調(diào)用更多的存儲過程l5 5返回自定義的錯誤信息返回自定義的錯誤信息l6 6更改原本要操作的更改原本要操作的SQLSQL語句語句l7 7防止數(shù)據(jù)表結(jié)構被更改或數(shù)據(jù)表被刪除防止數(shù)據(jù)表結(jié)構被更改或數(shù)據(jù)表被刪除l8 8審核和控制服務器會話審核和控制服務器會話12.112.1觸發(fā)器概述觸發(fā)器概述l觸發(fā)器的類型觸發(fā)器的類型l1 1DMLDML觸發(fā)器（觸發(fā)器（ select select、insertinsert、updateupdate、deletede

4、lete觸發(fā)觸發(fā) ） DML DML觸發(fā)器是當數(shù)據(jù)庫服務器中發(fā)生數(shù)據(jù)操作語言事件時觸發(fā)器是當數(shù)據(jù)庫服務器中發(fā)生數(shù)據(jù)操作語言事件時執(zhí)行的存儲過程。執(zhí)行的存儲過程。l2 2DDLDDL觸發(fā)器（觸發(fā)器（ create create 、 drop drop、alter alter 觸發(fā)）觸發(fā)） DDL DDL觸發(fā)器是在響應數(shù)據(jù)定義語言事件時執(zhí)行的存儲過程。觸發(fā)器是在響應數(shù)據(jù)定義語言事件時執(zhí)行的存儲過程。DDLDDL觸發(fā)器一般用于執(zhí)行數(shù)據(jù)庫中管理任務。如審核和規(guī)范觸發(fā)器一般用于執(zhí)行數(shù)據(jù)庫中管理任務。如審核和規(guī)范數(shù)據(jù)庫操作、防止數(shù)據(jù)庫表結(jié)構被修改等。數(shù)據(jù)庫操作、防止數(shù)據(jù)庫表結(jié)構被修改等。l3 3登錄觸發(fā)器

5、登錄觸發(fā)器 登錄觸發(fā)器將為響應登錄觸發(fā)器將為響應 LOGON LOGON 事件而激發(fā)存儲過程。與事件而激發(fā)存儲過程。與 SQL SQL Server Server 實例建立用戶會話時將引發(fā)此事件。實例建立用戶會話時將引發(fā)此事件。12.2 DML12.2 DML觸發(fā)器觸發(fā)器l DMLDML觸發(fā)器的類型觸發(fā)器的類型l1 1AfterAfter觸發(fā)器：觸發(fā)器：這類觸發(fā)器是在記錄已經(jīng)改變之后這類觸發(fā)器是在記錄已經(jīng)改變之后, ,才會被激活執(zhí)行，才會被激活執(zhí)行，它主要是用于它主要是用于記錄變更后的處理或檢查記錄變更后的處理或檢查。l2 2Instead OfInstead Of觸發(fā)器：觸發(fā)器：這類觸發(fā)器一

6、般是用來這類觸發(fā)器一般是用來取代原本要進行的操作取代原本要進行的操作，在記，在記錄變更之前發(fā)生的，它并不去執(zhí)行原來錄變更之前發(fā)生的，它并不去執(zhí)行原來SQLSQL語句里的操作，語句里的操作，而去執(zhí)行觸發(fā)器本身所定義的操作。而去執(zhí)行觸發(fā)器本身所定義的操作。12.2 DML12.2 DML觸發(fā)器觸發(fā)器兩個特殊的系統(tǒng)表兩個特殊的系統(tǒng)表l為了保存為了保存DMLDML操作之前的數(shù)據(jù)和操作之后的數(shù)據(jù)，系統(tǒng)提供操作之前的數(shù)據(jù)和操作之后的數(shù)據(jù)，系統(tǒng)提供了兩個特殊的表。了兩個特殊的表。SQL ServerSQL Server為每個為每個DMLDML觸發(fā)器都定義了兩個特殊的表，一個觸發(fā)器都定義了兩個特殊的表，一個是

7、是INSERTEDINSERTED表表，一個是，一個是DELETEDDELETED表表。這兩個表是建在數(shù)據(jù)庫服務器的內(nèi)存中的，是這兩個表是建在數(shù)據(jù)庫服務器的內(nèi)存中的，是由系統(tǒng)管理由系統(tǒng)管理的邏輯表的邏輯表，而不是真正存儲在數(shù)據(jù)庫中的物理表。對于這，而不是真正存儲在數(shù)據(jù)庫中的物理表。對于這兩個表，用戶只有讀取的權限，沒有修改的權限。兩個表，用戶只有讀取的權限，沒有修改的權限。這兩個表的結(jié)構與觸發(fā)器所在數(shù)據(jù)表的結(jié)構完全一致這兩個表的結(jié)構與觸發(fā)器所在數(shù)據(jù)表的結(jié)構完全一致，當，當觸發(fā)器的工作完成之后，這兩個表也將會從內(nèi)存中刪除。觸發(fā)器的工作完成之后，這兩個表也將會從內(nèi)存中刪除。12.2 DML12.2

8、 DML觸發(fā)器觸發(fā)器兩個特殊的系統(tǒng)表兩個特殊的系統(tǒng)表l對于插入記錄操作來說，對于插入記錄操作來說， INSERTEDINSERTED表里存放的是要插入的表里存放的是要插入的數(shù)據(jù)；數(shù)據(jù)；l對于更新記錄操作來說，對于更新記錄操作來說， INSERTEDINSERTED表里存放的是更新后的表里存放的是更新后的記錄。記錄。l對于更新記錄操作來說，對于更新記錄操作來說， DELETEDDELETED表里存放的是更新前的表里存放的是更新前的記錄；記錄；l對于刪除記錄操作來說，對于刪除記錄操作來說， DELETEDDELETED表里存入的是被刪除的表里存入的是被刪除的舊記錄。舊記錄。12.2 DML12.2

9、 DML觸發(fā)器觸發(fā)器用用T-SQLT-SQL語言創(chuàng)建觸發(fā)器語言創(chuàng)建觸發(fā)器l1 1創(chuàng)建創(chuàng)建AFTERAFTER觸發(fā)器的語法如下：觸發(fā)器的語法如下：CREATE TRIGGERCREATE TRIGGER schema_name.trigger_name schema_name.trigger_nameONON table | view table | view WITH ENCRYPTION|EXECUTE AS CALLER|SELF|WITH ENCRYPTION|EXECUTE AS CALLER|SELF|FOR|FOR|AFTERAFTER INSERT,UPDATE,DELETENO

10、T FOR REPLICATIONNOT FOR REPLICATIONASAS|EXTERNAL NAME assembly method |EXTERNAL NAME specifier12.2 DML12.2 DML觸發(fā)器觸發(fā)器用用T-SQLT-SQL語言創(chuàng)建觸發(fā)器語言創(chuàng)建觸發(fā)器l【例例12.112.1】修改修改TeacherTeacher（教師）表中的數(shù)據(jù)時，通過觸發(fā)（教師）表中的數(shù)據(jù)時，通過觸發(fā)器向客戶端顯示一條消息。器向客戶端顯示一條消息。CREATE TRIGGER teacher_update_messageCREATE TRIGGER teacher_update_messa

11、geON TeacherON TeacherAFTER UPDATE AFTER UPDATE ASASBEGINBEGINRAISERROR (RAISERROR (注意：有人修改教師表中的數(shù)據(jù)！注意：有人修改教師表中的數(shù)據(jù)！,16,10),16,10)ENDENDGOGO12.2 DML12.2 DML觸發(fā)器觸發(fā)器用用T-SQLT-SQL語言創(chuàng)建觸發(fā)器語言創(chuàng)建觸發(fā)器l【例例12.212.2】刪除Teacher（教師）表中的記錄時，通過觸發(fā)器刪除CourseTeacher（課程教師）表中和該教師相關的記錄。CREATE TRIGGER teacher_delete_courseON Teac

12、herAFTER DELETE ASBEGIN DELETE FROM CourseTeacherWHERE teacherID in (SELECT teacherID from deleted)ENDGO12.2 DML12.2 DML觸發(fā)器觸發(fā)器用用T-SQLT-SQL語言創(chuàng)建觸發(fā)器語言創(chuàng)建觸發(fā)器l創(chuàng)建創(chuàng)建INSTEAD OFINSTEAD OF觸發(fā)器觸發(fā)器lInstead OfInstead Of觸發(fā)器是在觸發(fā)器是在SQL ServerSQL Server服務器接到執(zhí)行服務器接到執(zhí)行SQLSQL語句語句請求后，激活請求后，激活Instead OfInstead Of觸發(fā)器，至于該觸發(fā)器

13、，至于該SQLSQL語句本身的操語句本身的操作，系統(tǒng)直接忽略。作，系統(tǒng)直接忽略。12.2 DML12.2 DML觸發(fā)器觸發(fā)器用用T-SQLT-SQL語言創(chuàng)建觸發(fā)器語言創(chuàng)建觸發(fā)器l創(chuàng)建創(chuàng)建INSTEAD OFINSTEAD OF觸發(fā)器的語法代碼如下：觸發(fā)器的語法代碼如下：CREATE TRIGGERCREATE TRIGGER schema_name.trigger_name schema_name.trigger_nameONON table | view table | view WITH ENCRYPTION|EXECUTE AS CALLER|SELF|WITH ENCRYPTION|E

14、XECUTE AS CALLER|SELF| INSTEAD OFINSTEAD OF INSERT,UPDATE,DELETEWITH APPENDWITH APPENDNOT FOR REPLICATIONNOT FOR REPLICATIONASAS|EXTERNAL NAME |EXTERNAL NAME 和和After觸發(fā)器相觸發(fā)器相比，僅將此處改比，僅將此處改為為Instead Of12.2 DML12.2 DML觸發(fā)器觸發(fā)器用用T-SQLT-SQL語言創(chuàng)建觸發(fā)器語言創(chuàng)建觸發(fā)器 【例例12.312.3】當有人試圖修改課程表中的數(shù)據(jù)時，利用觸發(fā)器當有人試圖修改課程表中的數(shù)據(jù)時，利用觸

15、發(fā)器跳過修改數(shù)據(jù)的跳過修改數(shù)據(jù)的SQLSQL語句（防止數(shù)據(jù)被修改），并向客戶端語句（防止數(shù)據(jù)被修改），并向客戶端顯示一條消息。顯示一條消息。lCREATE TRIGGER course_update TRIGGER course_updatelON course courselINSTEAD OF UPDATE OF UPDATE lASlBEGINl RAISERROR (警告：你無權修改課程表中的數(shù)據(jù)！,16,10)lENDlGO12.2 DML12.2 DML觸發(fā)器觸發(fā)器用用T-SQLT-SQL語言創(chuàng)建觸發(fā)器語言創(chuàng)建觸發(fā)器l對觸發(fā)器進行測試：對觸發(fā)器進行測試：lSELECT * * FR

16、OM Course WHERE courseID = 2 FROM Course WHERE courseID = 2lUPDATE Course SET totalperiod = 80 WHERE courseID = 2 Course SET totalperiod = 80 WHERE courseID = 2lSELECT * * FROM Course WHERE courseID = 2 FROM Course WHERE courseID = 2在在“結(jié)果結(jié)果”對話框里顯示了兩個完全一樣的記錄對話框里顯示了兩個完全一樣的記錄說明說明UPDATEUPDATE語句沒有發(fā)生作用。語句

17、沒有發(fā)生作用。12.2 DML12.2 DML觸發(fā)器觸發(fā)器查看觸發(fā)器查看觸發(fā)器l查看查看DMLDML觸發(fā)器有兩種方式：觸發(fā)器有兩種方式：l通用通用Management StudioManagement Studio來查看來查看 選中某一觸發(fā)器，然后雙擊即可查看該觸發(fā)器的信息。選中某一觸發(fā)器，然后雙擊即可查看該觸發(fā)器的信息。l利用系統(tǒng)存儲過程來查看利用系統(tǒng)存儲過程來查看 （1 1）系統(tǒng)存儲過程）系統(tǒng)存儲過程sp_helpsp_help可以了解如觸發(fā)器名稱、類型、可以了解如觸發(fā)器名稱、類型、創(chuàng)建時間等基本信息，其語法格式為：創(chuàng)建時間等基本信息，其語法格式為： sp_help sp_help 觸發(fā)器

18、名觸發(fā)器名 例如：例如：sp_help sp_help teacher_insertteacher_insert12.2 DML12.2 DML觸發(fā)器觸發(fā)器查看觸發(fā)器查看觸發(fā)器l（2 2）sp_helptextsp_helptext系統(tǒng)存儲過程系統(tǒng)存儲過程sp_helptextsp_helptext可以查看觸發(fā)器的文本信息，其語可以查看觸發(fā)器的文本信息，其語法格式為：法格式為： sp_helptext sp_helptext 觸發(fā)器名觸發(fā)器名 例如：例如：sp_helptext teacher_insertsp_helptext teacher_insert12.2 DML12.2 DML觸發(fā)

19、器觸發(fā)器修改觸發(fā)器修改觸發(fā)器l修改觸發(fā)器的語法代碼如下：修改觸發(fā)器的語法代碼如下：ALTER TRIGGERALTER TRIGGER ONON table|view table|viewWITH ENCRYPTION|EXECUTE AS CALLER|SELF|WITH ENCRYPTION|EXECUTE AS CALLER|SELF| FOR|AFTER|INSTEAD OFFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETENOT FOR REPLICATIONNOT FOR REPLICATIONASAS|EXTERNAL NAME |EXTERNAL

20、 NAME 分析上述語法代碼可以發(fā)現(xiàn)，修改觸發(fā)器語法中所涉及到主要參數(shù)和創(chuàng)建分析上述語法代碼可以發(fā)現(xiàn)，修改觸發(fā)器語法中所涉及到主要參數(shù)和創(chuàng)建觸發(fā)器的主要參數(shù)幾乎一樣，在此不再贅述。觸發(fā)器的主要參數(shù)幾乎一樣，在此不再贅述。12.2 DML12.2 DML觸發(fā)器觸發(fā)器修改觸發(fā)器修改觸發(fā)器l1. 1. 禁用禁用DMLDML觸發(fā)器觸發(fā)器使用使用Alter TableAlter Table語句禁用語句禁用DMLDML觸發(fā)器的語法如下：觸發(fā)器的語法如下：Alter tableAlter table 數(shù)據(jù)表名數(shù)據(jù)表名 Disable triggerDisable trigger 觸發(fā)器名或觸發(fā)器名或ALLA

21、LL 如果要禁用所有觸發(fā)器，用如果要禁用所有觸發(fā)器，用“ALLALL”來代替觸發(fā)器名。來代替觸發(fā)器名。l2 2啟用啟用DMLDML觸發(fā)器觸發(fā)器 使用使用Alter TableAlter Table語句也可以啟用觸發(fā)器，其語法如下：語句也可以啟用觸發(fā)器，其語法如下： Alter tableAlter table 數(shù)據(jù)表名數(shù)據(jù)表名 Enable triggerEnable trigger 觸發(fā)器名或觸發(fā)器名或ALLALL 如果要啟用所有觸發(fā)器，用如果要啟用所有觸發(fā)器，用“ALLALL”來代替觸發(fā)器名來代替觸發(fā)器名。12.2 DML12.2 DML觸發(fā)器觸發(fā)器修改觸發(fā)器修改觸發(fā)器l1. 1. 刪除刪

22、除DMLDML觸發(fā)器觸發(fā)器l用用SQLSQL語句也可刪除觸發(fā)器，刪除觸發(fā)器的語法代碼如下所語句也可刪除觸發(fā)器，刪除觸發(fā)器的語法代碼如下所示：示：lDrop Trigger Drop Trigger 觸發(fā)器名觸發(fā)器名l例子例子 刪除觸發(fā)器刪除觸發(fā)器teacher_updateteacher_updatelDrop trigger teacher_updateDrop trigger teacher_update 表1 Student表結(jié)構列名列名說明說明數(shù)據(jù)類型數(shù)據(jù)類型約束約束SnoSno學號字符串，長度為7主屬性主屬性SnameSname姓名字符串，長度為10非空非空SsexSsex性別字符串

23、，長度為2取取男男或或女女SageSage年齡整數(shù)取值取值15154545SdeptSdept所在院系所在院系字符串，長度為字符串，長度為2020默認為默認為計算機系計算機系列名列名說明說明數(shù)據(jù)類型數(shù)據(jù)類型約束約束CnoCno課程號字符串，長度為3主碼主碼CnameCname課程名字符串，長度為20非空非空CcreditCcredit學分整數(shù)取值大于取值大于0 0SemesterSemester學期整數(shù)取值大于取值大于0 0PeriodPeriod學時學時整數(shù)整數(shù)取值大于取值大于0 0表2 Course表結(jié)構小測驗列名列名說明說明數(shù)據(jù)類型數(shù)據(jù)類型約束約束SnoSno學號字符串，長度為7主屬性，

24、引用主屬性，引用StudentStudent的外碼的外碼 Cno Cno課程號字符串，長度為3主屬性，引用主屬性，引用CourseCourse的外碼的外碼GradeGrade成績成績整數(shù)整數(shù)取值取值0 0100100表3 SC表結(jié)構1.創(chuàng)建一個視圖vw_stu_avg：查詢每個學生的平均成績，要求列出學生的姓名、學號、平均成績。2.創(chuàng)建一個唯一索引，該索引依賴于數(shù)據(jù)表Student的Sname字段。3.創(chuàng)建一個名為pro_get_avg的存儲過程，該存儲過程能夠根據(jù)用戶指定的 Cname（課程名）求出該課程的平均成績。執(zhí)行該存儲過程。回顧回顧1.觸發(fā)器的定義，觸發(fā)器和存儲過程的區(qū)別 。2.觸發(fā)

25、器的功能。3.觸發(fā)器的分類。4.觸發(fā)器有哪幾種臨時表，里面存什么內(nèi)容。5.DML觸發(fā)器的分類。6.DML觸發(fā)器中After觸發(fā)器的創(chuàng)建。7.DML觸發(fā)器中Instead of觸發(fā)器的創(chuàng)建。12.3 DDL觸發(fā)器lDDLDDL觸發(fā)器觸發(fā)器像常規(guī)觸發(fā)器一樣，像常規(guī)觸發(fā)器一樣，DDL DDL 觸發(fā)器將激發(fā)存儲過程觸發(fā)器將激發(fā)存儲過程以響應事件。以響應事件。l但與但與DMLDML觸發(fā)器不同的是，它們不會為響應針對表或視圖的觸發(fā)器不同的是，它們不會為響應針對表或視圖的UPDATEUPDATE、INSERTINSERT或或DELETEDELETE語句而激發(fā)。而是為響應以語句而激發(fā)。而是為響應以 CREAT

26、ECREATE、ALTERALTER和和DROPDROP開頭的語句。開頭的語句。12.3 DDL觸發(fā)器一般來說，在以下幾種情況下可以使用一般來說，在以下幾種情況下可以使用DDLDDL觸發(fā)器：觸發(fā)器：（1 1）防止數(shù)據(jù)庫架構進行某些修改。）防止數(shù)據(jù)庫架構進行某些修改。（2 2）防止數(shù)據(jù)庫或數(shù)據(jù)表被誤操作刪除。）防止數(shù)據(jù)庫或數(shù)據(jù)表被誤操作刪除。（3 3）要記錄數(shù)據(jù)庫架構中的更改或事件。）要記錄數(shù)據(jù)庫架構中的更改或事件。僅在運行觸發(fā)僅在運行觸發(fā)DDLDDL觸發(fā)器的觸發(fā)器的DDLDDL語句后，語句后，DDLDDL觸發(fā)器才會激發(fā)。觸發(fā)器才會激發(fā)。DDLDDL觸發(fā)器觸發(fā)器只能作為只能作為AFTERAFTE

27、R觸發(fā)器使用觸發(fā)器使用。12.3 DDL觸發(fā)器創(chuàng)建創(chuàng)建DDLDDL觸發(fā)器的語法代碼如下：觸發(fā)器的語法代碼如下：CREATE TRIGGERCREATE TRIGGER ON ON ALL SERVER|DATABASEALL SERVER|DATABASEWITH ,.nWITH ,.n FOR|AFTERFOR|AFTERevent_type|event_group,.nevent_type|event_group,.nASASsql_statement;.n|EXTERNAL NAME;sql_statement;.n|EXTERNAL NAME;:=:=ENCRYPTIONENCRYPT

28、ION EXECUTE AS ClauseEXECUTE AS Clause :=:=assembly_name.class_name.method_nameassembly_name.class_name.method_name12.3 DDL觸發(fā)器【例例12.412.4】建立用于保護建立用于保護CJGLCJGL數(shù)據(jù)庫中的數(shù)據(jù)表不被刪除的觸發(fā)器。數(shù)據(jù)庫中的數(shù)據(jù)表不被刪除的觸發(fā)器。具體操作步驟如下：具體操作步驟如下：（1 1）啟動）啟動“SQL Server Management StudioSQL Server Management Studio”，在，在“對象資源管理器對象資源管理器”下下

29、選擇選擇“數(shù)據(jù)庫數(shù)據(jù)庫”，定位到，定位到“CJGLCJGL”數(shù)據(jù)庫。數(shù)據(jù)庫。（2 2）單擊）單擊“新建查詢新建查詢”按鈕，在彈出的按鈕，在彈出的“查詢編輯器查詢編輯器”的編輯區(qū)里輸入的編輯區(qū)里輸入以下代碼：以下代碼：CREATE TRIGGER disable_drop_tableCREATE TRIGGER disable_drop_tableON DATABASEON DATABASEFOR DROP_TABLE FOR DROP_TABLE ASASBEGINBEGINRAISERROR (RAISERROR (對不起，不能刪除對不起，不能刪除CJGLCJGL數(shù)據(jù)庫中的數(shù)據(jù)表數(shù)據(jù)庫中的數(shù)據(jù)表,16,10),16,10)ROLLBACKROLLBACKENDENDGOGO（3 3）單擊）單擊“執(zhí)行執(zhí)行”按鈕，生成觸發(fā)器。按鈕，生成觸發(fā)器。12.3 DDL觸發(fā)器測試測試DDLDDL觸發(fā)器的功能，具體操作步驟如下：觸發(fā)器的功能，具體操作步驟如下：（1 1）啟動）啟動“SQL Server Mana