項(xiàng)目4 存儲(chǔ)過(guò)程與觸發(fā)器管理

1、項(xiàng)目4：存儲(chǔ)過(guò)程與觸發(fā)器管理任務(wù)4-1：存儲(chǔ)過(guò)程的創(chuàng)建 任務(wù)4-2：存儲(chǔ)過(guò)程的執(zhí)行與管理任務(wù)4-3：觸發(fā)器的創(chuàng)建與使用1任務(wù)4-1：存儲(chǔ)過(guò)程的創(chuàng)建21、概述1）存儲(chǔ)過(guò)程的基本概念SQL Server的存儲(chǔ)過(guò)程類(lèi)似于編程語(yǔ)言中的過(guò)程。在使用Transact-SQL語(yǔ)言編程的過(guò)程中，可以將某些多次調(diào)用以實(shí)現(xiàn)某個(gè)特定任務(wù)的代碼段編寫(xiě)成一個(gè)過(guò)程，將其保存在數(shù)據(jù)庫(kù)中，并由SQL Server服務(wù)器通過(guò)過(guò)程名調(diào)用它們，這些過(guò)程就叫做存儲(chǔ)過(guò)程。3存儲(chǔ)過(guò)程可以實(shí)現(xiàn)：接受輸入?yún)?shù)并以輸出參數(shù)的格式向調(diào)用過(guò)程或批處理返回多個(gè)值。包含用于在數(shù)據(jù)庫(kù)中執(zhí)行操作（包括調(diào)用其他過(guò)程）的編程語(yǔ)句。 向調(diào)用過(guò)程或批處理返回狀態(tài)

2、值，以指明成功或失?。ㄒ约笆〉脑颍?。42）存儲(chǔ)過(guò)程的分類(lèi)系統(tǒng)存儲(chǔ)過(guò)程：由SQL Server 2005提供，用戶可以直接使用。SQL Server 2005 中的許多管理活動(dòng)都是通過(guò)一些系統(tǒng)存儲(chǔ)過(guò)程完成的。用戶定義的存儲(chǔ)過(guò)程：用戶自定義存儲(chǔ)過(guò)程用于實(shí)現(xiàn)用戶自己所需要實(shí)現(xiàn)的操作。53）存儲(chǔ)過(guò)程的優(yōu)點(diǎn)實(shí)現(xiàn)模塊化程序設(shè)計(jì)。存儲(chǔ)過(guò)程內(nèi)可引用其他存儲(chǔ)過(guò)程，可以簡(jiǎn)化一系列復(fù)雜語(yǔ)句?？梢詼p少網(wǎng)絡(luò)通信流量。存儲(chǔ)過(guò)程在創(chuàng)建時(shí)即在服務(wù)器上進(jìn)行編譯，所以執(zhí)行起來(lái)比單個(gè) SQL 語(yǔ)句快。可以作為一種安全機(jī)制。一個(gè)設(shè)計(jì)良好的數(shù)據(jù)庫(kù)應(yīng)用程序通常都會(huì)用到存儲(chǔ)過(guò)程。62、創(chuàng)建存儲(chǔ)過(guò)程1）使用SQL Server Mana

3、gement Studio創(chuàng)建存儲(chǔ)過(guò)程教材P157。72）使用T-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程CREATE PROC EDURE procedure_name parameter data_type = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ASsql_statement說(shuō)明：教材P157P158。8例9.1 不帶有參數(shù)的存儲(chǔ)過(guò)程。 下面的存儲(chǔ)過(guò)程從sales數(shù)據(jù)庫(kù)的三個(gè)表的連接中返回訂單的編號(hào)、客戶名稱(chēng)、銷(xiāo)售員和訂單日期。該存儲(chǔ)過(guò)程不使用任何參數(shù)。USE salesGOCREATE PROCE

4、DURE UP_OrderInfoASSELECT Orders.OrderID, Customer.CompanyName, Seller.Salename, orders.OrderDate FROM Orders INNER JOIN Seller ON Orders.SaleID = Seller.SaleIDINNER JOIN Customer ON Orders.CustomerID = Customer.CustomerIDGO9例9.2 帶有輸入?yún)?shù)的存儲(chǔ)過(guò)程。 下面的存儲(chǔ)過(guò)程從sales數(shù)據(jù)庫(kù)的三個(gè)表的聯(lián)接中返回訂單的編號(hào)、客戶名稱(chēng)、銷(xiāo)售員和訂單日期。該存儲(chǔ)過(guò)程接受日期區(qū)間

5、參數(shù)：開(kāi)始日期和結(jié)束日期。USE salesGOCREATE PROCEDURE UP_OrderInfoWithParam StartDate datetime, EndDate datetimeAS SELECT Orders.OrderID, Customer.CompanyName, Seller.Salename,Orders.OrderDate FROM Orders INNER JOIN Seller ON Orders.SaleID = Seller.SaleID INNER JOIN Customer ON Orders.CustomerID = Customer.Custo

6、merIDWHERE (Orders.OrderDate BETWEEN StartDate AND EndDate)GO10例9.3 帶有輸出參數(shù)的存儲(chǔ)過(guò)程。 該存儲(chǔ)過(guò)程從sales數(shù)據(jù)庫(kù)的Customer表查詢客戶的基本信息，輸入?yún)?shù)為客戶編號(hào)，輸出參數(shù)為客戶所在公司的名稱(chēng)和公司地址。USE salesGOCREATE PROCEDURE UP_CustomerInfo CustId nvarchar(3), ComName nvarchar(60) OUTPUT, ComAddress nvarchar(60) OUTPUTAS SELECT ComName=CompanyName, C

7、omAddress=AddressFROM Customer WHERE CustomerID=CustIdGO11例9.4 帶有返回值的存儲(chǔ)過(guò)程。 該存儲(chǔ)過(guò)程向Category表中插入數(shù)據(jù)，如果插入成功返回1，插入失敗返回0，輸入?yún)?shù)為商品種類(lèi)編號(hào)、種類(lèi)名稱(chēng)和種類(lèi)表述信息。USE salesGOCREATE PROCEDURE UP_InsertCate CategoryID int, CategoryName nvarchar(15), Description nvarchar(200)AS SET nocount on IF (NOT EXISTS(SELECT * FROM Categ

8、ory WHERE CategoryID=CategoryID) BEGIN INSERT INTO Category(CategoryID,CategoryName,Description) VALUES(CategoryID,CategoryName,Description) RETURN 1-添加數(shù)據(jù)成功返回1 END ELSE RETURN 0-添加數(shù)據(jù)失敗返回0GO12任務(wù)4-2：存儲(chǔ)過(guò)程的執(zhí)行與管理131、執(zhí)行存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程可以通過(guò)EXECUTE語(yǔ)句來(lái)執(zhí)行，其語(yǔ)法格式如下： EXEC UTE return_status = procedure_name | procedure_n

9、ame_var parameter = value | OUTPUT | DEFAULT ,.n WITH RECOMPILE 說(shuō)明：教材P159P160。141）不帶參數(shù)的存儲(chǔ)過(guò)程調(diào)用 調(diào)用例9.1中的存儲(chǔ)過(guò)程。USE salesEXECUTE UP_OrderInfo-或EXEC UP_OrderInfo-或UP_OrderInfo152）帶輸入?yún)?shù)的存儲(chǔ)過(guò)程的調(diào)用 調(diào)用例9.2中的存儲(chǔ)過(guò)程。USE salesEXECUTE UP_OrderInfoWithParam 2008-7-1, 2008-7-9-或EXECUTE UP_OrderInfoWithParam StartDate=2

10、008-7-1, EndDate=2008-7-9-或EXECUTE UP_OrderInfoWithParam EndDate=2008-7-9, StartDate=2008-7-1163）帶輸出參數(shù)的存儲(chǔ)過(guò)程的調(diào)用 調(diào)用例9.3中的存儲(chǔ)過(guò)程。USE salesGODECLARE Name nvarchar(60), Address nvarchar(60)EXECUTE UP_CustomerInfo C01, Name OUTPUT, Address OUTPUT SELECT Name, Address174）帶有返回值的存儲(chǔ)過(guò)程的調(diào)用 調(diào)用例9.4中的存儲(chǔ)過(guò)程。USE salesD

11、ECLARE return_value intEXECUTE return_value = UP_InsertCate 5, 香煙, 中華、熊貓和玉溪IF return_value=1 PRINT 添加數(shù)據(jù)成功ELSE PRINT 數(shù)據(jù)已存在，不能添加185）一個(gè)存儲(chǔ)過(guò)程調(diào)用另一存儲(chǔ)過(guò)程-創(chuàng)建存儲(chǔ)過(guò)程USE salesGOCREATE PROCEDURE UP_CallInsertCateAS DECLARE return_value int EXECUTE return_value = UP_InsertCate 5,香煙,中華、熊貓和玉溪 IF return_value=1 PRINT 添

12、加數(shù)據(jù)成功 ELSE PRINT 數(shù)據(jù)已存在，不能添加GO-執(zhí)行存儲(chǔ)過(guò)程USE salesEXECUTE UP_CallInsertCate192、管理存儲(chǔ)過(guò)程1）查看存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程之后，它的名字就存儲(chǔ)在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中?？梢允褂孟到y(tǒng)存儲(chǔ)過(guò)程sp_help、sp_helptext、sp_depends來(lái)查看用戶自定義存儲(chǔ)過(guò)程。202）修改存儲(chǔ)過(guò)程ALTER PROC EDURE procedure_name parameter data_type = default OUTPUT ,.n WITH RECOMPILE | ENC

13、RYPTION | RECOMPILE , ENCRYPTION ASsql_statement注：修改存儲(chǔ)過(guò)程的語(yǔ)法格式與創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式相似。213）重命名存儲(chǔ)過(guò)程 sp_rename procedure_name , new_procedure_name示例：USE salesGOsp_rename UP_OrderInfoWithParam, UP_OrderInfoDateGO224）刪除名存儲(chǔ)過(guò)程 DROP PROCEDURE procedure ,.n 示例：USE salesGODROP PRODEDURE UP_OrderInfoGO23課堂練習(xí)（1）創(chuàng)建一個(gè)存儲(chǔ)過(guò)程【

14、proc_1】，其功能是用來(lái)查找所有女銷(xiāo)售員的清單。然后執(zhí)行該存儲(chǔ)過(guò)程。（2）創(chuàng)建一個(gè)帶有輸入?yún)?shù)的存儲(chǔ)過(guò)程【proc_2】，其功能是通過(guò)一個(gè)給定的銷(xiāo)售員編號(hào)，顯示出該銷(xiāo)售員的詳細(xì)信息。然后執(zhí)行該存儲(chǔ)過(guò)程。（3）創(chuàng)建一個(gè)帶有輸入?yún)?shù)和輸出參數(shù)的存儲(chǔ)過(guò)程【proc_3】，其功能是通過(guò)一個(gè)給定的產(chǎn)品編號(hào)，查詢出該產(chǎn)品的價(jià)格和庫(kù)存量，并通過(guò)輸出參數(shù)進(jìn)行返回。然后執(zhí)行該存儲(chǔ)過(guò)程。24任務(wù)4-3：觸發(fā)器的創(chuàng)建和使用251、概述觸發(fā)器（trigger）是一種特殊的存儲(chǔ)過(guò)程，它不同于一般的存儲(chǔ)過(guò)程。一般的存儲(chǔ)過(guò)程通過(guò)過(guò)程名被直接調(diào)用，而觸發(fā)器主要是通過(guò)事件進(jìn)行觸發(fā)而被執(zhí)行。觸發(fā)器是一個(gè)功能強(qiáng)大的工具，與表緊

15、密連接，可以看作是表格定義的一部分。當(dāng)用戶修改（INSERT、UPDATE或DELETE）指定表或視圖中的數(shù)據(jù)時(shí)，該表中的相應(yīng)的觸發(fā)器就會(huì)自動(dòng)執(zhí)行。觸發(fā)器基于一個(gè)表創(chuàng)建，但可以操作多個(gè)表。觸發(fā)器常用來(lái)實(shí)現(xiàn)復(fù)雜的商業(yè)規(guī)則。但是，不管觸發(fā)器所進(jìn)行的操作多復(fù)雜，觸發(fā)器都只作為一個(gè)獨(dú)立的單元被執(zhí)行，被看作一個(gè)事務(wù)。如果在執(zhí)行觸發(fā)器的過(guò)程中發(fā)生了錯(cuò)誤，則整個(gè)事務(wù)將會(huì)自動(dòng)回滾。262、觸發(fā)器的分類(lèi)DML 觸發(fā)器 AFTER 觸發(fā)器 INSTEAD OF 觸發(fā)器 CLR 觸發(fā)器DDL 觸發(fā)器271） DML 觸發(fā)器 當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言 (DML) 事件時(shí)將調(diào)用 DML 觸發(fā)器。DML 事件包括在指定

16、表或視圖中修改數(shù)據(jù)的 INSERT 語(yǔ)句、UPDATE 語(yǔ)句或 DELETE 語(yǔ)句。DML 觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的 Transact-SQL 語(yǔ)句。28 AFTER 觸發(fā)器 又稱(chēng)后觸發(fā)器（After Trigger），這種類(lèi)型的觸發(fā)器將在執(zhí)行了相應(yīng)的DML語(yǔ)句操作之后才被觸發(fā)。可以對(duì)變動(dòng)的數(shù)據(jù)進(jìn)行檢查，如果發(fā)現(xiàn)錯(cuò)誤，將拒絕接受或回滾變動(dòng)的數(shù)據(jù)。指定AFTER與指定FOR相同，AFTER 觸發(fā)器只能在表上定義。在同一個(gè)數(shù)據(jù)表中可以創(chuàng)建多個(gè)AFTER觸發(fā)器。29 INSTEAD OF 觸發(fā)器 又稱(chēng)前觸發(fā)器（Inserted Of Trigger）INSTEAD OF觸發(fā)器在數(shù)據(jù)變動(dòng)

17、以前被觸發(fā)，并取代變動(dòng)數(shù)據(jù)的操作（UPDATE、INSERT和DELETE操作），而去執(zhí)行觸發(fā)器定義的操作。INSTEAD OF觸發(fā)器可以在表或視圖上定義。在表或視圖上，每個(gè)UPDATE、INSERT和DELETE語(yǔ)句最多可以定義一個(gè)INSTEAD OF觸發(fā)器。30 CLR 觸發(fā)器 CLR 觸發(fā)器可以是AFTER觸發(fā)器或INSTEAD OF 觸發(fā)器。CLR 觸發(fā)器還可以是DDL 觸發(fā)器。CLR 觸發(fā)器將執(zhí)行在托管代碼（在 .NET Framework 中創(chuàng)建并在 SQL Server 中上載的程序集的成員）編寫(xiě)的方法，而不用執(zhí)行Transact-SQL語(yǔ)句。31說(shuō)明： DML 觸發(fā)器語(yǔ)句使用兩

18、種特殊的表：刪除的表（deleted 表）和插入的表（inserted 表）。SQL Server 2005 會(huì)自動(dòng)創(chuàng)建和管理這兩種表?？梢允褂眠@兩種駐留內(nèi)存的臨時(shí)表來(lái)測(cè)試特定數(shù)據(jù)修改的影響以及設(shè)置 DML 觸發(fā)器操作條件。 刪除的表deleted 用于存儲(chǔ) DELETE 和 UPDATE 語(yǔ)句所影響的行的副本。在執(zhí)行 DELETE 或 UPDATE 語(yǔ)句的過(guò)程中，行從觸發(fā)器的基表中刪除，并傳輸?shù)絛eleted表中。deleted表和觸發(fā)器的基表通常沒(méi)有相同的行。 插入的表inserted用于存儲(chǔ) INSERT 和 UPDATE 語(yǔ)句所影響的行的副本。在插入或更新事務(wù)期間，新行將同時(shí)被添加到i

19、nserted表和觸發(fā)器基表。inserted表中的行是觸發(fā)器基表中新行的副本。 更新操作類(lèi)似于在刪除操作之后執(zhí)行插入操作；首先，舊行被復(fù)制到deleted表中，然后，新行被復(fù)制到觸發(fā)器表和inserted表中。322）DDL 觸發(fā)器 DDL 觸發(fā)器是一種特殊的觸發(fā)器，它在響應(yīng)數(shù)據(jù)定義語(yǔ)言 (DDL)主要包括CREATE、ALTER和DROP 語(yǔ)句時(shí)觸發(fā)。它們可以用于數(shù)據(jù)庫(kù)中執(zhí)行管理任務(wù)，例如，審核以及規(guī)范數(shù)據(jù)庫(kù)操作。333、創(chuàng)建觸發(fā)器1）使用SQL Server Management Studio創(chuàng)建觸發(fā)器教材P166。342）使用T-SQL語(yǔ)句創(chuàng)建觸發(fā)器 創(chuàng)建DML觸發(fā)器創(chuàng)建觸發(fā)器時(shí)需指定

20、： 觸發(fā)器名稱(chēng)。在其上定義觸發(fā)器的表或視圖。觸發(fā)器將何時(shí)激發(fā)。激活觸發(fā)器的數(shù)據(jù)修改語(yǔ)句。有效選項(xiàng)為 INSERT、UPDATE 或 DELETE。多個(gè)數(shù)據(jù)修改語(yǔ)句可激活同一個(gè)觸發(fā)器。例如，觸發(fā)器可由 INSERT 或 UPDATE 語(yǔ)句激活。觸發(fā)操作主體。35創(chuàng)建DML觸發(fā)器的語(yǔ)句格式如下：CREATE TRIGGER schema_name.trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS dml_sql_statement 說(shuō)明：教材P167。3

21、6例10.1 創(chuàng)建一個(gè)后觸發(fā)器，在Seller表中插入數(shù)據(jù)后，顯示友好的提示信息。USE salesGOCREATE TRIGGER tr_notify ON SellerAFTER INSERTAS BEGIN PRINT (剛剛在Seller表中增加了一條記錄！)ENDGO向Seller表中添加一條記錄來(lái)驗(yàn)證觸發(fā)器： INSERT INTO Seller(SaleID,SaleName)VALUES(s23,趙明明)37例10.2 創(chuàng)建Customer表的刪除觸發(fā)器tr_CustomerDelete。USE salesGOCREATE TRIGGER tr_CustomerDeleteON

22、 CustomerFOR DELETEAS BEGIN DECLARE com varchar(60) SELECT com= CompanyName FROM DELETED PRINT rtrim(com)+ 客戶信息已經(jīng)被刪除！ENDGO刪除Customer表中的一條記錄來(lái)驗(yàn)證觸發(fā)器： DELETE FROM Customer WHERE CustomerID=c0838 創(chuàng)建DDL觸發(fā)器創(chuàng)建DDL觸發(fā)器的語(yǔ)句格式如下： CREATE TRIGGER trigger_name ON ALL SERVER | DATABASE FOR | AFTERAS ddl_sql_statement

23、說(shuō)明：教材P16839例10.3 創(chuàng)建DDL觸發(fā)器，當(dāng)修改sales數(shù)據(jù)庫(kù)中的表結(jié)構(gòu)時(shí)，彈出提示信息“數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改！”。USE salesGOCREATE TRIGGER tr_alterTableON DATABASEFOR ALTER_TABLEASBEGIN PRINT 數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改！ENDGO向Customer表中增加一個(gè)字段來(lái)驗(yàn)證觸發(fā)器： ALTER TABLE Customer ADD Memo varchar(200)404、管理觸發(fā)器1）修改觸發(fā)器使用ALTER TRIGGER命令修改觸發(fā)器，修改觸發(fā)器與創(chuàng)建觸發(fā)器的語(yǔ)法基本相同，只是將創(chuàng)建觸發(fā)器的CREATE關(guān)

24、鍵字換成了ALTER關(guān)鍵字。412）查看觸發(fā)器可以使用系統(tǒng)存儲(chǔ)過(guò)程sp_help、sp_helptext、sp_depends和sp_helptrigger分別查看觸發(fā)器的不同信息。其中，系統(tǒng)存儲(chǔ)過(guò)程sp_helptrigger，返回對(duì)當(dāng)前數(shù)據(jù)庫(kù)的指定表上定義的DML 觸發(fā)器的類(lèi)型，語(yǔ)法格式如下：sp_helptrigger table_name423）禁用/啟用觸發(fā)器當(dāng)用戶想暫停觸發(fā)器的使用，但又不想刪除它，這時(shí)可以禁用觸發(fā)器，使其無(wú)效。當(dāng)需要時(shí)可以再次啟用。 禁用/啟用DML觸發(fā)器使用DISABLE TRIGGER命令禁用觸發(fā)器，語(yǔ)法格式為： DISABLE TRIGGER schema_name trigger_name ,.n | ALL ON object_name 說(shuō)明：教材P170使用ENABLE TRIGGER命令禁用觸發(fā)器，語(yǔ)法格式為： ENABLE TRIGGER schema_name trigger_name ,.n | ALL ON object_name例10.7 啟用Customer表上的tr_CustomerDelete觸發(fā)器。 ENABLE TRIGGER tr_Cust