工作任務(wù)10 創(chuàng)建與使用觸發(fā)器

1、SQL Server 應(yīng)用系統(tǒng)開發(fā)應(yīng)用系統(tǒng)開發(fā)計(jì)算機(jī)與信息工程系計(jì)算機(jī)與信息工程系工作任務(wù)工作任務(wù)10 創(chuàng)建與使用觸發(fā)器創(chuàng)建與使用觸發(fā)器12. 1 在應(yīng)用系統(tǒng)中使用觸發(fā)器在應(yīng)用系統(tǒng)中使用觸發(fā)器?12. 2 在應(yīng)用系統(tǒng)中創(chuàng)建觸發(fā)器在應(yīng)用系統(tǒng)中創(chuàng)建觸發(fā)器12. 3 觸發(fā)器實(shí)施數(shù)據(jù)完整性實(shí)例觸發(fā)器實(shí)施數(shù)據(jù)完整性實(shí)例12. 4 查看、修改和刪除觸發(fā)器查看、修改和刪除觸發(fā)器12. 5 使用觸發(fā)器的注意事項(xiàng)使用觸發(fā)器的注意事項(xiàng)返回目錄返回目錄3學(xué)號 班號 姓名 住址1AA1ASD2AA2SDF3BB1DER4CC1FGHStudentClass班號人數(shù)A2B3C4Insert5CD1DFGDeleteUp

2、date在前面章節(jié)中在前面章節(jié)中,王浩同學(xué)設(shè)計(jì)的應(yīng)用系統(tǒng)王浩同學(xué)設(shè)計(jì)的應(yīng)用系統(tǒng),有以下數(shù)據(jù)有以下數(shù)據(jù)TABLE:問題?12. 1 在應(yīng)用系統(tǒng)中使用在應(yīng)用系統(tǒng)中使用觸發(fā)器觸發(fā)器?解決辦法解決辦法-在應(yīng)用系統(tǒng)中使用觸發(fā)器在應(yīng)用系統(tǒng)中使用觸發(fā)器! 在對某個表進(jìn)行數(shù)據(jù)插入、修改、在對某個表進(jìn)行數(shù)據(jù)插入、修改、刪除時刪除時,觸發(fā)一定的動作去保證其他表的觸發(fā)一定的動作去保證其他表的相關(guān)數(shù)據(jù)的正確性相關(guān)數(shù)據(jù)的正確性.512. 1. 1 觸發(fā)器的概念及分類觸發(fā)器的概念及分類 觸發(fā)器是一種特殊類型的存儲過程，它不同于前面介紹過的一般的存儲過程。 一般的存儲過程通過存儲過程名稱被直接調(diào)用，如: exec spad

3、dstudent觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行的一段服務(wù)器程序,不能被直接調(diào)用.6 觸發(fā)器（Trigger）在對表或視圖執(zhí)行UPDATE、INSERT 或 DELETE 語句時自動觸發(fā)執(zhí)行自動觸發(fā)執(zhí)行，以防止對數(shù)據(jù)進(jìn)行不正確、未授權(quán)或不一致的修改。 對表中數(shù)據(jù)的操作有三種基本類型，數(shù)據(jù)插入、修改、刪除，因此，觸發(fā)器也有三種類型：觸發(fā)器也有三種類型：INSERT、UPDATE、DELETE。觸發(fā)器三種類型觸發(fā)器三種類型712.1.2 觸發(fā)器的工作原理觸發(fā)器的工作原理 1. INSERT觸發(fā)器的工作原理觸發(fā)器的工作原理 關(guān)鍵: 臨時表 inserted學(xué)號 班號 姓名 住址1AA1ASD2A

4、A2SDF3BB1DER4CC1FGHStudentClass班號人數(shù)A2B3C4Insert5CD1DFG5CD1DFG學(xué)號班號姓名住址inserted維護(hù)8學(xué)號 班號 姓名 住址1AA1ASD2AA2SDF3BB1DER4CC1FGHStudentClass班號人數(shù)A2B3C4deletedeleted維護(hù)2. DELETE觸發(fā)器的工作原理觸發(fā)器的工作原理 關(guān)鍵: deleted臨時表學(xué)號班號姓名住址4CC1FGH93. UPDATE觸發(fā)器的工作原理觸發(fā)器的工作原理 修改: 刪除一條舊記錄,插入一條新記錄. 關(guān)鍵: deleted表和inserted表學(xué)號 班號 姓名 住址1AA1ASD2

5、AA2SDF3BB1DER4CC1FGHStudentClass班號人數(shù)A2B3C4Insert4BD14BD1DFG學(xué)號班號姓名住址inserted維護(hù)deleted學(xué)號班號姓名住址4CC1FGHDFG維護(hù)delete1012.2 觸發(fā)器的創(chuàng)建12.2.1 使用使用T-SQL語句創(chuàng)建觸發(fā)器語句創(chuàng)建觸發(fā)器 1. 創(chuàng)建觸發(fā)器的創(chuàng)建觸發(fā)器的SQL語句語法：語句語法：CREATE TRIGGER trigger_name ON table_name WITH ENCRYPTION FOR DELETE,INSERT,UPDATEAS sql_statement11 trigger_name ：要創(chuàng)建

6、的觸發(fā)器名稱。 table_name ：指定所創(chuàng)建的觸發(fā)器與之相關(guān)聯(lián)的表名。必須是一個現(xiàn)存的表。 WITH ENCRYPTION ：加密創(chuàng)建觸發(fā)器的文本。 FOR INSERT ,DELETE ,UPDATE ：指定所創(chuàng)建的觸發(fā)器將在發(fā)生哪些事件時被觸發(fā)，也即指定創(chuàng)建觸發(fā)器的類型如果指定的選項(xiàng)多于一個，以逗號分隔這些選項(xiàng)。 sql_statement ：指定觸發(fā)器執(zhí)行的SQL語句。12 2. 創(chuàng)建創(chuàng)建INSERT觸發(fā)器觸發(fā)器 當(dāng)某個班級增加一名學(xué)生，即向表student中插入一行數(shù)據(jù)時，需要更改該學(xué)生所在班級的記錄，以增加該班級的學(xué)生總?cè)藬?shù)。下例使用INSERT觸發(fā)器自動完成這個工作。【例【例

7、10-1】為student表建立INSERT觸發(fā)器以自動更新class表學(xué)生人數(shù)。USE jwglGO13CREATE TRIGGER student_insert ON student FOR INSERTASDECLARE x TINYINTSELECT x= COUNT(s.student_id)FROM student s ,inserted i WHERE s.class_id = i.class_id UPDATE c SET student_num = x FROM class c, inserted i WHERE c.class_id = i.class_idGO14 運(yùn)行插

8、入觸發(fā)器: (1).首先查詢一個班級的當(dāng)前人數(shù)首先查詢一個班級的當(dāng)前人數(shù)， SELECT * FROM class WHERE class_id = g99403系統(tǒng)顯示如下運(yùn)行結(jié)果：class_id monitor classroom student_num g99403 成佳洱 教學(xué)樓212 5當(dāng)前g99403班級當(dāng)前的學(xué)生人數(shù)是5人15 (2). 添加學(xué)生記錄添加學(xué)生記錄： insert into student values( g9940306,程濤,男,1981-9-22,g99403,1999-9-1,南京市御道街2號) (3).再一次使用前面的語句查詢再一次使用前面的語句查詢g9

9、9403班級的信息，可以班級的信息，可以看到下面的結(jié)果顯示看到下面的結(jié)果顯示：class_id monitor classroom student_num g99403 成佳洱 教學(xué)樓212 6 class表中該班級記錄的student_num字段已經(jīng)自動更新為6。16 class表表student_num列自動更新的原因列自動更新的原因： 在student表上執(zhí)行了INSERT操作后，觸發(fā)了插入觸發(fā)器student_insert，該觸發(fā)器自動對學(xué)生人數(shù)進(jìn)行累加（或重新統(tǒng)計(jì)）,然后寫入class表。 從上述例子可以看出，通過觸發(fā)器可以維護(hù)數(shù)據(jù)完整性。17 3. 創(chuàng)建創(chuàng)建DELETE觸發(fā)器觸發(fā)器

10、【例【例10-2】為student表建立DELETE觸發(fā)器，在刪除學(xué)生記錄時自動更新class表中相應(yīng)班級的學(xué)生人數(shù)。USE jwglGO CREATE TRIGGER student_delete ON studentFOR DELETEASDECLARE x TINYINTSELECT x=COUNT(s.student_id) FROM student s ,deleted d WHERE s.class_id = d.class_id18UPDATE class SET student_num = x FROM class c, deleted d WHERE c.class_id =

11、 d.class_idGO19 4. 創(chuàng)建創(chuàng)建UPDATE觸發(fā)器觸發(fā)器 在實(shí)際的教務(wù)管理中，可能會有教師在各個院系之間進(jìn)行工作調(diào)動，一方面需要更新teacher表中相關(guān)教師的department_id列的內(nèi)容；另一方面，一個教師從一個系調(diào)動到另一個系，會影響兩個系的教師總?cè)藬?shù)，這就需要更新這兩個系的教師總?cè)藬?shù)?，F(xiàn)在來為teacher表建立一個更新觸發(fā)器，實(shí)現(xiàn)系信息表department中相關(guān)列的自動更新?！纠纠?0-3】為teacher表建立UPDATE觸發(fā)器，在教師數(shù)據(jù)變更時自動更新department表的教師人數(shù)。USE jwglGO20CREATE TRIGGER teacher_up

12、date ON teacherFOR UPDATEASUPDATE department SET teacher_num = (SELECT COUNT(t.teacher_id) FROM teacher t , inserted iWHERE t.department_id = i.department_id )FROM department d , inserted iWHERE d.department_id = i.department_idUPDATE department SET teacher_num = (SELECT COUNT ( t.teacher_id ) FROM

13、teacher t , deleted eWHERE t.department_id = e.department_id )FROM department d , deleted eWHERE d.department_id = e.department_idGO21 檢驗(yàn)修改觸發(fā)器: (1). 首先看看當(dāng)前department表中各系的數(shù)據(jù)，執(zhí)行 SELECT * FROM department 語句，語句，系統(tǒng)顯示的執(zhí)行結(jié)果如下：department_id department_name department_header teacher_num dep_01 無線電無線電 王敬遠(yuǎn)王敬遠(yuǎn)

14、1dep_02 通信與信息工程通信與信息工程 康輝康輝 1dep_03 電子工程電子工程 董一平董一平 2dep_04 計(jì)算機(jī)科學(xué)計(jì)算機(jī)科學(xué) 紀(jì)云紀(jì)云 5 (2). 然后我們修改一個教師的department_id，然后再查詢department表的數(shù)據(jù)： UPDATE teacher SET department_id = dep_01 WHERE teacher_name = 潘惠潘惠22 (3). SELECT * FROM department 結(jié)果如下：department_id department_name department_header teacher_num dep_01

15、 無線電無線電 王敬遠(yuǎn)王敬遠(yuǎn) 2dep_02 通信與信息工程通信與信息工程 康輝康輝 1dep_03 電子工程電子工程 董一平董一平 1dep_04 計(jì)算機(jī)科學(xué)計(jì)算機(jī)科學(xué) 紀(jì)云紀(jì)云 5 現(xiàn)在，由于一個教師從“電子工程”系調(diào)到了“無線電”系，“無線電”系的教師人數(shù)增加為2，而“電子工程”系的教師人數(shù)減少為1。這個UPDATE觸發(fā)器的功能實(shí)現(xiàn)了。23 從整體性能方面考慮，需要注意一個問題：注意一個問題： 一個表的更新觸發(fā)器一旦建立，那么只要對該表中的數(shù)據(jù)進(jìn)行更新，這種更新無論是對表中的一行或多行，還是一列或多列，都將執(zhí)行觸發(fā)器操作。而在實(shí)際應(yīng)用中，可能只關(guān)心對特定列是否被更新，比如上面例子中我們只

16、關(guān)心teacher表的department_id是否更新，如果特定的列被更新，則執(zhí)行觸發(fā)器操作，否則不執(zhí)行觸發(fā)器操作。 在設(shè)計(jì)更新觸發(fā)器時可以通過IF UPDATE 來實(shí)現(xiàn)。在同一個觸發(fā)器的定義語句中，可以使用多個IF UPDATE語句來對不同的列的修改執(zhí)行不同的觸發(fā)器操作。24【例【例10-4】根據(jù)示例3，使用IF UPDATE進(jìn)行優(yōu)化，只對teacher表特定列的更新作出觸發(fā)器操作。USE jwglGO/* 建立更新觸發(fā)器 */CREATE TRIGGER teacher_update ON teacherFOR UPDATEAS/* 如果更新teacher表的department_id列

17、，則執(zhí)行對department表teacher_num列的更新* /25IF UPDATE (department_id)BEGIN UPDATE department SET teacher_num = ( SELECT COUNT(t.teacher_id) FROM teacher t , inserted iWHERE t.department_id = i.department_id ) FROM department d , inserted i WHERE d.department_id = i.department_id UPDATE department SET teache

18、r_num = ( SELECT COUNT(t.teacher_id) FROM teacher t , deleted eWHERE t.department_id = e.department_id ) FROM department d , deleted e WHERE d.department_id = e.department_idENDGO2612.2.2 使用企業(yè)管理器創(chuàng)建觸發(fā)器使用企業(yè)管理器創(chuàng)建觸發(fā)器 在企業(yè)管理器中使用以下步驟創(chuàng)建觸發(fā)器： 展開服務(wù)器組，然后展開服務(wù)器。 展開“數(shù)據(jù)庫”文件夾，展開含觸發(fā)器的表所屬的數(shù)據(jù)庫（例如：教務(wù)管理數(shù)據(jù)庫JWGL），然后單擊“表”文件

19、夾。 在詳細(xì)信息窗格中，右擊將在其上創(chuàng)建觸發(fā)器的表（如student），指向“所有任務(wù)”菜單，然后單擊“管理觸發(fā)器”命令。如圖12-2所示。 圖12-2 觸發(fā)器屬性界面 在“名稱”中，單擊 。 在“文本”框中顯示了創(chuàng)建觸發(fā)器的關(guān)鍵T-SQL語句，輸入和修改觸發(fā)器的文本。 若要檢查語法，單擊“檢查語法”命令。 點(diǎn)擊“確定”完成創(chuàng)建，點(diǎn)擊“關(guān)閉”關(guān)閉對話框。 創(chuàng)建觸發(fā)器時，一般是在查詢分析器中直接使用創(chuàng)建觸發(fā)器的語法來定義觸發(fā)器。本節(jié)后面的內(nèi)容都是在查詢分析器中完成的。2712.3 觸發(fā)器實(shí)施數(shù)據(jù)完整性實(shí)例12.3.1 實(shí)現(xiàn)參照完整性實(shí)現(xiàn)參照完整性 觸發(fā)器能夠維持兩個表間的參照完整性（Refere

20、ntial Integrity），就像外鍵一樣。下面一個例子說明了觸發(fā)器如何實(shí)現(xiàn)參照完整性。【例【例10-5】向teacher_course_class表添加一行數(shù)據(jù)，檢查所插入數(shù)據(jù)的有效性。確保教師（teacher_id）存在teacher表中，課程（course_id）存在于course表中，而班級（class_id）必須存在于class表中。使用下面的代碼來定義teacher_course_class表INSERT觸發(fā)器：CREATE TRIGGER tcc_insert ON teacher_course_classFOR INSERTAS28IF (NOT EXISTS ( SELE

21、CT teacher_id FROM teacher WHERE teacher_id IN ( SELECT teacher_id FROM inserted ) ) OR( NOT EXISTS ( SELECT course_id FROM course WHERE course_id IN ( SELECT course_id FROM inserted ) ) OR( NOT EXISTS ( SELECT class_id FROM class WHERE class_id IN ( SELECT class_id FROM inserted ) )BEGIN PRINT 添加記錄

22、操作不能完成！ PRINT 輸入的教師編號、課程編號或班級編號有錯誤。 ROLLBACK TRANSACTIONEND29 當(dāng)tcc_insert觸發(fā)器被觸發(fā)時，將檢查teacher、course和class表，測試inserted臨時表中的teacher_id、course_id和class_id是否存在這三個表中。如果某一項(xiàng)不存在則撤消添加操作。12.3.2 實(shí)施特殊業(yè)務(wù)規(guī)則實(shí)施特殊業(yè)務(wù)規(guī)則 實(shí)現(xiàn)一些特殊業(yè)務(wù)規(guī)則是觸發(fā)器運(yùn)用的一個重要方面。例如，如果一個教師正在為某些班級的課程授課，則不能刪除這個教師的數(shù)據(jù)。【例【例12-6】定義teacher表的DELETE觸發(fā)器，實(shí)施“正在授課的教師數(shù)

23、據(jù)不能刪除”這個業(yè)務(wù)規(guī)則：CREATE TRIGGER teacher_delete ON teacherFOR DELETEASIF ( EXISTS ( SELECT teacher_id FROM teacher_course_class 30WHERE teacher_id IN ( SELECT teacher_id FROM deleted )BEGIN PRINT 刪除記錄操作不能完成！ PRINT 該教師正在為一些班級授課。 ROLLBACK TRANSACTIONENDGO3112.4 查看、修改和刪除觸發(fā)器12.4.1 查看觸發(fā)器信息查看觸發(fā)器信息 1. 使用使用T-SQL

24、語句查看觸發(fā)器信息語句查看觸發(fā)器信息 查看表中的觸發(fā)器信息 使用系統(tǒng)存儲過程sp_helptrigger可以查看指定表中所定義的觸發(fā)器及它們的類型。例如，要查看student表中觸發(fā)器信息，可使用下列語句： EXEC sp_helptrigger student32 查看觸發(fā)器定義查看觸發(fā)器定義 使用系統(tǒng)存儲過程sp_helptext可以查看指定觸發(fā)器的定義文本。 EXEC sp_helptext student_insert 查看觸發(fā)器的相關(guān)性查看觸發(fā)器的相關(guān)性 使用系統(tǒng)存儲過程sp_depends可以查看指定觸發(fā)器的相關(guān)性，了解觸發(fā)器所依賴的表或視圖。 EXEC sp_depends st

25、udent_delete33 2. 使用企業(yè)管理器查看觸發(fā)器相關(guān)信息使用企業(yè)管理器查看觸發(fā)器相關(guān)信息 使用企業(yè)管理器查看表中的觸發(fā)器信息及觸發(fā)器的定義文本 展開服務(wù)器組，然后展開服務(wù)器。 展開“數(shù)據(jù)庫”文件夾，展開含觸發(fā)器的表所屬的數(shù)據(jù)庫，然后單擊“表”文件夾。 在詳細(xì)信息窗格中，右擊觸發(fā)器所在的表，指向“所有任務(wù)”菜單，然后單擊“管理觸發(fā)器”命令。 展開“名稱”下拉框即可查看該表上的觸發(fā)器，選擇一觸發(fā)器即可在“文本”區(qū)顯示該觸發(fā)器的定義文本。 使用企業(yè)管理器查看的相關(guān)性 在企業(yè)管理器中，使用以下步驟來查看觸發(fā)器的相關(guān)性： 展開服務(wù)器組，然后展開服務(wù)器。 展開“數(shù)據(jù)庫”文件夾，展開觸發(fā)器所屬的

26、數(shù)據(jù)庫，然后單擊“表”文件夾。 在詳細(xì)信息窗格中，右擊觸發(fā)器所屬的表，指向“所有任務(wù)”菜單，然后單擊“顯示相關(guān)性”命令。 在“對象”中，單擊要查看其相關(guān)性的觸發(fā)器。 3412.4.2 修改觸發(fā)器修改觸發(fā)器 1. 使用使用T-SQL語句修改觸發(fā)器語句修改觸發(fā)器 使用T-SQL語句ALTER TRIGGER可以修改觸發(fā)器，具體語法形式如下：ALTER TRIGGER trigger_name ON tablename WITH ENCRYPTION FOR DELETE , INSERT , UPDATE AS sql_statement35 2. 使用企業(yè)管理器修改觸發(fā)器使用企業(yè)管理器修改觸發(fā)器

27、 使用企業(yè)管理器對觸發(fā)器進(jìn)行修改的具體步驟如下： 展開服務(wù)器組，然后展開服務(wù)器。 展開“數(shù)據(jù)庫”文件夾，展開含觸發(fā)器的表所屬的數(shù)據(jù)庫，然后單擊“表”文件夾。 在詳細(xì)信息窗格中，右擊觸發(fā)器所在的表，指向“所有任務(wù)”菜單，然后單擊“管理觸發(fā)器”命令。 在“名稱”框中選擇觸發(fā)器的名稱。 按需要在“文本”字段中更改觸發(fā)器的文本。 若要檢查觸發(fā)器的語法，單擊“檢查語法”命令。 點(diǎn)擊“確定”按鈕。3612.4.3 刪除觸發(fā)器刪除觸發(fā)器 1. 使用使用T-SQL語句刪除觸發(fā)器語句刪除觸發(fā)器 使用DROP TRIGGER語句可以從當(dāng)前數(shù)據(jù)庫某個表中刪除一個或多個觸發(fā)器。其語法如下所示： DROP TRIGGER trigger ,.n 參數(shù)： trigger ： 要刪除的觸發(fā)器名稱。 n ： 表示可以刪除多個觸發(fā)器的占位符?！纠纠?0-7】刪除觸發(fā)器student_delete，執(zhí)行如下的語句。USE jwglIF EXISTS ( SELECT name FROM sysobjects