第八章 存儲過程與觸發(fā)器

1、第八章第八章 存儲過程與觸發(fā)器存儲過程與觸發(fā)器 存儲過程與觸發(fā)器是數據庫中的一個重要組成部分，存儲過程可以把數據庫的復雜操作封裝為獨立的程序模塊，便于程序的維護和減少網絡通訊流量。而觸發(fā)器是可以自動調用執(zhí)行的程序模塊，可以實現比較復雜的約束功能，本章主要介紹了存儲過程與觸發(fā)器的概念，以及如何創(chuàng)建、使用和管理存儲過程與觸發(fā)器。8.1存儲過程存儲過程8.1.1存儲過程概述 存儲過程(Stored Procedure)是一組預先編譯好的T-SQL代碼。存儲過程可以作為一個獨立的數據庫對象，也可以作為一個單元被用戶的應用程序調用。存儲過程經過了一次創(chuàng)建后，可以被多次調用。由于是已經編譯好的代碼，所以在

2、執(zhí)行的時候不必再次進行編譯，從而提高了程序的運行效率。使用存儲過程具有以下優(yōu)點： 存儲過程只在創(chuàng)建時進行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲過程可提高數據庫執(zhí)行速度。 當對數據庫進行復雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此復雜操作用存儲過程封裝起來與數據庫提供的事務處理結合一起使用。8.1存儲過程存儲過程存儲過程可以重復使用,可減少數據庫開發(fā)人員的工作量。使用存儲過程可以完成所有數據庫操作，并可通過編程方式控制對數據庫信息訪問的權限以提高安全性。8.1.28.1.2存儲過程的類型存儲過程

3、的類型 SQL Server支持的存儲過程可分為5類：在不同情況下需要執(zhí)行不同的存儲過程。 系統(tǒng)存儲過程、 本地存儲過程、 臨時存儲過程、 遠程存儲過程 擴展存儲過程。8.1存儲過程存儲過程 1系統(tǒng)存儲過程 系統(tǒng)存儲過程是由系統(tǒng)提供的存儲過程，可以作為命令執(zhí)行各種操作。系統(tǒng)存儲過程定義在系統(tǒng)數據庫master中，其前綴是sp_,它們?yōu)闄z索系統(tǒng)表的信息提供了方便快捷的方法。系統(tǒng)存儲過程允許系統(tǒng)管理員執(zhí)行修改系統(tǒng)表的數據庫管理任務，可以在任何一個數據庫中執(zhí)行。 2本地存儲過程 本地存儲過程是指在用戶數據庫中創(chuàng)建的存儲過程，這種存儲過程完成用戶指定的數據庫操作，其名稱不能以sp_為前綴。 3臨時存儲

4、過程 臨時存儲過程屬于本地存儲過程。如果本地存儲過程的名稱前面有一個#，該存儲過程就稱為局部臨時存儲過程，這種存儲過程只能在一個用戶會話中使用。8.1存儲過程存儲過程 如果本地存儲過程的名稱前有兩個#，該過程就是全局臨時存儲過程，這種存儲過程可以在所有用戶會話中使用。 4遠程存儲過程 遠程存儲過程指從遠程服務器上調用的存儲過程。 5擴展存儲過程 在SQL Server環(huán)境之外執(zhí)行的動態(tài)鏈接庫稱為擴展存儲過程，其前綴是sp_。使用時需要先加載到SQL Server系統(tǒng)中，并且按照使用存儲過程的方法執(zhí)行。 8.1存儲過程存儲過程8.1.3存儲過程的創(chuàng)建 用戶存儲過程只能定義在當前數據庫中，可以使用

5、SQL Server的企業(yè)管理器或Transact-SQL語句創(chuàng)建存儲過程。默認情況下，用戶創(chuàng)建的存儲過程歸數據庫所有者擁有，數據庫的所有者可以把許可授權給其他用戶。1使用企業(yè)管理器創(chuàng)建存儲過程 (1)啟動企業(yè)管理器，登錄到要使用的服務器。 (2)在企業(yè)管理器的左窗格中，展開要創(chuàng)建存儲過程的數據庫文件夾，單擊“存儲過程”文件夾，此時在右窗格中顯示該數據庫的所有存儲過程。 (3)右擊“存儲過程”文件夾，在彈出的快捷菜單中選擇“新建存儲過程”，此時打開如圖8-1所示的“新建存儲過程”對話框。 8.1存儲過程存儲過程新建存儲過程如圖: 8.1存儲過程存儲過程(4)在“文本”編輯框中輸入存儲過程的正文

6、內容。(5)單擊“檢查語法”按鈕，檢查語法是否正確。(6)單擊“確定”按鈕保存。(7)在右窗格中，右擊該存儲過程，在彈出菜單中選擇“所有任務”，選擇“管理權限”設置權限。 8.1存儲過程存儲過程2.使用Transact-SQL語句創(chuàng)建存儲過程CREATE PROCEDURE的語法形式如下：CREATE PROC procedure_name;number parameter data_type VARYING=defaultOUTPUT ，n WITH RECOMPILE | ENCRYPTION|RECOMPILEFOR REPLICATIONAS sql_statements8.1存儲過程

7、存儲過程其中其中: : procedure_name:用于定義存儲過程名，必須符合標識符規(guī)則，且對于數據庫及其所有者必須惟一;創(chuàng)建局部臨時過程，可以在procedure name前面加一個#：創(chuàng)建全局臨時過程，可以在procedure name前加#。 Number:為可選的整數，用于區(qū)分同名的存儲過程，以便用一條DROP Procedure語句刪除一組存儲過程。 parameter:存儲過程的形參，形參局部于該存儲過程，參數名必須符合標識符規(guī)則，并且首字符必須為，可定義一個或多個形參，執(zhí)行存儲過程時應提供相應的實在參數，除非定義了該參數的默認值。8.1存儲過程存儲過程 Data_type:用

8、于指定形參數據類型，形參類型可為SQL Server支持的任何類型，但cursor類型只能用于OUTPUT參數，如果指定形參類型為cursor，必須同時指定VARYING和OUTPUT關鍵字。default指定存儲過程輸入參數的默認值，默認值必須是常量或NULL，默認值中可以包含通配符(%、_、和 )，如果定義了默認值，執(zhí)行存儲過程時根據情況可不提供實參。 OUTPUT:用于指定參數從存儲過程返回信息。如果一個輸出參數的類型為游標，并且結果集會動態(tài)變化，則使用關鍵字VARYING指明輸出參數的內容可以變化。n表示可為存儲過程指定若干個參數。 RECOMPILE：指明SQL Server每次運行

9、該過程時，將對其重新編譯； 8.1存儲過程存儲過程 ENCRYPTION：表示SQL Server加密syscomments表中包含CREATE PROCEDURE語句文本的條目；使用ENCRYPTION可防止將過程作為SQL Server復制的一部分發(fā)布，防止用戶使用系統(tǒng)存儲過程讀取存儲過程的定義文本。 FOR REPLICATION:用于說明不能在訂閱服務器上執(zhí)行為復制創(chuàng)建的存儲過程，該選項不能和WITH RECOMPILE一起使用。 sql_statements：存儲過程體包含的T-SQL語句序列。 8.1存儲過程存儲過程 對于用戶自定義存儲過程要注意如下幾點： 用戶定義的存儲過程只能在

10、當前數據庫中創(chuàng)建(臨時過程除外，臨時過程總是在tempdb中創(chuàng)建)。 成功執(zhí)行CREATE PROCEDURE語句后，過程名存儲在sysobjects系統(tǒng)表中，而CREATE PROCEDURE語句的文本存儲在syscomments表中。 自動執(zhí)行存儲過程。SQL Server啟動時可以自動執(zhí)行一個或多個存儲過程。這些存儲過程必須由系統(tǒng)管理員在master數據庫中創(chuàng)建，并在sysadmin固定服務器角色下作為后臺過程執(zhí)行。這些過程不能有任何輸入參數。 sql_statements的限制。除了SET SHOWPLAN_TEXT和SET SHOWPLAN_ ALL以外，其它SET語句均可以在存儲過

11、程語句序列中使用。 存儲過程的定義不能跨越批處理。 8.1存儲過程存儲過程【例8-1】創(chuàng)建一個簡單的存儲過程，實現檢索學生的姓名、課程名和相應的成績。USE student_msIF EXISTS(SELECT name FROM sysobjects WHERE name=suc_query AND TYPE=P)DROP PROCEDURE suc_queryGOCREATE PROCEDURE suc_queryASSELECT 姓名,課程名稱,成績FROM student,course,scoreWHERE student.學號=score.學號 AND course.課程號=scor

12、e.課程號ORDER BY student.學號GO 8.1存儲過程存儲過程【例8-2】創(chuàng)建一個具有返回參數的存儲過程，實現按給定學號查詢學生的姓名和平均成績。USE student_msIF EXISTS(SELECT name FROM sysobjects WHERE name=sco_avg_query AND TYPE=P)DROP PROCEDURE sco_avg_queryGOCREATE PROCEDURE sco_avg_query(s_snum char(8),s_name char(10) OUTPUT,sco_avg float OUTPUT)ASSELECT s_n

13、ame=姓名,sco_avg=AVG(成績)FROM student,course,scoreWHERE student.學號=score.學號 AND course.課程號=score.課程號GROUP BY student.姓名GO 8.1存儲過程存儲過程【例8-3】創(chuàng)建一個存儲過程insert_pro,該存儲過程包含兩個默認參數，其值分別為：“男”和“黨員”。在創(chuàng)建該存儲過程前，先建立一個簡單的空表stu，分別有姓名、性別、政治面貌三個字段，其中沒有關鍵字并且允許空值。USE student_msIF EXISTS(SELECT name FROM sysobjects WHERE na

14、me=insert_pro AND TYPE=P)DROP PROCEDURE insert_proGOCREATE PROCEDURE insert_pro(char1 char(2)=男, char2 char(10)=黨員)AS INSERT INTO stu (性別,政治面貌) VALUES(char1,char2)GO 8.1存儲過程存儲過程 8.1.38.1.3執(zhí)行存儲過程執(zhí)行存儲過程 通過EXEC命令可以執(zhí)行一個已定義的存儲過程，其語法格式為： EXEC return_status=procedure_name ,mumber | procedure_name_var param

15、eter= value | variable OUTPUT DEFAULT，nWITH RECOMPILE其中： 8.1存儲過程存儲過程 return_ status:是一個可選的整型變量，保存存儲過程的返回狀態(tài)。EXEC語句在使用該變量前，必須對其進行定義。 procedure_name:擬調用的存儲過程名稱。 procedure_name_var:局部定義變量名，代表存儲過程名稱。 number:用于指明組中的存儲過程 parameter:過程參數，在CREATE PROCEDURE語句中定義。 Value:是存儲過程中的實際參數。 variable：是用來保存OUTPUT參數返回值。 O

16、UTPUT：指定存儲過程必須返回一個參數。 8.1存儲過程存儲過程 DEFAULT:根據過程的定義，提供參數的默認值。 n:表示可以指定一個或者多個parameter,value或variable。 WITH RECOMPILE:指明強制編譯存儲過程?！纠?-4】執(zhí)行例8-1中定義的存儲過程，查詢所有學生的成績。在查詢分析器中執(zhí)行下列語句：USE student_msGOEXEC suc_queryGO 8.1存儲過程存儲過程【例8-5】執(zhí)行例8-2中定義的存儲過程，查詢部分學生的成績在查詢分析器中執(zhí)行下列語句：USE student_msDECLARE s_name char(10),sco

17、_avg numeric(5,2)EXEC sco_avg_query 010202,s_name OUTPUT,sco_avg OUTPUTSELECT 學生姓名=s_name,平均成績=sco_avgGOGOALTER TABLE studentADD 身份證號碼(19) NULLG0 8.1存儲過程存儲過程執(zhí)行存儲過程查詢部分學生的成績 : : 8.1存儲過程存儲過程【例8-6】執(zhí)行例8-3中定義的存儲過程，向表stu中插入三行數據。在查詢分析器中執(zhí)行下列語句：USE student_msEXEC insert_pro EXEC insert_pro 女EXEC insert_pro 女

18、,團員執(zhí)行結果如圖所示。 8.18.14 4存儲過程的查看存儲過程的查看創(chuàng)建存儲過程之后，它的名字就存儲在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中?？梢允褂闷髽I(yè)管理器或系統(tǒng)存儲過程查看用戶創(chuàng)建的存儲過程。 1在企業(yè)管理器中查看用戶創(chuàng)建的存儲過程(1)在企業(yè)管理器的左窗格中，展開要創(chuàng)建存儲過程的數據庫文件夾，單擊“存儲過程”文件夾，此時在右窗格中顯示該數據庫的所有存儲過程。如圖所示8.1存儲過程存儲過程 8.1存儲過程存儲過程 查看存儲過程如圖: (2) 右擊要查看的存儲過程，這里右擊存儲過程sco_avg_query，從彈出的快捷菜單中選擇“屬性”選項，彈出

19、“存儲過程屬性”對話框。在此對話框中能夠看到存儲過程的源代碼，如圖所示。8.1存儲過程存儲過程 (3) 如果從彈出的快捷菜單中依次選擇“所有任務”、“顯示相關性”選項，會彈出“相關性”對話框，顯示與選擇的存儲過程有依賴關系的其他數據庫對象的名稱，如圖所示。 8.1存儲過程存儲過程 2可以使用sp_helptext系統(tǒng)存儲過程查看存儲過程的定義信息。其語法格式為：sp_helptext procedure_name?！纠?-7】查看數據庫student_ms中存儲過程sco_avg_query的定義。 在存儲器中執(zhí)行下列語句：USE student_msGOsp_helptext sco_avg

20、_query 8.1存儲過程存儲過程 存儲過程的定義信息如圖: 8.1存儲過程存儲過程 8.1.58.1.5存儲過程的修改、刪除存儲過程的修改、刪除 可以在企業(yè)管理器中修改、刪除用戶自定義的存儲過程，也可以通過Transact-SQL語句修改、刪除存儲過程1在企業(yè)管理器中修改存儲過程 打開“存儲過程屬性”對話框，在中間的文本編輯框中直接修改存儲過程的定義；定義修改后，可以通過“檢查語法”按鈕對新定義的存儲過程內容進行語法檢查；并可以通過“權限”按鈕，打開“對象屬性”對話框,在其中勾選該存儲過程的使用者，完成對存儲過程的權限進行設置。修改設置完成后，單擊“確定”按鈕即可。 8.1存儲過程存儲過程

21、 2使用ALTER PROCEDURE命令可以修改已存在的存儲過程。其語法形式為：ALTER PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT ，n WITH RECOMPILE | ENCRYPTION|RECOMPILEFOR REPLICATIONAS sql_statements其中各個參數含義同CREATE PROCEDURE語句。 8.1存儲過程存儲過程 修改存儲過程時，應注意以下幾點：如果在CREATE PROCEDURE語句中使用過參數，那么在ALTER PROCEDURE語句中也

22、應該使用這些參數。每次只能修改一個存儲過程。存儲過程的創(chuàng)建者、dbwe owner和db_ ddladmin的成員擁有執(zhí)行ALTER PROCEDURE語句的許可，其他用戶不能使用。用ALTER PROCEDURE更改的存儲過程的權限和啟動屬性保持不變。 8.1存儲過程存儲過程 3使用企業(yè)管理器刪除存儲過程 使用企業(yè)管理器刪除一個或多個存儲過程，先將它們選取，然后右擊其中一個被選取的存儲過程，并從快捷菜單中選取“刪除”命令，接著再單擊“除去對象”對話框中的“全部除去”按鈕。4使用DROP PROCEDURE語句刪除存儲過程 刪除存儲過程使用DROP命令，DROP命令可將一個或多個存儲過程或者存

23、儲過程組從當前數據庫中刪除。具體語法如下： DROP PROC procedure_name，n 其中各參數的意義與修改存儲過程命令中參數的意義相同。 8.1存儲過程存儲過程 【例8-8】將存儲過程sco_avg_query從數據庫中刪除。DROP sco_avg_query注意：注意： 如果存儲過程分組，則將無法刪除組內的單個存儲過程。要刪除一個存儲過程將會刪除同一組內的所有存儲過程. 如果用一個存儲過程調用某個已被刪除的存儲過程，則SQL Server會在執(zhí)行該過程調用時顯示一條錯誤信息。而如果定義了一個與已被刪除的存儲過程同名的并且具有相同參數的新的存儲過程，那么調用該過程的其它過程仍然

24、能夠順利執(zhí)行。 8.1存儲過程存儲過程 8.2.18.2.1觸發(fā)器概述觸發(fā)器概述1觸發(fā)器的概念 觸發(fā)器是一種在基表被修改時能自動執(zhí)行的內嵌存儲過程過程，它主要是通過事件進行觸發(fā)而被執(zhí)行的過程，這有區(qū)別于普通的存儲過程通過存儲過程名被直接調用。也就是當一個特定的動作發(fā)生在一個特定的表上時，某個觸發(fā)器被激活，從而確保對數據的處理必須符合由觸發(fā)器中SQL語句所定義的規(guī)則。 每個觸發(fā)器通常有三個通用的部分：名稱、動作和執(zhí)行。觸發(fā)器的動作可以是INSERT 、 UPDATE或DELETE語句，觸發(fā)器的執(zhí)行部分通常含有一個存儲過程或批處理，不允許使用參數，也不能被直接調用，只能由系統(tǒng)自動激活。觸發(fā)器可以查

25、詢其他表，而且可以包含復雜的SQL語句。它們主要用于控制復雜的業(yè)務規(guī)則或要求。 8.2 觸發(fā)器觸發(fā)器 2觸發(fā)器的優(yōu)點與其他約束相比較，觸發(fā)器具有以下幾個優(yōu)點： (1)強制比CHECK約束更復雜的數據完整性 在數據庫中要實現數據完整性的約束，可以使用CHECK約束或觸發(fā)器來實現。但是在CHECK約束中不允許引用其他表中的列來完成檢查工作，而觸發(fā)器則可以引用其他表中的列來完成數據完整性的約束檢查。（2）使用自定義的錯誤信息 用戶有時需要在數據完整性遭到破壞或其他情況下，發(fā)出預先自定義好的錯誤信息或動態(tài)自定義的錯誤信息。通過使用觸發(fā)器，用戶可以捕獲破壞數據完整性的操作，并返回自定義的錯誤信息。 8.

26、2 觸發(fā)器觸發(fā)器 (3)實現數據庫中多張表的級聯修改 用戶可以通過觸發(fā)器對數據庫中的相關表進行級聯修改。(4)比較數據庫修改前后數據的狀態(tài) 觸發(fā)器具有訪問由INSERT、UPDATE或DELETE語句引起的數據變化前后，表狀態(tài)之間的差別的能力。因此用戶就可以在觸發(fā)器中引用由于修改所影響的記錄行。(5)維護非規(guī)范化數據 用戶可以使用觸發(fā)器來保證非規(guī)范數據庫中的低級數據的完整性。維護非規(guī)范化數據與表的級聯是不同的。表的級聯指的是不同表之間的主外鍵關系，維護表的級聯可以通過設置表的主鍵與外鍵的關系來實現。而非規(guī)范數據通常是指在表中的派生的、冗余的數據值，維護非規(guī)范化數據應該通過使用觸發(fā)器來實現。 8

27、.2 觸發(fā)器觸發(fā)器 3觸發(fā)器類型 SQL Server 2000支持兩種類型的觸發(fā)器：AFTER觸發(fā)器和INSTEAD OF觸發(fā)器。 AFTER觸發(fā)器要求只有執(zhí)行了某些操作(INSERT、UPDATE,、DELETE)之后，觸發(fā)器才被觸發(fā)，且只能在表上定義，可以為針對表的同一操作定義多個觸發(fā)器，也可定義哪個觸發(fā)器先被觸發(fā)，哪個后被觸發(fā)，通常使用系統(tǒng)存儲過程sp_set triggerorder來完成此任務。 INSTEAD OF觸發(fā)器并不執(zhí)行其所定義的操作，而僅執(zhí)行觸發(fā)器本身。該觸發(fā)器既可在表上定義，也可在視圖上定義，但對同一操作只能定義一個INSTEAD OF觸發(fā)器。 8.2 觸發(fā)器觸發(fā)器

28、8.2.28.2.2觸發(fā)器的創(chuàng)建觸發(fā)器的創(chuàng)建 在SQL Server中，可以通過企業(yè)管理器或者使用Transact-SQL語句創(chuàng)建觸發(fā)器。在創(chuàng)建觸發(fā)器之前應該注意以下幾個問題: CREATE TRIGGER語句必須是批處理中的第一個語句。將該批處理中隨后的其他所有語句解釋為CREATE TRIGGER語句定義的一部分。 當創(chuàng)建一個觸發(fā)器時，必須指定觸發(fā)器的名字，在哪一個表上定義觸發(fā)器，激活觸發(fā)器的修改語句，如INSERT,DELETE,UPDATE。當然兩個或三個不同的修改語句也可以都觸發(fā)同一個觸發(fā)器，如INSERT和UPDATE語句都能激活同一個觸發(fā)器。 觸發(fā)器作為數據庫對象，其名稱必須遵循

29、標識符的命名規(guī)則。 創(chuàng)建觸發(fā)器的權限默認分配給表的所有者，且不能將該權限轉給其他用戶。 8.2 觸發(fā)器觸發(fā)器 雖然觸發(fā)器可以引用當前數據庫以外的對象，但只能在當前數據庫中創(chuàng)建觸發(fā)器。 不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時表。不應引用系統(tǒng)表，而應使用信息架構視圖。 在含有用DELETE或UPDATE操作定義的外鍵的表中，不能定義INSTEAD OF 和INSTEAD OF UPDATE觸發(fā)器。 雖然TRUNCATE TABLE語句類似于沒有WHERE子句的DELETE語句，但它并不會引發(fā)DELETE觸發(fā)器，因為TRUNCATE TABLE語句沒有記錄。 8.2 觸發(fā)器觸發(fā)器

30、1使用企業(yè)管理器創(chuàng)建觸發(fā)器 (1)啟動企業(yè)管理器，登錄到要使用的服務器。 (2)在企業(yè)管理器的左窗格中，展開要創(chuàng)建觸發(fā)器的數據庫文件夾，單擊“表”文件夾，此時在右窗格中顯示該數據庫的所有表。 (3)在右窗格中，右擊要創(chuàng)建觸發(fā)器的數據表，在彈出的快捷菜單中，選擇“所有任務”“管理觸發(fā)器”，打開“觸發(fā)器屬性”對話框。 (4)在“名稱”下拉框中選擇“新建”，在“文本”編輯框中輸入觸發(fā)器的文本語句。 (5)單擊“檢查語法”按鈕，檢查語句是否正確。 (6)單擊“應用”按鈕，在“名稱”下拉列框中會有新創(chuàng)建的觸發(fā)器名字。 (7)單擊“確定”按鈕，關閉窗口創(chuàng)建成功。 8.2 觸發(fā)器觸發(fā)器 2使用Transac

31、t-SQL語句創(chuàng)建觸發(fā)器 使用Transact-SQL語言中的CREATE TRIGGER語句也可以創(chuàng)建觸發(fā)器，其中需要指定定義觸發(fā)器的基表、觸發(fā)器執(zhí)行的事件和觸發(fā)器的所有指令。創(chuàng)建觸發(fā)器的過程類似于創(chuàng)建存儲過程，其語法格式如下： CREATE TRIGGER trigger_name ONtable | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT ， UPDATE ， DELETE WITH APPENDNOT FOR REPLICATIONASIF UPDATE(column) AND|ORUPDATE(column) .n|I

32、F( COLUNNS_UPDATED()bitwise_operatorupdated_bitmask) comparison_operatorcolumn_bitmask.nsql_statement.n 8.2 觸發(fā)器觸發(fā)器 【例8-9】在student_ms數據庫中，創(chuàng)建一個AFTER觸發(fā)器，要求實現以下功能:在score表上創(chuàng)建一個插入、更新類型的觸發(fā)器score_Check，當在成績字段中插入或修改考試分數后，觸發(fā)該觸發(fā)器，檢查分數是否在0100之間。在查詢分析器中執(zhí)行下列語句：USE student_msGOIF EXISTS(SELECT name FROM sysobjects

33、WHERE name=scoreCheckAND type=TR) DROP TRIGGER score_CheckGO 8.2 觸發(fā)器觸發(fā)器 CREATE TRIGGER score_CheckON scoreFOR INSERT, UPDATEASIF UPDATE(成績)PRINT AFTER觸發(fā)器開始執(zhí)行BEGIN DECLARE ScoreValue real SELECT ScoreValue=(SELECT 成績 FROM inserted) IF ScoreValue100 OR ScoreValue0 PRINT 輸入的分數有誤，請確認輸入的考試分數!ENDGO 8.2 觸發(fā)

34、器觸發(fā)器 在上述的score_Check觸發(fā)器創(chuàng)建過程中，使用了inserted表，實際上，這個表是在觸發(fā)器被執(zhí)行時，系統(tǒng)自動創(chuàng)建的。如果觸發(fā)一個DELETE觸發(fā)器時，系統(tǒng)同樣會創(chuàng)建一個deleted表。下面介紹兩個表的內容。 inserted邏輯表：用于保存基本表中被INSERT和UPDATE語句影響的數據行，當向表中插入數據時，INSERT觸發(fā)器觸發(fā)執(zhí)行，新的記錄插入到inserted表中。deleted邏輯表：用于保存已從基本表中刪除的記錄，當觸發(fā)一個DELETE觸發(fā)器時，被刪除的記錄存放到deleted邏輯表中。 8.2 觸發(fā)器觸發(fā)器 當修改一條記錄時，相當于插入一新記錄，同時刪除舊記

35、錄。當對定義了UPDATE觸發(fā)器的基本表記錄進行修改時，表中原記錄移到deleted表中，修改過的記錄插入到inserted表中。 deleted、inserted兩個邏輯表的查詢方法與數據庫表的查詢方法相同。例如要檢索deleted, inserted表中的所有記錄，可使用如下語句：SELECT * FROM deletedSELECT * FROM inserted 一般來說，基本表中不會存在和deleted（inserted）中具有完全相同內容的數據行。 8.2 觸發(fā)器觸發(fā)器 8.2.38.2.3觸發(fā)器的使用觸發(fā)器的使用在創(chuàng)建好觸發(fā)器以后，可以通過執(zhí)行相應的SQL語句來使用觸發(fā)器?！纠?

36、-10】在score表中分別插入和更新數據，查看觸發(fā)器執(zhí)行效果，并通過比較不同的結果，了解觸發(fā)器的執(zhí)行過程。在查詢分析器中執(zhí)行下列語句：USE student_msGOPRINT 在score中插入記錄時觸發(fā)器執(zhí)行結果:INSERT INTO scoreVALUES(010209,01003,127)GO 8.2 觸發(fā)器觸發(fā)器 PRINT 在score中修改記錄時觸發(fā)器執(zhí)行結果:UPDATE scoreSET 成績=107WHERE 學號=010532 AND 課程號=02003GOPRINT 插入符合要求的數據INSERT INTO scoreVALUES(010209,01003,87)G

37、O執(zhí)行結果如圖所示： 8.2 觸發(fā)器觸發(fā)器 觸發(fā)器執(zhí)行結果如圖 : 8.2 觸發(fā)器觸發(fā)器 8.2.4查看觸發(fā)器信息 像存儲過程一樣，觸發(fā)器在創(chuàng)建后，其名稱保存在系統(tǒng)表sysobjects中，并把創(chuàng)建的源代碼保存在系統(tǒng)表syscomments中。如果要知道作用于表上的觸發(fā)器是對哪個表在起作用，又作了哪些操作，必須通過查看觸發(fā)器信息才可以知道。SQL Server為用戶提供多種查看觸發(fā)器信息的方法。 1. 使用企業(yè)管理器查看觸發(fā)器定義信息 （1）打開“企業(yè)管理器”，展開數據庫，右擊需要查看的表。 （2）在彈出的菜單中選擇“所有任務” “管理觸發(fā)器”，SQL Server將彈出“觸發(fā)器屬性”對話框。

38、 （3）在“觸發(fā)器屬性”對話框中，通過“名稱”下拉框，用戶可以選擇要查看的觸發(fā)器名稱，在下面的文本框中查看該觸發(fā)器的定義語句。 8.2 觸發(fā)器觸發(fā)器 使用企業(yè)管理器查看與觸發(fā)器有依賴關系的其他數據庫對象（1）打開“企業(yè)管理器”，展開數據庫，在右邊的窗格中，右擊需要查看的表。（）在彈出的菜單中選擇“所有任務” “顯示相關性”，SQL Server將彈出“相關性”對話框。（3）在“相關性”對話框中，通過“對象”下拉框，用戶可以選擇要查看的觸發(fā)器名稱，左邊的頁框中會顯示依賴于該對象的其他對象，右邊的頁框中會顯示該對象所依賴的其他對象。 8.2 觸發(fā)器觸發(fā)器 3使用系統(tǒng)存儲過程查看觸發(fā)器 系統(tǒng)存儲過程

39、sp_help 、 sp_helptext和sp_depends分別提供有關觸發(fā)器的不同信息。 通過sp_help系統(tǒng)存儲過程，可以了解觸發(fā)器的一般信息(名字、屬性、類型、創(chuàng)建時間等)。 通過sp_helptext能夠查看觸發(fā)器的定義信息。 通過sp_depends能夠查看指定觸發(fā)器所引用的表或指定的表涉及的所有觸發(fā)器。 8.2 觸發(fā)器觸發(fā)器 注意注意：用戶必須在當前數據庫中查看觸發(fā)器的信息，而且被查看的觸發(fā)器必須已經被創(chuàng)建。和存儲過程的加密類似，用戶也可以在創(chuàng)建觸發(fā)器時，通過指定WITH ENCRYPTION來對觸發(fā)器的定義文本信息進行加密，加密后的觸發(fā)器無法用sp_helptext來查看相

40、關信息。 用戶還可以通過使用系統(tǒng)存儲過程sp_helptrigger來查看某張?zhí)囟ū砩洗嬖诘挠|發(fā)器的某些相關信息。具體命令的語法如下：EXEC sp_helptrigger table_name 8.2 觸發(fā)器觸發(fā)器 【例8-13】使用系統(tǒng)存儲過程sp_helptrigger查看表student上存在的所有觸發(fā)器的相關信息。在“查詢分析器”的查詢窗口中執(zhí)行下面的語句：USE student_msGOEXEC sp_helptrigger studentGO 8.2 觸發(fā)器觸發(fā)器 8.2.58.2.5修改觸發(fā)器修改觸發(fā)器 通過系統(tǒng)存儲過程、企業(yè)管理器或Transact-SQL語句，可以修改觸發(fā)器的名字和正文。1.使用sp_rename系統(tǒng)存儲過程修改觸發(fā)器的名字，其語法格式為：sp_rename old_name, new_name其中，old_name為觸發(fā)器原來的