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

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

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

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

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

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

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

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

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

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

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

11、程語句序列中使用。 存儲(chǔ)過程的定義不能跨越批處理。 8.1存儲(chǔ)過程存儲(chǔ)過程【例8-1】創(chuàng)建一個(gè)簡單的存儲(chǔ)過程，實(shí)現(xiàn)檢索學(xué)生的姓名、課程名和相應(yī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.學(xué)號(hào)=score.學(xué)號(hào) AND course.課程號(hào)=scor

12、e.課程號(hào)ORDER BY student.學(xué)號(hào)GO 8.1存儲(chǔ)過程存儲(chǔ)過程【例8-2】創(chuàng)建一個(gè)具有返回參數(shù)的存儲(chǔ)過程，實(shí)現(xiàn)按給定學(xué)號(hào)查詢學(xué)生的姓名和平均成績。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.學(xué)號(hào)=score.學(xué)號(hào) AND course.課程號(hào)=score.課程號(hào)GROUP BY student.姓名GO 8.1存儲(chǔ)過程存儲(chǔ)過程【例8-3】創(chuàng)建一個(gè)存儲(chǔ)過程insert_pro,該存儲(chǔ)過程包含兩個(gè)默認(rèn)參數(shù)，其值分別為：“男”和“黨員”。在創(chuàng)建該存儲(chǔ)過程前，先建立一個(gè)簡單的空表stu，分別有姓名、性別、政治面貌三個(gè)字段，其中沒有關(guān)鍵字并且允許空值。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存儲(chǔ)過程存儲(chǔ)過程 8.1.38.1.3執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程 通過EXEC命令可以執(zhí)行一個(gè)已定義的存儲(chǔ)過程，其語法格式為： EXEC return_status=procedure_name ,mumber | procedure_name_var param

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

16、UTPUT：指定存儲(chǔ)過程必須返回一個(gè)參數(shù)。 8.1存儲(chǔ)過程存儲(chǔ)過程 DEFAULT:根據(jù)過程的定義，提供參數(shù)的默認(rèn)值。 n:表示可以指定一個(gè)或者多個(gè)parameter,value或variable。 WITH RECOMPILE:指明強(qiáng)制編譯存儲(chǔ)過程?！纠?-4】執(zhí)行例8-1中定義的存儲(chǔ)過程，查詢所有學(xué)生的成績。在查詢分析器中執(zhí)行下列語句：USE student_msGOEXEC suc_queryGO 8.1存儲(chǔ)過程存儲(chǔ)過程【例8-5】執(zhí)行例8-2中定義的存儲(chǔ)過程，查詢部分學(xué)生的成績在查詢分析器中執(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 學(xué)生姓名=s_name,平均成績=sco_avgGOGOALTER TABLE studentADD 身份證號(hào)碼(19) NULLG0 8.1存儲(chǔ)過程存儲(chǔ)過程執(zhí)行存儲(chǔ)過程查詢部分學(xué)生的成績 : : 8.1存儲(chǔ)過程存儲(chǔ)過程【例8-6】執(zhí)行例8-3中定義的存儲(chǔ)過程，向表stu中插入三行數(shù)據(jù)。在查詢分析器中執(zhí)行下列語句：USE student_msEXEC insert_pro EXEC insert_pro 女EXEC insert_pro 女

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

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

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

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

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

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

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

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

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

27、.2 觸發(fā)器觸發(fā)器 3觸發(fā)器類型 SQL Server 2000支持兩種類型的觸發(fā)器：AFTER觸發(fā)器和INSTEAD OF觸發(fā)器。 AFTER觸發(fā)器要求只有執(zhí)行了某些操作(INSERT、UPDATE,、DELETE)之后，觸發(fā)器才被觸發(fā)，且只能在表上定義，可以為針對表的同一操作定義多個(gè)觸發(fā)器，也可定義哪個(gè)觸發(fā)器先被觸發(fā)，哪個(gè)后被觸發(fā)，通常使用系統(tǒng)存儲(chǔ)過程sp_set triggerorder來完成此任務(wù)。 INSTEAD OF觸發(fā)器并不執(zhí)行其所定義的操作，而僅執(zhí)行觸發(fā)器本身。該觸發(fā)器既可在表上定義，也可在視圖上定義，但對同一操作只能定義一個(gè)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ā)器之前應(yīng)該注意以下幾個(gè)問題: CREATE TRIGGER語句必須是批處理中的第一個(gè)語句。將該批處理中隨后的其他所有語句解釋為CREATE TRIGGER語句定義的一部分。 當(dāng)創(chuàng)建一個(gè)觸發(fā)器時(shí)，必須指定觸發(fā)器的名字，在哪一個(gè)表上定義觸發(fā)器，激活觸發(fā)器的修改語句，如INSERT,DELETE,UPDATE。當(dāng)然兩個(gè)或三個(gè)不同的修改語句也可以都觸發(fā)同一個(gè)觸發(fā)器，如INSERT和UPDATE語句都能激活同一個(gè)觸發(fā)器。 觸發(fā)器作為數(shù)據(jù)庫對象，其名稱必須遵循

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

30、1使用企業(yè)管理器創(chuàng)建觸發(fā)器 (1)啟動(dòng)企業(yè)管理器，登錄到要使用的服務(wù)器。 (2)在企業(yè)管理器的左窗格中，展開要?jiǎng)?chuàng)建觸發(fā)器的數(shù)據(jù)庫文件夾，單擊“表”文件夾，此時(shí)在右窗格中顯示該數(shù)據(jù)庫的所有表。 (3)在右窗格中，右擊要?jiǎng)?chuàng)建觸發(fā)器的數(shù)據(jù)表，在彈出的快捷菜單中，選擇“所有任務(wù)”“管理觸發(fā)器”，打開“觸發(fā)器屬性”對話框。 (4)在“名稱”下拉框中選擇“新建”，在“文本”編輯框中輸入觸發(fā)器的文本語句。 (5)單擊“檢查語法”按鈕，檢查語句是否正確。 (6)單擊“應(yīng)用”按鈕，在“名稱”下拉列框中會(huì)有新創(chuàng)建的觸發(fā)器名字。 (7)單擊“確定”按鈕，關(guān)閉窗口創(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)建存儲(chǔ)過程，其語法格式如下： 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數(shù)據(jù)庫中，創(chuàng)建一個(gè)AFTER觸發(fā)器，要求實(shí)現(xiàn)以下功能:在score表上創(chuàng)建一個(gè)插入、更新類型的觸發(fā)器score_Check，當(dāng)在成績字段中插入或修改考試分?jǐn)?shù)后，觸發(fā)該觸發(fā)器，檢查分?jǐn)?shù)是否在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 輸入的分?jǐn)?shù)有誤，請確認(rèn)輸入的考試分?jǐn)?shù)!ENDGO 8.2 觸發(fā)

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

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

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

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

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

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

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