SQL Server數(shù)據(jù)庫應用與開發(fā)：第08章 存儲過程和觸發(fā)器

1、SQL Server 2005 數(shù)據(jù)庫應用與開發(fā)第08章 存儲過程和觸發(fā)器 內(nèi)容提要:存儲過程（Stored Procedure）是一組完成特定功能的Transact- SQL語句的集合。存儲過程是通過用戶、其他過程或觸發(fā)器來調(diào)用執(zhí)行。利用存儲過程可以保證數(shù)據(jù)的完整性，提高執(zhí)行重復任務的性能和數(shù)據(jù)的一致性。存儲過程主要應用于控制訪問權限、為數(shù)據(jù)庫表中的活動創(chuàng)建審計追蹤、將關系到數(shù)據(jù)庫及其所有相關應用程序的數(shù)據(jù)定義語句和數(shù)據(jù)操作語句分隔開。 觸發(fā)器（Trigger）是一種特殊的存儲過程。觸發(fā)器通常在特定的表上定義，當該表的相應事件發(fā)生時自動執(zhí)行，用于實現(xiàn)強制業(yè)務規(guī)則和數(shù)據(jù)完整性等。第08章 存儲

2、過程和觸發(fā)器 本章內(nèi)容:8.1 存儲過程概述8.2 創(chuàng)建和管理存儲過程8.3 觸發(fā)器概述8.4 創(chuàng)建和管理觸發(fā)器8.5小結8.1 存儲過程概述存儲過程的主要用途：提高了處理復雜任務的能力。增強了代碼的復用率和共享性。存儲過程一旦創(chuàng)建后即可在程序中調(diào)用任意多次。減少了網(wǎng)絡中數(shù)據(jù)的流量。存儲過程在服務器注冊，加快了過程的運行速度。加強了系統(tǒng)的安全性。存儲過程具有安全特性（例如權限）和所有權鏈接，用戶可以被授予權限來執(zhí)行存儲過程而不必直接對存儲過程中引用的對象具有權限。8.1 存儲過程概述8.1.1 存儲過程的類型SQL Server 2005支持的存儲過程的類型主要有如下4類。（1）系統(tǒng)存儲過程。

3、SQL Server 2005 中的許多管理活動都是存儲過程執(zhí)行的。從物理意義上講，系統(tǒng)存儲過程存儲在源數(shù)據(jù)庫中，并且?guī)в?sp_ 前綴。從邏輯上講，系統(tǒng)存儲過程出現(xiàn)在每個系統(tǒng)定義數(shù)據(jù)庫和用戶定義數(shù)據(jù)庫的 sys 構架中。（2）用戶定義的存儲過程。用戶可以自己創(chuàng)建存儲過程。存儲過程是指封裝了可重用代碼的模塊或例程。用戶存儲過程有兩種類型：8.1 存儲過程概述8.1.1 存儲過程的類型Transact-SQL 存儲過程是指保存的 Transact-SQL 語句集合，可接受和返回用戶提供的參數(shù)。CLR 存儲過程是指對 Microsoft .NET Framework 公共語言運行時 (CLR) 方

4、法的引用，可以接受和返回用戶提供的參數(shù)（3）臨時存儲過程。以“#”或“#”為前綴，表示局部臨時存儲過程和全局臨時存儲過程。（4）擴展存儲過程。以xp_為前綴，是SQL Server 2005的實例可以動態(tài)加載和運行的 DLL。8.1 存儲過程概述8.1.2 存儲過程的設計原則用戶創(chuàng)建存儲過程時，應注意遵循以下幾點原則。存儲過程最大不能超過128MB。用戶定義的存儲過程只能在當前數(shù)據(jù)庫中創(chuàng)建。存儲過程是為了處理那些需要被多次運行的Transact-SQL語句集。SQL Server允許在存儲過程創(chuàng)建時引用一個不存在的對象，系統(tǒng)只檢查創(chuàng)建存儲過程的語法。執(zhí)行時，存儲過程引用了一個不存在的對象，則這

5、次執(zhí)行操作將會失敗。存儲過程可以嵌套使用。嵌套的最大層次可以用 NESTLEVEL函數(shù)來查看。8.1 存儲過程概述8.1.3 常用系統(tǒng)存儲過程的使用SQL Server 2005提供了許多系統(tǒng)存儲過程,下面介紹幾種常用的系統(tǒng)存儲過程。（1）sp_helpdb 用于查看數(shù)據(jù)庫名稱及大小。（2）sp_helptext 用于顯示規(guī)則、默認值、未加密的存儲過程、用戶定義函數(shù)、觸發(fā)器或視圖的文本。（3）sp_renamedb 用于重命名數(shù)據(jù)庫。（4）sp_rename 用于更改當前數(shù)據(jù)庫中用戶創(chuàng)建對象（如表、列或用戶定義數(shù)據(jù)類型）的名稱。（5）sp_helplogins查看所有數(shù)據(jù)庫用戶登錄信息。（6）

6、sp_helpsrvrolemember 用于以查看所有數(shù)據(jù)庫用戶所屬的角色信息。8.2 創(chuàng)建和管理存儲過程8.2.1 創(chuàng)建存儲過程1使用SQL Server Management Studio創(chuàng)建存儲過程利用SQL Server Management Studio創(chuàng)建存儲過程就是創(chuàng)建一個模板，通過改寫模板創(chuàng)建存儲過程。具體參考步驟如下。（1）啟動SQL Server Management Studio，在對象資源管理器中，展開“數(shù)據(jù)庫”| teaching |“可編程性”|“存儲過程”。（2）如圖8.1所示，右擊“存儲過程”節(jié)點，選擇“新建存儲過程”菜單命令。8.2 創(chuàng)建和管理存儲過程8.2

7、.1 創(chuàng)建存儲過程1使用SQL Server Management Studio創(chuàng)建存儲過程（3）系統(tǒng)彈出存儲過程模板，如圖8.2所示，用戶可以參照模板在其中輸入合適的Transact-SQL語句。（4）單擊工具欄中的“執(zhí)行”按鈕，即可將存儲過程保存在數(shù)據(jù)庫中。（5）刷新“存儲過程”節(jié)點，可以觀察到下方出現(xiàn)了新建的存儲過程。8.2 創(chuàng)建和管理存儲過程8.2.1 創(chuàng)建存儲過程2使用CREATE PROCEDURE語句創(chuàng)建存儲過程CREATE PROCEDURE語句的語法格式如下：CREATE PROC EDURE procedure_name ; number parameter_data_ty

8、pe VARYING = default OUTPUT ,n WITH RECOMPILE | ENCRYPTION FOR REPLICATION AS sql_statament ,n 例8.1創(chuàng)建一個存儲過程，輸出所有學生的姓名、課程名稱和期末成績信息。程序代碼如下:CREATE PROCEDURE student_scoreASSELECT sname, cname, final FROM student s ,course c ,score sc WHERE s.studentno=sc. studentno and c.courseno=sc.courseno8.2 創(chuàng)建和管理存儲

9、過程8.2.1 創(chuàng)建存儲過程例8.2創(chuàng)建一個存儲過程，輸出指定學生的姓名及課程名稱、期末成績信息。程序代碼如下:CREATE PROCEDURE student_score1 student_name nchar(8) ASSELECT sname, cname, final FROM student s ,course c ,score sc WHERE s.studentno=sc. studentno and c.courseno=sc.courseno and .sname=student_name AS sql_statament ,n 例8.3 創(chuàng)建一個存儲過程，用輸出參數(shù)返回指定

10、學生的所有課程的期末成績的平均值程序代碼如下:CREATE PROCEDURE student_score2 student_name nchar(8), average numeric(6,2) OUTPUT ASSELECT average=AVG(final) FROM student s ,course c ,score sc WHERE s.studentno=sc.studentno and c.courseno=sc.coursenoand s.sname=student_name例8.4 創(chuàng)建一個存儲過程，用輸出參數(shù)返回指定學生的所有課程的期末成績的平均值，若不指定學生姓名，則

11、返回所有學生的所有課程的期末成績的平均值。程序代碼如下:CREATE PROCEDURE student_score3 student_name nchar(8)=NULL, average numeric(6,2) OUTPUTASSELECT average =AVG(final) FROM student s ,course c ,score sc WHERE s.studentno=sc. studentno and c.courseno=sc.coursenoand (s.sname= student_name or student_name IS NULL)8.2 創(chuàng)建和管理存儲過

12、程8.2.2 修改存儲過程1. 利用SQL Server Management Studio修改存儲過程修改存儲過程的參考操作步驟如下。（1）在“對象資源管理器”中展開“數(shù)據(jù)庫” | teaching |“可編程性”|“存儲過程”。（2）右擊要修改的用戶存儲過程如student_score，在彈出快捷菜單中選擇“修改”命令。（3）在查詢編輯器中出現(xiàn)存儲過程的源代碼，用戶可以直接進行修改。（4）修改完畢，執(zhí)行該存儲過程，將修改后的存儲過程保存到數(shù)據(jù)庫中。 8.2 創(chuàng)建和管理存儲過程8.2.2 修改存儲過程2. 使用ALTER PROCEDURE語句修改存儲過程使用ALTER PROCEDURE語

13、句可以修改存儲過程。ALTER PROCEDURE語句的語法格式如下：ALTER PROC EDURE procedure_name ; number parameter_data_type VARYING = default OUTPUT ,n WITH RECOMPILE | ENCRYPTION FOR REPLICATION AS sql_statament ,n 例8.5 修改存儲過程student_score，使其以加密方式存儲在系統(tǒng)表syscomments中。程序代碼如下:ALTER PROCEDURE student_scoreWITH ENCRYPTIONASSELECT s

14、name, cname, final FROM student s ,course c ,score sc WHERE s.studentno=sc. Studentno and c.courseno=sc.courseno8.2 創(chuàng)建和管理存儲過程8.2.3 執(zhí)行存儲過程利用EXECUTE 語句可以執(zhí)行存儲過程。對于存儲過程的所有者或任何一名對此過程擁有EXECUTE特權的用戶，都可以執(zhí)行此存儲過程。EXECUTE語句的語法格式如下： EXEC UTE return_status = procedure_name ; number parameter1 = value | parameter

15、1=variable OUTPUT . WITH RECOMPILE 例8.6 執(zhí)行存儲過程student_score2。分析：由于該存儲過程有輸出參數(shù)，那么必須在執(zhí)行存儲過程前定義一個變量，以接收存儲過程要傳出的值。然后可以使用如下語句輸出變量ave的值。程序代碼如下:DECLARE ave numeric(6,2)EXEC student_score2 student_name=何影, average =ave OUTPUTSELECT ave例8.7 使用默認值執(zhí)行的存儲過程student_score3。程序代碼如下:DECLARE ave numeric(6,2)EXEC studen

16、t_score3 average =ave OUTPUTSELECT ave8.3 觸發(fā)器概述觸發(fā)器（Trigger）是一種響應數(shù)據(jù)操作語言（DML）事件或數(shù)據(jù)定義語言（DDL）事件而執(zhí)行的特殊類型的存儲過程，是在用戶對某一表中的數(shù)據(jù)進行UPDATE、INSERT和DELETE操作時被觸發(fā)執(zhí)行的一段程序。觸發(fā)器有助于強制引用完整性，以便在添加、更新或刪除表中的行時保留表之間已定義的關系。8.3 觸發(fā)器概述8.3.1 觸發(fā)器的作用觸發(fā)器可以實現(xiàn)由主鍵和外鍵所不能保證的復雜的參照完整性和數(shù)據(jù)一致性。（1）觸發(fā)器的主要作用。觸發(fā)器可以對數(shù)據(jù)庫進行級聯(lián)修改。實現(xiàn)比CHECK約束更為復雜的限制。強制表的

17、修改要合乎業(yè)務規(guī)則。8.3 觸發(fā)器概述8.3.1 觸發(fā)器的作用（2）使用觸發(fā)器的優(yōu)點。 觸發(fā)器可通過數(shù)據(jù)庫中的相關表實現(xiàn)級聯(lián)更改。觸發(fā)器可以強制比用CHECK約束定義的約束更為復雜的約束。 觸發(fā)器也可以監(jiān)測數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對策。一個表中的多個同類觸發(fā)器（INSERT、UPDATE 或 DELETE）允許采取多個不同的對策以響應同一個修改語句。 8.3 觸發(fā)器概述8.3.2 觸發(fā)器的分類兩種類型的觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。1. DML觸發(fā)器DML觸發(fā)器是在執(zhí)行INSERT、UPDATE或DELETE語句時被激活的觸發(fā)器。DML觸發(fā)器又分為AFTER和INSTEAD

18、 OF兩種觸發(fā)器:AFTER 觸發(fā)器是在激活它的語句執(zhí)行成功后才激活.INSTEAD OF觸發(fā)器的激活將替代相應的觸發(fā)語句。當數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言（DML）事件時將調(diào)用 DML 觸發(fā)器。DML 觸發(fā)器可以查詢其他表，還可以包含復雜的 Transact-SQL 語句。將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個事務對待。如果檢測到錯誤,則整個事務即自動回滾。8.3 觸發(fā)器概述8.3.2 觸發(fā)器的分類。1. DML觸發(fā)器: DML 觸發(fā)器通常用于以下場合： DML 觸發(fā)器可通過數(shù)據(jù)庫中的相關表實現(xiàn)級聯(lián)更改。DML 觸發(fā)器可以防止惡意或錯誤的 INSERT、UPDATE 以及 DELETE

19、操作，并強制執(zhí)行比 CHECK 約束定義的限制更為復雜的其他限制。 與 CHECK 約束不同，DML 觸發(fā)器可引用其他表中的列。DML 觸發(fā)器可以評估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施。一個表中的多個同類DML觸發(fā)器允許采取多個不同的操作來響應同一個修改語句。8.3 觸發(fā)器概述8.3.2 觸發(fā)器的分類。2. DDL觸發(fā)器DDL觸發(fā)器是在執(zhí)行CREATE、ALTER和DROP語句時被激活的觸發(fā)器，是由數(shù)據(jù)定義語言引起的。如果要執(zhí)行以下操作，可以使用DDL觸發(fā)器： 要防止對數(shù)據(jù)庫架構進行某些更改。希望數(shù)據(jù)庫中發(fā)生某種情況以響應數(shù)據(jù)庫架構中的更改。 要記錄數(shù)據(jù)庫架構中的更改或事件。僅在運行觸

20、發(fā) DDL 觸發(fā)器的 DDL 語句后，DDL 觸發(fā)器才會激發(fā)。DDL 觸發(fā)器無法作為 INSTEAD OF 觸發(fā)器使用。8.3 觸發(fā)器概述8.3.3 觸發(fā)器的工作原理在觸發(fā)器的執(zhí)行過程中，SQL Server為每個觸發(fā)器創(chuàng)建和管理兩個特殊的表，一個是插入表inserted表，一個是刪除表deleted表。這兩個表建在數(shù)據(jù)庫服務器的內(nèi)存中，與觸發(fā)器所在數(shù)據(jù)表的結構是完全一致。當由INSERT或UPDATE語句激活相應觸發(fā)器之后，所有被添加或被更新的記錄都被存儲到inserted表。當由DELETE或UPDATE語句激活相應觸發(fā)器之后，所有被刪除的記錄都被送到deleted表。在觸發(fā)器的執(zhí)行過程中

21、，可以讀取這兩個表中的內(nèi)容，但不能修改它們。當觸發(fā)器的工作完成之后，這兩個表也將從內(nèi)存中刪除。8.3 觸發(fā)器概述8.3.4 創(chuàng)建觸發(fā)器前應該注意的問題在創(chuàng)建觸發(fā)器前，需要注意以下一些問題：CREATE TRIGGER 語句必須是批處理中的第一個語句，而且只能用于一個表或視圖。創(chuàng)建觸發(fā)器的權限默認分配給表的所有者，且不能將該權限轉(zhuǎn)給其他用戶。觸發(fā)器可以引用當前數(shù)據(jù)庫以外的對象，但只能在當前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時表。不應引用系統(tǒng)表，而應使用信息架構視圖。在含有用 DELETE或UPDATE操作定義的外鍵的表中，不能定義INSTEAD OF和 I

22、NSTEAD OF UPDATE觸發(fā)器。8.4 創(chuàng)建和管理觸發(fā)器8.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器可以通過SQL Server Management Studio和CREATE TRIGGER語句兩種方法。創(chuàng)建觸發(fā)器時需要指定如下的選項： 觸發(fā)器名稱和需要定義觸發(fā)器的表。觸發(fā)器將何時激發(fā)。激活觸發(fā)器的數(shù)據(jù)修改語句。有效選項為INSERT、UPDATE或 DELETE。多個數(shù)據(jù)修改語句可激活同一個觸發(fā)器。8.4 創(chuàng)建和管理觸發(fā)器8.4.1 創(chuàng)建觸發(fā)器1. 在SQL Server Management Studio中創(chuàng)建觸發(fā)器（1）啟動SQL Server Management Studio，在“對

23、象資源管理器”中展開“數(shù)據(jù)庫”|teaching|“表” 。（2）選擇要創(chuàng)建觸發(fā)器的表student并展開。右單“觸發(fā)器”節(jié)點，在彈出的快捷菜單中選擇“新建觸發(fā)器”命令。（3）此時彈出如圖8.4所示的新建觸發(fā)器編輯窗口，其中包含觸發(fā)器模板，用戶可以參照模板在其中輸入觸發(fā)器的Transact-SQL語句。（4）單擊工具欄中的“！”執(zhí)行按鈕，將觸發(fā)器保存到數(shù)據(jù)庫中。8.4 創(chuàng)建和管理觸發(fā)器8.4.1 創(chuàng)建觸發(fā)器2. 使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的語法格式如下：CREATE TRIGGER trigger_name ON table | view WITH ENCRYPT

24、ION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS sql_statament ,n 例8.8 為student表創(chuàng)建一個觸發(fā)器，用來禁止更新學號字段的值。程序代碼如下:CREATE TRIGGER update_s_tr ON student AFTER UPDATEASIF UPDATE(studentno) BEGIN RAISERROR(不能修改學號,16,2) ROLLBACKEND此時，若有更新語句如下：UPDATE student SET studentno=0837221508WHERE studentno=0937

25、221508則提示“不能修改學號”， 更新語句得不到執(zhí)行。raiserror 是由單詞 raise error 組成 raise 增加; 提高; 提升raiserror 的作用： raiserror 是用于拋出一個錯誤。 以下資料來源于sql server 2005的幫助 其語法如下：RAISERROR(msg_id|msg_str|local_variable,severity,state,argument,.n)WITHoption,.n簡要說明一下：第一個參數(shù)： msg_id | msg_str | local_variable msg_id：表示可以是一個sys.messages表中定

26、義的消息代號； 使用 sp_addmessage 存儲在 sys.messages 目錄視圖中的用戶定義錯誤消息號。 用戶定義錯誤消息的錯誤號應當大于 50000。 msg_str：表示也可以是一個用戶定義消息，該錯誤消息最長可以有 2047 個字符； （如果是常量，請使用Nxxxx，因為是nvarchar的） 當指定 msg_str 時，RAISERROR 將引發(fā)一個錯誤號為 5000 的錯誤消息。 local_variable：表示也可以是按照 msg_str 方式的格式化字符串變量。第二個參數(shù)：severity 用戶定義的與該消息關聯(lián)的嚴重級別。（這個很重要） 任何用戶都可以指定 0 到

27、 18 之間的嚴重級別。 0,10的閉區(qū)間內(nèi)，不會跳到catch； 如果是11,19,則跳到catch； 如果20,無窮)，則直接終止數(shù)據(jù)庫連接；第三個參數(shù)：state 如果在多個位置引發(fā)相同的用戶定義錯誤， 則針對每個位置使用唯一的狀態(tài)號有助于找到引發(fā)錯誤的代碼段。 介于 1 至 127 之間的任意整數(shù)。（state 默認值為1） 當state 值為 0 或大于 127 時會生成錯誤！第四個參數(shù)：argument 用于代替 msg_str 或?qū)?msg_id 的消息中的定義的變量的參數(shù)。第五個參數(shù)：option 錯誤的自定義選項，可以是下表中的任一值： LOG ：在錯誤日志和應用程序日志

28、中記錄錯誤； NOWAIT：將消息立即發(fā)送給客戶端； SETERROR：將 ERROR 值和 ERROR_NUMBER 值設置為 msg_id 或 50000；8.4 創(chuàng)建和管理觸發(fā)器8.4.1 創(chuàng)建觸發(fā)器例8.9 為course表創(chuàng)建一個觸發(fā)器，用來防止用戶刪除任何必修課程的課程記錄。程序代碼如下:CREATE TRIGGER delete_c_tr ON courseINSTEAD OF DELETE ASIF EXISTS ( SELECT * FROM course WHERE type=必修 ) BEGIN RAISERROR(不能刪除必修課程,16,2) ROLLBACK END此

29、時，若刪除新語句如下：DELETE FORM course WHERE type=必修則提示“不能刪除必修課程”，刪除語句得不到執(zhí)行。8.4 創(chuàng)建和管理觸發(fā)器8.4.1 創(chuàng)建觸發(fā)器例8.10 為score表創(chuàng)建一個觸發(fā)器，用來防止用戶對score表中的數(shù)據(jù)進行任何修改。程序代碼如下:CREATE TRIGGER update_sc_tr ON scoreINSTEAD OF UPDATEASRAISERROR(不能修改成績表中的數(shù)據(jù),16,2)此時，若有更新語句如下：UPDATE score SET final=60則提示“不能修改成績表中的數(shù)據(jù)”，更新語句得不到執(zhí)行。同一個觸發(fā)器。8.4 創(chuàng)

30、建和管理觸發(fā)器8.4.2 修改觸發(fā)器1. 使用SQL Server Management Studio修改觸發(fā)器修改觸發(fā)器的操作步驟如下。（1）在“對象資源管理器”中展開“數(shù)據(jù)庫”。（2）選擇觸發(fā)器所在的數(shù)據(jù)庫，如teaching數(shù)據(jù)庫，并展開該數(shù)據(jù)庫的“表”節(jié)點。（3）選擇觸發(fā)器所在的表score，展開表中的“觸發(fā)器”。（4）右擊要修改的觸發(fā)器，在彈出的快捷菜單中選擇“修改”命令。（5）在彈出的觸發(fā)器編輯窗口，用戶可以直接進行修改。修改完畢，單擊工具欄中的“！”按鈕執(zhí)行該觸發(fā)器，將修改后的觸發(fā)器保存到數(shù)據(jù)庫中。8.4 創(chuàng)建和管理觸發(fā)器8.4.2 修改觸發(fā)器2. 使用ALTER TRIGGER

31、語句修改觸發(fā)器ALTER TRIGGER語句的語法格式如下：ALTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OFINSERT, UPDATE , DELETE AS sql_statament ,n 例8.11 使用ALTER TRIGGER語句修改觸發(fā)器update_s_tr，用來禁止更新學號字段和姓名字段的值。程序代碼如下:ALTER TRIGGER update_s_tr ON student AFTER UPDATEASIF UPDATE(studentno) OR UPDATE(sname) BEGIN RAISERROR(不能修改學號或姓名,16,2) ROLLBACK END8.4 創(chuàng)建和