第12章觸發器

1、第第1212章章 觸發器觸發器 第第1212章章 觸發器觸發器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李四晉級后，薪水也需調整觸發器可以完成自動調整第第1212章章 觸發器觸發器l案例案例2 2學號學號姓名姓名是否是否畢業畢業備注備注1喬布斯否2比爾否3張三否4李四否5王五否學號學號選修課程選修課程成績成績1數

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

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

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

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

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

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

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

9、 DML觸發器觸發器用用T-SQLT-SQL語言創建觸發器語言創建觸發器l1 1創建創建AFTERAFTER觸發器的語法如下：觸發器的語法如下：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觸發器觸發器用用T-SQLT-SQL語言創建觸發器語言創建觸發器l【例例12.112.1】修改修改TeacherTeacher（教師）表中的數據時，通過觸發（教師）表中的數據時，通過觸發器向客戶端顯示一條消息。器向客戶端顯示一條消息。CREATE TRIGGER teacher_update_messageCREATE TRIGGER teacher_update_messa

11、geON TeacherON TeacherAFTER UPDATE AFTER UPDATE ASASBEGINBEGINRAISERROR (RAISERROR (注意：有人修改教師表中的數據！注意：有人修改教師表中的數據！,16,10),16,10)ENDENDGOGO12.2 DML12.2 DML觸發器觸發器用用T-SQLT-SQL語言創建觸發器語言創建觸發器l【例例12.212.2】刪除Teacher（教師）表中的記錄時，通過觸發器刪除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觸發器觸發器用用T-SQLT-SQL語言創建觸發器語言創建觸發器l創建創建INSTEAD OFINSTEAD OF觸發器觸發器lInstead OfInstead Of觸發器是在觸發器是在SQL ServerSQL Server服務器接到執行服務器接到執行SQLSQL語句語句請求后，激活請求后，激活Instead OfInstead Of觸發器，至于該觸發器

13、，至于該SQLSQL語句本身的操語句本身的操作，系統直接忽略。作，系統直接忽略。12.2 DML12.2 DML觸發器觸發器用用T-SQLT-SQL語言創建觸發器語言創建觸發器l創建創建INSTEAD OFINSTEAD OF觸發器的語法代碼如下：觸發器的語法代碼如下：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觸發器相觸發器相比，僅將此處改比，僅將此處改為為Instead Of12.2 DML12.2 DML觸發器觸發器用用T-SQLT-SQL語言創建觸發器語言創建觸發器 【例例12.312.3】當有人試圖修改課程表中的數據時，利用觸發器當有人試圖修改課程表中的數據時，利用觸

15、發器跳過修改數據的跳過修改數據的SQLSQL語句（防止數據被修改），并向客戶端語句（防止數據被修改），并向客戶端顯示一條消息。顯示一條消息。lCREATE TRIGGER course_update TRIGGER course_updatelON course courselINSTEAD OF UPDATE OF UPDATE lASlBEGINl RAISERROR (警告：你無權修改課程表中的數據！,16,10)lENDlGO12.2 DML12.2 DML觸發器觸發器用用T-SQLT-SQL語言創建觸發器語言創建觸發器l對觸發器進行測試：對觸發器進行測試：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在在“結果結果”對話框里顯示了兩個完全一樣的記錄對話框里顯示了兩個完全一樣的記錄說明說明UPDATEUPDATE語句沒有發生作用。語句

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

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

19、器觸發器修改觸發器修改觸發器l修改觸發器的語法代碼如下：修改觸發器的語法代碼如下：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 分析上述語法代碼可以發現，修改觸發器語法中所涉及到主要參數和創建分析上述語法代碼可以發現，修改觸發器語法中所涉及到主要參數和創建觸發器的主要參數幾乎一樣，在此不再贅述。觸發器的主要參數幾乎一樣，在此不再贅述。12.2 DML12.2 DML觸發器觸發器修改觸發器修改觸發器l1. 1. 禁用禁用DMLDML觸發器觸發器使用使用Alter TableAlter Table語句禁用語句禁用DMLDML觸發器的語法如下：觸發器的語法如下：Alter tableAlter table 數據表名數據表名 Disable triggerDisable trigger 觸發器名或觸發器名或ALLA

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

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

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

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

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

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

27、R觸發器使用觸發器使用。12.3 DDL觸發器創建創建DDLDDL觸發器的語法代碼如下：觸發器的語法代碼如下：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觸發器【例例12.412.4】建立用于保護建立用于保護CJGLCJGL數據庫中的數據表不被刪除的觸發器。數據庫中的數據表不被刪除的觸發器。具體操作步驟如下：具體操作步驟如下：（1 1）啟動）啟動“SQL Server Management StudioSQL Server Management Studio”，在，在“對象資源管理器對象資源管理器”下下

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