觸發(fā)器的創(chuàng)建和使用

觸發(fā)器的創(chuàng)建和使用第一頁，共三十頁，編輯于2023年，星期五本章學(xué)習(xí)目標(biāo)了解觸發(fā)器和一般存儲(chǔ)過程的主要區(qū)別了解使用觸發(fā)器的優(yōu)點(diǎn)掌握創(chuàng)建觸發(fā)器的方法掌握查看觸發(fā)器信息的方法掌握修改和刪除觸發(fā)器的方法第二頁，共三十頁，編輯于2023年，星期五10.1概述第三頁，共三十頁，編輯于2023年，星期五10.1.1觸發(fā)器的概念觸發(fā)器是一種特殊類型的存儲(chǔ)過程。一般的存儲(chǔ)過程通過存儲(chǔ)過程名稱被直接調(diào)用，而觸發(fā)器主要是通過當(dāng)某個(gè)事件發(fā)生時(shí)自動(dòng)被觸發(fā)執(zhí)行的。觸發(fā)器可以用于SQLServer約束、默認(rèn)值和規(guī)則的完整性檢查，還可以完成難以用普通約束實(shí)現(xiàn)的復(fù)雜功能。第四頁，共三十頁，編輯于2023年，星期五10.1.1觸發(fā)器的概念當(dāng)創(chuàng)建數(shù)據(jù)庫對(duì)象或在數(shù)據(jù)表中插入記錄、修改記錄或者刪除記錄時(shí)，SQLServer就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器和引起觸發(fā)器執(zhí)行的SQL語句被當(dāng)作一次事務(wù)處理，如果這次事務(wù)未獲得成功，SQLServer會(huì)自動(dòng)返回該事務(wù)執(zhí)行前的狀態(tài)。第五頁，共三十頁，編輯于2023年，星期五10.1.2觸發(fā)器的優(yōu)點(diǎn)使用觸發(fā)器有以下優(yōu)點(diǎn)：觸發(fā)器是自動(dòng)的執(zhí)行的。觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進(jìn)行層疊更改。觸發(fā)器可以強(qiáng)制限制。這些限制比用CHECK約束所定義的更復(fù)雜。第六頁，共三十頁，編輯于2023年，星期五10.1.3觸發(fā)器的類型在SQLServer2005中，觸發(fā)器可以分為DML觸發(fā)器和DDL觸發(fā)器兩種，它們的主要區(qū)別如下：DDL觸發(fā)器用于響應(yīng)各種數(shù)據(jù)定義語言(DDL)事件。這些事件主要對(duì)應(yīng)于Transact-SQL中的CREATE、ALTER和DROP語句，以及執(zhí)行類似DDL操作的某些系統(tǒng)存儲(chǔ)過程。它們用于執(zhí)行管理任務(wù)，并強(qiáng)制影響數(shù)據(jù)庫的業(yè)務(wù)規(guī)則。第七頁，共三十頁，編輯于2023年，星期五10.1.3觸發(fā)器的類型DML觸發(fā)器是在用戶使用數(shù)據(jù)操作語言(DML)事件編輯數(shù)據(jù)時(shí)發(fā)生。DML事件是針對(duì)表或視圖的INSERT、UPDATE或DELETE語句。DML觸發(fā)器有助于在表或視圖中修改數(shù)據(jù)時(shí)強(qiáng)制業(yè)務(wù)規(guī)則，擴(kuò)展數(shù)據(jù)完整性。第八頁，共三十頁，編輯于2023年，星期五10.1.3觸發(fā)器的類型DML觸發(fā)器又分為AFTER觸發(fā)器和INSTEADOF觸發(fā)器兩種：AFTER觸發(fā)器：這種類型的觸發(fā)器將在數(shù)據(jù)變動(dòng)（INSERT、UPDATE和DELETE操作）完成以后才被觸發(fā)。AFTER觸發(fā)器只能在表上定義。INSTEADOF觸發(fā)器：INSTEADOF觸發(fā)器將在數(shù)據(jù)變動(dòng)以前被觸發(fā)，并取代變動(dòng)數(shù)據(jù)的操作，而去執(zhí)行觸發(fā)器定義的操作。INSTEADOF觸發(fā)器可以在表或視圖上定義。每個(gè)INSERT、UPDATE和DELETE語句最多定義一個(gè)INSTEADOF觸發(fā)器。第九頁，共三十頁，編輯于2023年，星期五10.2創(chuàng)建觸發(fā)器在創(chuàng)建觸發(fā)器之前應(yīng)該考慮以下幾個(gè)問題：CREATETRIGGER必須是批處理中的第一條語句。觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫中創(chuàng)建。TRUNCATETABLE語句不會(huì)引發(fā)DELETE觸發(fā)器。WRITETEXT語句不會(huì)引發(fā)INSERT或UPDATE觸發(fā)器。第十頁，共三十頁，編輯于2023年，星期五10.2.1使用SQLServer管理控制臺(tái)創(chuàng)建DML觸發(fā)器在SQLServer管理控制臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫，單擊要?jiǎng)?chuàng)建觸發(fā)器的數(shù)據(jù)表，右擊其中的“觸發(fā)器”文件夾。從彈出的快捷菜單中選擇“新建觸發(fā)器”選項(xiàng)，會(huì)出現(xiàn)新建觸發(fā)器模板窗口。第十一頁，共三十頁，編輯于2023年，星期五10.2.1使用SQLServer管理控制臺(tái)創(chuàng)建DML觸發(fā)器當(dāng)創(chuàng)建一個(gè)觸發(fā)器時(shí)必須指定以下幾項(xiàng)內(nèi)容：觸發(fā)器的名稱；在其上定義觸發(fā)器的表；觸發(fā)器將何時(shí)激發(fā)；執(zhí)行觸發(fā)操作的編程語句。第十二頁，共三十頁，編輯于2023年，星期五10.2.1使用SQLServer管理控制臺(tái)創(chuàng)建DML觸發(fā)器例10-2-1：創(chuàng)建一個(gè)INSERT觸發(fā)器“TR_Stu_Insert”，當(dāng)在T_STUDENT表中插入一條新記錄時(shí)，觸發(fā)該觸發(fā)器，并給出“你插入了一條新記錄！”的提示信息。例10-2-2：創(chuàng)建了Stu_Insert觸發(fā)器后，查看向T_STUDENT表中插入數(shù)據(jù)時(shí)，此觸發(fā)器所完成的功能。第十三頁，共三十頁，編輯于2023年，星期五10.2.2使用Transact-SQL語句創(chuàng)建DML觸發(fā)器使用Transact-SQL語言中的CREATETRIGGER命令也可以創(chuàng)建DML觸發(fā)器：CREATETRIGGER[schema_name.]trigger_nameON{table|view}[WITH<dml_trigger_option>[,...n]]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}AS{sql_statement[;][,...n]}<dml_trigger_option>::=[ENCRYPTION][EXECUTEASClause]第十四頁，共三十頁，編輯于2023年，星期五10.2.2使用Transact-SQL語句創(chuàng)建DML觸發(fā)器其中，各參數(shù)的說明如下：schema_name：DML觸發(fā)器所屬架構(gòu)的名稱。trigger_name：觸發(fā)器的名稱。table|view：對(duì)其執(zhí)行DML觸發(fā)器的表或視圖。WITHENCRYPTION：對(duì)CREATETRIGGER語句的文本進(jìn)行加密處理。EXECUTEAS：指定用于執(zhí)行該觸發(fā)器的安全上下文。第十五頁，共三十頁，編輯于2023年，星期五10.2.2使用Transact-SQL語句創(chuàng)建DML觸發(fā)器FOR|AFTER：AFTER指定觸發(fā)器僅在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行時(shí)才被觸發(fā)。不能對(duì)視圖定義AFTER觸發(fā)器。INSTEADOF：指定執(zhí)行DML觸發(fā)器而不是觸發(fā)SQL語句。{[DELETE][,][INSERT][,][UPDATE]}：指定數(shù)據(jù)修改語句。必須至少指定一個(gè)選項(xiàng)。sql_statement：觸發(fā)條件和操作。第十六頁，共三十頁，編輯于2023年，星期五10.2.2使用Transact-SQL語句創(chuàng)建DML觸發(fā)器DML觸發(fā)器可以使用兩個(gè)特殊的臨時(shí)表，它們分別是inserted表和deleted表。這兩個(gè)表都存在于內(nèi)存中。它們?cè)诮Y(jié)構(gòu)上類似于定義了觸發(fā)器的表。在inserted表中存儲(chǔ)著被INSERT和UPDATE語句影響的新的數(shù)據(jù)行。在執(zhí)行INSERT或UPDATE語句時(shí)，新的數(shù)據(jù)行被添加到基本表中，同時(shí)這些數(shù)據(jù)行的備份被復(fù)制到inserted臨時(shí)表中。第十七頁，共三十頁，編輯于2023年，星期五10.2.2使用Transact-SQL語句創(chuàng)建DML觸發(fā)器在deleted表中存儲(chǔ)著被DELETE和UPDATE語句影響的舊數(shù)據(jù)行。在執(zhí)行DELETE或UPDATE語句時(shí)，指定的數(shù)據(jù)行從基本表中刪除，然后被轉(zhuǎn)移到了deleted表中。在基本表和deleted表中一般不會(huì)存在相同的數(shù)據(jù)行。一個(gè)UPDATE操作實(shí)際上是由一個(gè)DELETE操作和一個(gè)INSERT操作組成的。在執(zhí)行UPDATE操作時(shí)，舊的數(shù)據(jù)行從基本表中轉(zhuǎn)移到deleted表中，然后將新的數(shù)據(jù)行同時(shí)插入基本表和inserted表中。第十八頁，共三十頁，編輯于2023年，星期五10.2.2使用Transact-SQL語句創(chuàng)建DML觸發(fā)器例10-2-3：創(chuàng)建一個(gè)AFTER觸發(fā)器，要求實(shí)現(xiàn)以下功能：在T_SCORE表上創(chuàng)建一個(gè)插入、更新類型的觸發(fā)器TR_ScoreCheck，當(dāng)在SCORE字段中插入或修改考試分?jǐn)?shù)后，觸發(fā)該觸發(fā)器，檢查分?jǐn)?shù)是否在0-100之間。例10-2-4：創(chuàng)建一個(gè)AFTER觸發(fā)器，要求如下：在T_STUDENT表上創(chuàng)建一個(gè)刪除類型的觸發(fā)器TR_Stu_Delete，當(dāng)在T_STUDENT表中刪除某一條記錄后，觸發(fā)該觸發(fā)器，在T_SCORE表中刪除與此學(xué)號(hào)對(duì)應(yīng)的記錄。第十九頁，共三十頁，編輯于2023年，星期五10.2.2使用Transact-SQL語句創(chuàng)建DML觸發(fā)器例10-2-5：創(chuàng)建一個(gè)INSTEADOF觸發(fā)器，要求實(shí)現(xiàn)以下功能：在T_COURSE表上創(chuàng)建一個(gè)刪除類型的觸發(fā)器TR_NotAllowDelete，當(dāng)在T_COURSE表中刪除記錄時(shí)，觸發(fā)該觸發(fā)器，顯示不允許刪除表中數(shù)據(jù)的提示信息。第二十頁，共三十頁，編輯于2023年，星期五10.3查看、修改和刪除觸發(fā)器第二十一頁，共三十頁，編輯于2023年，星期五10.3.1查看并修改觸發(fā)器1．使用SQLServer管理控制臺(tái)查看并修改觸發(fā)器（1）查看并修改觸發(fā)器定義信息在SQLServer管理控制臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫，選擇指定的數(shù)據(jù)庫和表，單擊要查看的表，單擊其中的“觸發(fā)器”文件夾，此時(shí)會(huì)在右側(cè)的“摘要”窗口中看到此表中的所有觸發(fā)器，右擊某個(gè)觸發(fā)器名稱，從彈出的快捷菜單中選擇“修改”選項(xiàng)，在打開的窗口中可以查看到定義觸發(fā)器的語句，在窗口中也可以直接修改觸發(fā)器的定義。第二十二頁，共三十頁，編輯于2023年，星期五10.3.1查看并修改觸發(fā)器（2）查看與觸發(fā)器有依賴關(guān)系的其他數(shù)據(jù)庫對(duì)象右擊某個(gè)觸發(fā)器名稱，從彈出的快捷菜單中選擇“查看依賴關(guān)系”選項(xiàng)，在出現(xiàn)的“對(duì)象依賴關(guān)系”對(duì)話框中可以查看到依賴于此觸發(fā)器的對(duì)象和此觸發(fā)器依賴的對(duì)象。第二十三頁，共三十頁，編輯于2023年，星期五10.3.1查看并修改觸發(fā)器2．使用系統(tǒng)存儲(chǔ)過程查看觸發(fā)器可以使用系統(tǒng)存儲(chǔ)過程sp_help、sp_helptext和sp_depends分別查看觸發(fā)器的不同信息。它們的具體用途和語法形式如下：sp_help：用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時(shí)間。sp_help‘觸發(fā)器名稱’sp_helptext：用于查看觸發(fā)器的正文信息。sp_helptext‘觸發(fā)器名稱’第二十四頁，共三十頁，編輯于2023年，星期五10.3.1查看并修改觸發(fā)器sp_depends：用于查看指定觸發(fā)器所引用的表。sp_depends‘觸發(fā)器名稱’例10-3-1：使用系統(tǒng)存儲(chǔ)過程查看TR_Stu_Insert觸發(fā)器的一般信息。例10-3-2：使用系統(tǒng)存儲(chǔ)過程查看T_STUDENT表涉及到的所有觸發(fā)器。第二十五頁，共三十頁，編輯于2023年，星期五10.3.2刪除觸發(fā)器1．使用SQLServer管理控制臺(tái)刪除觸發(fā)器在SQLServer管理控制臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫，選擇指定的數(shù)據(jù)庫和表，單擊要查看的表，單擊其中的“觸發(fā)器”文件夾，此時(shí)會(huì)在右側(cè)的“摘要”窗口中看到此表中的所有觸發(fā)器，右擊某個(gè)觸發(fā)器名稱，從彈出的快捷菜單中選擇“刪除”選項(xiàng)，會(huì)彈出“刪除對(duì)象”對(duì)話框，單擊“確定”按鈕，即可刪除該觸發(fā)器。第二十六頁，共三十頁，編輯于2023年，星期五10.3.2刪除觸發(fā)器2．使用Transact-SQL語句刪除觸發(fā)器使用系統(tǒng)命令DROPTRIGGER刪除指定的觸發(fā)器。其語法形式如下：DROPTRIGGER{trigger}[,...n]例10-3-3：使用系統(tǒng)命令刪除TR_Stu_Insert觸發(fā)器。第二十七頁，共三十頁，編輯于2023年，星期五10.3.2刪除觸發(fā)器3．直接刪除觸發(fā)器所在的表刪除觸發(fā)器的另一個(gè)方法是直接刪除觸發(fā)器所在的表。刪除表時(shí)，SQLServer將會(huì)自動(dòng)刪除與該表相關(guān)的所有觸發(fā)器。第二十八頁，共三十頁，編輯于2023年，星期五10