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

1、2022-1-25SQL Server 20051 2SQL Server 2005第第9 9章章 存儲(chǔ)過程與觸發(fā)器存儲(chǔ)過程與觸發(fā)器* * 存儲(chǔ)過程和觸發(fā)器都是存儲(chǔ)過程和觸發(fā)器都是SQL ServerSQL Server的數(shù)據(jù)庫(kù)對(duì)象。的數(shù)據(jù)庫(kù)對(duì)象。 存儲(chǔ)過程的存在獨(dú)立于表，存放在服務(wù)器上，供客戶端調(diào)用；存儲(chǔ)過程的存在獨(dú)立于表，存放在服務(wù)器上，供客戶端調(diào)用； 觸發(fā)器的使用則和表的更新操作緊密結(jié)合，是一種特殊的存儲(chǔ)觸發(fā)器的使用則和表的更新操作緊密結(jié)合，是一種特殊的存儲(chǔ)過程，使用觸發(fā)器可以大大提高數(shù)據(jù)庫(kù)應(yīng)用程序的靈活性和健過程，使用觸發(fā)器可以大大提高數(shù)據(jù)庫(kù)應(yīng)用程序的靈活性和健壯性，可以利用觸發(fā)器來實(shí)

2、現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則，更有效地實(shí)施壯性，可以利用觸發(fā)器來實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則，更有效地實(shí)施數(shù)據(jù)完整性。數(shù)據(jù)完整性。 存儲(chǔ)過程和觸發(fā)器是大型、復(fù)雜、高性能要求的數(shù)據(jù)庫(kù)應(yīng)用系存儲(chǔ)過程和觸發(fā)器是大型、復(fù)雜、高性能要求的數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)所必需要的技術(shù)圍繞存儲(chǔ)過程和觸發(fā)器。統(tǒng)所必需要的技術(shù)圍繞存儲(chǔ)過程和觸發(fā)器。 主要內(nèi)容：存儲(chǔ)過程概述、設(shè)計(jì)存儲(chǔ)過程、實(shí)現(xiàn)和管理存儲(chǔ)過主要內(nèi)容：存儲(chǔ)過程概述、設(shè)計(jì)存儲(chǔ)過程、實(shí)現(xiàn)和管理存儲(chǔ)過程、程、DML DML 觸發(fā)器概述、設(shè)計(jì)觸發(fā)器概述、設(shè)計(jì)DML DML 觸發(fā)器、實(shí)現(xiàn)和管理觸發(fā)器、實(shí)現(xiàn)和管理DML DML 觸發(fā)觸發(fā)器、器、DDLDDL觸發(fā)器概述、設(shè)計(jì)觸發(fā)器概述、設(shè)計(jì)DDL DD

3、L 觸發(fā)器、實(shí)現(xiàn)觸發(fā)器、實(shí)現(xiàn) DDL DDL 觸發(fā)器等。觸發(fā)器等。 3SQL Server 2005目錄目錄 9.1 存儲(chǔ)過程存儲(chǔ)過程 9.2 DML 觸發(fā)器觸發(fā)器 9.3 DDL 觸發(fā)器觸發(fā)器練習(xí)題練習(xí)題9 9 上機(jī)實(shí)習(xí)上機(jī)實(shí)習(xí)8 8 4SQL Server 20059.1 9.1 存儲(chǔ)過程存儲(chǔ)過程 9.1.1 存儲(chǔ)過程概述 9.1.2 設(shè)計(jì)存儲(chǔ)過程 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程返回本節(jié)首頁(yè)5SQL Server 2005返回本節(jié)首頁(yè)使用使用T-SQLT-SQL程序時(shí)，可用兩種方法存儲(chǔ)和執(zhí)行程序：程序時(shí)，可用兩種方法存儲(chǔ)和執(zhí)行程序：1 1）可以將程序存儲(chǔ)在本地，并設(shè)計(jì)向）可以將程序存儲(chǔ)在本

4、地，并設(shè)計(jì)向SQL ServerSQL Server發(fā)發(fā)送命令并處理結(jié)果的應(yīng)用程序。送命令并處理結(jié)果的應(yīng)用程序。2 2）也可以將程序以存儲(chǔ)過程形式存儲(chǔ)在）也可以將程序以存儲(chǔ)過程形式存儲(chǔ)在SQL ServerSQL Server服務(wù)器中，并創(chuàng)建執(zhí)行存儲(chǔ)過程并處理結(jié)果的應(yīng)用程服務(wù)器中，并創(chuàng)建執(zhí)行存儲(chǔ)過程并處理結(jié)果的應(yīng)用程序。序。 9.1.1 9.1.1 存儲(chǔ)過程概述存儲(chǔ)過程概述6SQL Server 2005返回本節(jié)首頁(yè) 1 1、存儲(chǔ)過程基本知識(shí)、存儲(chǔ)過程基本知識(shí) SQL Server中的存儲(chǔ)過程與其它編程語(yǔ)言過程：1）接受輸入?yún)?shù)并以輸出參數(shù)的格式向調(diào)用過程或批處理返回多個(gè)值；2）包含用于在數(shù)據(jù)

5、庫(kù)中執(zhí)行操作（包括調(diào)用其它過程）的編程語(yǔ)句；3）向調(diào)用過程或批處理返回狀態(tài)值，以指明成功或失?。ㄒ约笆〉脑颍?。 可以使用可以使用 T-SQL EXECUTE T-SQL EXECUTE 語(yǔ)句來運(yùn)行存儲(chǔ)過程。存儲(chǔ)過程語(yǔ)句來運(yùn)行存儲(chǔ)過程。存儲(chǔ)過程與函數(shù)不同，因?yàn)榇鎯?chǔ)過程不返回取代其名稱的值，也不能與函數(shù)不同，因?yàn)榇鎯?chǔ)過程不返回取代其名稱的值，也不能直接在表達(dá)式中使用。直接在表達(dá)式中使用。9.1.1 9.1.1 存儲(chǔ)過程概述存儲(chǔ)過程概述7SQL Server 2005返回本節(jié)首頁(yè)在在SQL ServerSQL Server中使用存儲(chǔ)過程的好處包括：中使用存儲(chǔ)過程的好處包括：1）存儲(chǔ)過程已在服務(wù)器

6、注冊(cè)；2）存儲(chǔ)過程具有安全特性（例如權(quán)限）。用戶可以被授予權(quán)限來執(zhí)行存儲(chǔ)過程而不必?fù)碛衅湟脤?duì)象的權(quán)限；3）存儲(chǔ)過程可以強(qiáng)制應(yīng)用程序的安全性，保護(hù)應(yīng)用程序不受SQL Injection攻擊（可將惡意代碼插入到以后將傳遞給SQL Server供分析和執(zhí)行的字符串中，后將執(zhí)行并遭到攻擊）；4）存儲(chǔ)過程允許模塊化程序設(shè)計(jì)，一次創(chuàng)建多次調(diào)用，可維護(hù)性好，以一致的方式訪問數(shù)據(jù)庫(kù)；5）存儲(chǔ)過程是命名代碼，允許延遲綁定。這提供了一個(gè)用于簡(jiǎn)單代碼演變的間接級(jí)別；6）存儲(chǔ)過程可以減少網(wǎng)絡(luò)通信流量。一個(gè)需要數(shù)百行T-SQL代碼的操作可以通過一條執(zhí)行過程代碼的語(yǔ)句來執(zhí)行。9.1.1 9.1.1 存儲(chǔ)過程概述存儲(chǔ)過程

7、概述8SQL Server 2005返回本節(jié)首頁(yè)2 2、存儲(chǔ)過程的類型、存儲(chǔ)過程的類型（1 1）用戶定義的存儲(chǔ)過程）用戶定義的存儲(chǔ)過程存儲(chǔ)過程是指封裝了可重用代碼的模塊或例程。存儲(chǔ)過程可以接受輸入?yún)?shù)、向客戶端返回表格或標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語(yǔ)言(DDL)和數(shù)據(jù)操作語(yǔ)言(DML)語(yǔ)句，然后返回輸出參數(shù)。在SQL Server 2005中，存儲(chǔ)存儲(chǔ)過程有兩種類型：過程有兩種類型：T-SQLT-SQL或或CLRCLR。1 1）T-SQL T-SQL 存儲(chǔ)過程是指保存的存儲(chǔ)過程是指保存的T-SQLT-SQL語(yǔ)句集合，可以接受和返回用戶提語(yǔ)句集合，可以接受和返回用戶提供的參數(shù)。供的參數(shù)。2 2）

8、CLRCLR存儲(chǔ)過程是指對(duì)存儲(chǔ)過程是指對(duì).NET Framework.NET Framework公共語(yǔ)言運(yùn)行時(shí)公共語(yǔ)言運(yùn)行時(shí)(CLR)(CLR)方法的引用，方法的引用，可以接受和返回用戶提供的參數(shù)。它們?cè)诳梢越邮芎头祷赜脩籼峁┑膮?shù)。它們?cè)?NET Framework.NET Framework程序集中是作為程序集中是作為類的公共靜態(tài)方法實(shí)現(xiàn)的。類的公共靜態(tài)方法實(shí)現(xiàn)的。9.1.1 9.1.1 存儲(chǔ)過程概述存儲(chǔ)過程概述9SQL Server 2005返回本節(jié)首頁(yè)（2 2）擴(kuò)展存儲(chǔ)過程）擴(kuò)展存儲(chǔ)過程 擴(kuò)展存儲(chǔ)過程允許您使用編程語(yǔ)言（例如C）創(chuàng)建自己的外部例程。擴(kuò)展存儲(chǔ)過程是指SQL Server的

9、實(shí)例可以動(dòng)態(tài)加載和運(yùn)行的DLL。注意：CLR集成提供了更為可靠和安全的替代方法來編寫擴(kuò)展存儲(chǔ)過程。（3 3）系統(tǒng)存儲(chǔ)過程）系統(tǒng)存儲(chǔ)過程SQL Server 2005中的許多管理活動(dòng)都是通過一種特殊的存儲(chǔ)過程執(zhí)行的，這種存儲(chǔ)過程被稱為系統(tǒng)存儲(chǔ)過程。例如，sys.sp_changedbowner。 SQL Server支持在SQL Server和外部程序之間提供一個(gè)接口以實(shí)現(xiàn)各種維護(hù)活動(dòng)的系統(tǒng)存儲(chǔ)過程。這些擴(kuò)展存儲(chǔ)程序使用xp_前綴。9.1.1 9.1.1 存儲(chǔ)過程概述存儲(chǔ)過程概述10SQL Server 2005返回本節(jié)首頁(yè)1 1、存儲(chǔ)過程的設(shè)計(jì)規(guī)則、存儲(chǔ)過程的設(shè)計(jì)規(guī)則1）CREATE PROC

10、EDURE定義自身可以包括任意數(shù)量和類型的SQL語(yǔ)句，除了：CREATE AGGREGATE 、CREATE RULE、CREATE DEFAULT、CREATE SCHEMA、CREATE或ALTER FUNCTION、CREATE或ALTER TRIGGER、CREATE或ALTER PROCEDURE、CREATE或ALTER VIEW、SET PARSEONLY、SET SHOWPLAN_ALL、SET SHOWPLAN_TEXT、SET SHOWPLAN_XML、USE database_name。2）其它數(shù)據(jù)庫(kù)對(duì)象均可在存儲(chǔ)過程中創(chuàng)建并被引用。3）可以在存儲(chǔ)過程內(nèi)引用臨時(shí)表。4）存

11、儲(chǔ)過程內(nèi)創(chuàng)建的本地臨時(shí)表，退出該存儲(chǔ)過程后，臨時(shí)表將消失。5）存儲(chǔ)過程A調(diào)用存儲(chǔ)過程B，則B可以訪問A創(chuàng)建的所有對(duì)象。6）遠(yuǎn)程存儲(chǔ)過程不參與事務(wù)處理，不能回滾遠(yuǎn)程存儲(chǔ)過程的更改。7）存儲(chǔ)過程中的參數(shù)的最大數(shù)目為 2100；存儲(chǔ)過程中的局部變量的最大數(shù)目?jī)H受可用內(nèi)存的限制；根據(jù)可用內(nèi)存的不同，存儲(chǔ)過程最大可達(dá) 128 MB。9.1.2 9.1.2 設(shè)計(jì)存儲(chǔ)過程設(shè)計(jì)存儲(chǔ)過程11SQL Server 2005返回本節(jié)首頁(yè)2 2、限定存儲(chǔ)過程內(nèi)的名稱、限定存儲(chǔ)過程內(nèi)的名稱l 在存儲(chǔ)過程內(nèi)，如果用于語(yǔ)句（例如SELECT或INSERT）的對(duì)象名沒有限定架構(gòu)，則架構(gòu)將默認(rèn)為該存儲(chǔ)過程的架構(gòu)。l在存儲(chǔ)過程內(nèi)

12、，如果創(chuàng)建該存儲(chǔ)過程的用戶沒有限定 SELECT、INSERT、UPDATE或DELETE語(yǔ)句中引用的表名或視圖名，則默認(rèn)情況下，通過該存儲(chǔ)過程對(duì)這些表進(jìn)行的訪問將受到該過程創(chuàng)建者的權(quán)限的限制。l 如果有其他用戶要使用存儲(chǔ)過程，則用于所有數(shù)據(jù)定義語(yǔ)言(DDL)語(yǔ)句（例如CREATE、ALTER或DROP語(yǔ)句，DBCC語(yǔ)句，EXECUTE和動(dòng)態(tài)SQL語(yǔ)句）的對(duì)象名應(yīng)該用該對(duì)象架構(gòu)的名稱來限定。l如果沒有指定架構(gòu)名稱，SQL Server將首先嘗試使用調(diào)用方的默認(rèn)架構(gòu)或用戶在EXECUTE AS子句中指定的架構(gòu)來解析對(duì)象名稱，然后嘗試使用dbo架構(gòu)。9.1.2 9.1.2 設(shè)計(jì)存儲(chǔ)過程設(shè)計(jì)存儲(chǔ)過程

13、12SQL Server 2005返回本節(jié)首頁(yè)3 3、加密過程定義、加密過程定義WITH ENCRYPTION子句加密存儲(chǔ)過程定義，任何人將無(wú)法解密。4 4、SETSET語(yǔ)句選項(xiàng)語(yǔ)句選項(xiàng) 當(dāng)創(chuàng)建或更改T-SQL存儲(chǔ)過程后，數(shù)據(jù)庫(kù)引擎將保存SET QUOTED_IDENTIFIERQUOTED_IDENTIFIER和SET ANSI_NULLSSET ANSI_NULLS的設(shè)置。執(zhí)行存儲(chǔ)過程時(shí)，將使用這些原始設(shè)置。因此，在執(zhí)行存儲(chǔ)過程時(shí)，將忽略任何客戶端會(huì)話的SET QUOTED_IDENTIFIER和SET ANSI_NULLS設(shè)置。在存儲(chǔ)過程中出現(xiàn)的SET QUOTED_IDENTIFIER

14、和SET ANSI_NULLS語(yǔ)句不影響存儲(chǔ)過程的功能。9.1.2 9.1.2 設(shè)計(jì)存儲(chǔ)過程設(shè)計(jì)存儲(chǔ)過程13SQL Server 2005返回本節(jié)首頁(yè)CREATE PROCEDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n創(chuàng)建存儲(chǔ)過程前，請(qǐng)考慮下列事項(xiàng)：創(chuàng)建存儲(chǔ)過程前，請(qǐng)考慮下列事項(xiàng)：1）CREATE PROCED

15、URE 只能單獨(dú)在一個(gè)批處理中使用；2）存儲(chǔ)過程是架構(gòu)作用域內(nèi)的對(duì)象，其名必須遵守標(biāo)識(shí)符規(guī)則；3）只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過程。創(chuàng)建存儲(chǔ)過程時(shí)，應(yīng)指定：創(chuàng)建存儲(chǔ)過程時(shí)，應(yīng)指定：1）所有輸入?yún)?shù)和向調(diào)用過程或批處理返回的輸出參數(shù)；2）執(zhí)行數(shù)據(jù)庫(kù)操作（包括調(diào)用其它過程）的編程語(yǔ)句；3）返回至調(diào)用過程或批處理以表明成功或失敗的狀態(tài)值。9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程14SQL Server 2005返回本節(jié)首頁(yè)臨時(shí)存儲(chǔ)過程：臨時(shí)存儲(chǔ)過程：n專用和全局臨時(shí)存儲(chǔ)過程與臨時(shí)表類似。n#表示本地臨時(shí)存儲(chǔ)過程，n#表示全局臨時(shí)存儲(chǔ)過程，nSQL Serv

16、er關(guān)閉后，這些過程將不復(fù)存在。9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程15SQL Server 2005返回本節(jié)首頁(yè)nT-SQL T-SQL 語(yǔ)句或批處理可以包含嵌入?yún)?shù)。語(yǔ)句或批處理可以包含嵌入?yún)?shù)。nsp_executesqlsp_executesql用于執(zhí)行可以多次重復(fù)使用或動(dòng)態(tài)生成的用于執(zhí)行可以多次重復(fù)使用或動(dòng)態(tài)生成的 T-T-SQL SQL 語(yǔ)句或批處理。語(yǔ)句或批處理。n其語(yǔ)法:sp_executesql stmt=stmt ,params=Nparameter_name data_type OUTPUT,.n,param1=value

17、1,.n9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程16SQL Server 2005返回本節(jié)首頁(yè)例例9-1 9-1 建立含參數(shù)的建立含參數(shù)的SQLSQL命令字符串，并指定參數(shù)值并執(zhí)行。命令字符串，并指定參數(shù)值并執(zhí)行。DECLARE IntVariable int;SQLString nvarchar(500),ParmDefinition nvarchar(500);SET SQLString =NSELECT * FROM AdventureWorks.HumanResources.Employee WHERE ManagerID = Manage

18、rID; - 建立SQL命令字符串SET ParmDefinition=NManagerID tinyint;SET IntVariable=197; /SET IntVariable=197; /* *用參數(shù)值執(zhí)行用參數(shù)值執(zhí)行SQLSQL命令字符串命令字符串* */ /EXECUTE sp_executesql SQLString,ParmDefinition,ManagerID=IntVariable;SET IntVariable = 109; /SET IntVariable = 109; /* *用另一個(gè)參數(shù)值執(zhí)行用另一個(gè)參數(shù)值執(zhí)行SQLSQL命令字符串命令字符串* */ /EXEC

19、UTE sp_executesql SQLString, ParmDefinition,ManagerID=IntVariable;9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程17SQL Server 2005返回本節(jié)首頁(yè)例例9-2 9-2 使用帶有復(fù)雜使用帶有復(fù)雜 SELECT SELECT 的簡(jiǎn)單過程，返回某個(gè)視圖中的所有雇員的簡(jiǎn)單過程，返回某個(gè)視圖中的所有雇員（提供了姓名）、他們的職務(wù)和部門名稱。該存儲(chǔ)過程不使用任何參數(shù)。（提供了姓名）、他們的職務(wù)和部門名稱。該存儲(chǔ)過程不使用任何參數(shù)。IF OBJECT_ID(HumanResources.usp

20、_GetAllEmployees,P) IS NOT NULLDROP PROCEDURE HumanResources.usp_GetAllEmployees;CREATE PROCEDURECREATE PROCEDURE HumanResources.usp_GetAllEmployeesHumanResources.usp_GetAllEmployees AS AS SELECT LastName,FirstName,JobTitle,Department FROM HumanResources.vEmployeeDepartment;GO - usp_GetAllEmployees

21、存儲(chǔ)過程可通過以下方法執(zhí)行：EXECUTE HumanResources.usp_GetAllEmployees;GO -或如下HumanResources.usp_GetAllEmployees; -如SP是批處理中的第一條命令9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程18SQL Server 2005返回本節(jié)首頁(yè)（1 1）指定參數(shù)）指定參數(shù)n存儲(chǔ)過程通過其參數(shù)與調(diào)用程序通信。n當(dāng)程序執(zhí)行存儲(chǔ)過程時(shí)，可通過存儲(chǔ)過程的參數(shù)向該存儲(chǔ)過程傳遞值。n這些值可用作 T-SQL 編程語(yǔ)言中的標(biāo)準(zhǔn)變量。n存儲(chǔ)過程也可通過 OUTPUT 參數(shù)將值返回至調(diào)用程序。n

22、一個(gè)存儲(chǔ)過程可有多達(dá) 2100 個(gè)參數(shù)，每個(gè)參數(shù)都有名稱、數(shù)據(jù)類型、方向和默認(rèn)值。9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程19SQL Server 2005返回本節(jié)首頁(yè)（2 2）從存儲(chǔ)過程中返回?cái)?shù)據(jù)：）從存儲(chǔ)過程中返回?cái)?shù)據(jù)：1 1）使用）使用 OUTPUT OUTPUT 參數(shù)返回?cái)?shù)據(jù)參數(shù)返回?cái)?shù)據(jù) 例例9-79-7 CREATE PROCEDURE Sales.usp_GetEmployeeSalesYTD CREATE PROCEDURE Sales.usp_GetEmployeeSalesYTD SalesPerson nvarchar(50),

23、SalesYTD money OUTPUT AS AS SELECT SalesYTD=SalesYTD FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.EmployeeID = sp.SalesPersonID WHERE LastName=SalesPerson;9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程20SQL Server 2005返回本節(jié)首頁(yè)DECLARE SalesYTDBySalesPerson money; - 定義接收存儲(chǔ)過程輸出值的變量EX

24、ECUTE Sales.usp_GetEmployeeSalesYTD NBlythe, SalesYTD=SalesYTDBySalesPerson OUTPUTOUTPUT; -調(diào)用存儲(chǔ)過程得到輸出值PRINT 該雇員本年度至盡的銷售總額為+convert(varchar(10),SalesYTDBySalesPerson);9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程21SQL Server 2005返回本節(jié)首頁(yè)2 2）使用返回代碼返回?cái)?shù)據(jù)）使用返回代碼返回?cái)?shù)據(jù)n存儲(chǔ)過程可返回一個(gè)整數(shù)的“返回代碼”，表明執(zhí)行狀態(tài)。n使用 RETURNRETURN

25、 語(yǔ)句指定存儲(chǔ)過程的返回代碼。n返回代碼保存到變量中，才能在調(diào)用程序時(shí)使用返回代碼值。n例如，使用int數(shù)據(jù)類型的賦值變量result保存存儲(chǔ)過程my_proc的返回代碼： DECLARE result int; EXECUTE result = my_proc; DECLARE result int; EXECUTE result = my_proc;n返回代碼通常用在存儲(chǔ)過程內(nèi)的控制流塊中，為每種可能的錯(cuò)誤情況設(shè)置返回代碼值。n可以在T-SQL語(yǔ)句后使用ERROR函數(shù)，來檢測(cè)該語(yǔ)句執(zhí)行過程中是否有錯(cuò)誤發(fā)生。9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過

26、程22SQL Server 2005返回本節(jié)首頁(yè)3 3）在）在OUTPUTOUTPUT參數(shù)中使用參數(shù)中使用cursor cursor 數(shù)據(jù)類型數(shù)據(jù)類型T-SQLT-SQL存儲(chǔ)過程只能將存儲(chǔ)過程只能將cursorcursor用于用于VARYINGVARYING和和OUTPUTOUTPUT參數(shù)。參數(shù)。CREATE PROCEDURE dbo.currency_cursor CREATE PROCEDURE dbo.currency_cursor currency_cursor CURSOR VARYING OUTPUTAS SET currency_cursor=CURSOR FORWARD_ON

27、LY STATIC FOR SELECT CurrencyCode, Name FROM Sales.Currency;OPEN currency_cursor;DECLARE MyCur CURSOR;EXEC dbo.currency_cursor currency_cursor=MyCur OUTPUT; -執(zhí)行并返回Cursor變量WHILE (FETCH_STATUS = 0) FETCH NEXT FROM MyCur;-沒有真正取出值并加以處理CLOSE MyCur; DEALLOCATE MyCur;9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建

28、存儲(chǔ)過程23SQL Server 2005返回本節(jié)首頁(yè)（3 3）嵌套存儲(chǔ)過程）嵌套存儲(chǔ)過程n當(dāng)一個(gè)存儲(chǔ)過程通過引用CLR例程、類型或聚合來調(diào)用另一個(gè)存儲(chǔ)過程或執(zhí)行托管代碼時(shí)，存儲(chǔ)過程將被嵌套。n嵌套存儲(chǔ)過程和托管代碼引用最高可達(dá)32級(jí)。n每當(dāng)調(diào)用的存儲(chǔ)過程或托管代碼引用開始執(zhí)行，嵌套級(jí)別就增加一級(jí)；執(zhí)行完成后，嵌套級(jí)別就減少一級(jí)。n試圖超過最高 32 級(jí)的嵌套將導(dǎo)致整個(gè)調(diào)用鏈?zhǔn)?。n正在執(zhí)行的存儲(chǔ)過程的當(dāng)前嵌套級(jí)別存儲(chǔ)在NESTLEVEL函數(shù)中。n盡管嵌套限制為32級(jí)，但SQL Server對(duì)在給定的存儲(chǔ)過程中可以調(diào)用的存儲(chǔ)過程數(shù)量沒有限制。因而只要從屬存儲(chǔ)過程不調(diào)用其它從屬存儲(chǔ)過程且不超過最

29、大嵌套級(jí)別即可。9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程24SQL Server 2005返回本節(jié)首頁(yè)在在SQL Server 2005SQL Server 2005中，可以基于中，可以基于.NET Framework.NET Framework公共語(yǔ)言公共語(yǔ)言運(yùn)行時(shí)運(yùn)行時(shí)(CLR)(CLR)創(chuàng)建中的程序集使用編程方法創(chuàng)建存儲(chǔ)過程：具創(chuàng)建中的程序集使用編程方法創(chuàng)建存儲(chǔ)過程：具體步驟為：體步驟為：1）使用.NET Framework支持的語(yǔ)言將存儲(chǔ)過程定義為類的靜態(tài)方法。然后，使用適當(dāng)?shù)恼Z(yǔ)言編譯器編譯該類，在.NET Framework中生成程序集；

30、2）使用CREATE ASSEMBLY語(yǔ)句在SQL Server中注冊(cè)程序集；3）通過使用CREATE PROCEDURE語(yǔ)句創(chuàng)建引用注冊(cè)程序集的存儲(chǔ)過程。創(chuàng)建、修改或刪除程序集命令：創(chuàng)建、修改或刪除程序集命令：CREATE ASSEMBLYCREATE ASSEMBLY、ALTER ALTER ASSEMBLYASSEMBLY、DROP ASSEMBLYDROP ASSEMBLY；創(chuàng)建；創(chuàng)建CLRCLR存儲(chǔ)過程也通過存儲(chǔ)過程也通過CREATE CREATE PROCEDUREPROCEDURE命令。命令。9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-創(chuàng)建創(chuàng)建CLRCLR存儲(chǔ)過

31、程存儲(chǔ)過程25SQL Server 2005返回本節(jié)首頁(yè)n若要執(zhí)行存儲(chǔ)過程，可以使用若要執(zhí)行存儲(chǔ)過程，可以使用T-SQL EXECUTET-SQL EXECUTE語(yǔ)句。如果存儲(chǔ)過程是批處語(yǔ)句。如果存儲(chǔ)過程是批處理中的第一條語(yǔ)句，那么不使用理中的第一條語(yǔ)句，那么不使用EXECUTEEXECUTE關(guān)鍵字也可以執(zhí)行存儲(chǔ)過程。關(guān)鍵字也可以執(zhí)行存儲(chǔ)過程。n系統(tǒng)存儲(chǔ)過程以字符sp_開頭。它們物理上存儲(chǔ)于資源數(shù)據(jù)庫(kù)中，但邏輯上出現(xiàn)在 SQL Server實(shí)例的每個(gè)系統(tǒng)定義和用戶定義數(shù)據(jù)庫(kù)的sys架構(gòu)中。n建議使用建議使用 sys sys 架構(gòu)名稱對(duì)所有系統(tǒng)存儲(chǔ)名稱進(jìn)行限定，以防止名稱沖突。架構(gòu)名稱對(duì)所有系統(tǒng)

32、存儲(chǔ)名稱進(jìn)行限定，以防止名稱沖突。以下示例說明執(zhí)行系統(tǒng)存儲(chǔ)過程的推薦方法。以下示例說明執(zhí)行系統(tǒng)存儲(chǔ)過程的推薦方法。EXEC sys.sp_who;EXEC sys.sp_who; 下列示例說明執(zhí)行系統(tǒng)存儲(chǔ)過程的向后兼容方法。 EXEC sp_who; EXEC master.dbo.sp_who; EXEC mydatabase.sp_who;EXEC dbo.sp_who; EXEC mydatabase.dbo.sp_who;9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程26SQL Server 2005返回本節(jié)首頁(yè)（1 1）數(shù)據(jù)庫(kù)排序規(guī)則匹配）數(shù)據(jù)

33、庫(kù)排序規(guī)則匹配 SQL Server 2005 與系統(tǒng)過程名稱匹配時(shí)調(diào)用數(shù)據(jù)庫(kù)排序規(guī)則。如果在具有區(qū)分大小寫的排序規(guī)則的數(shù)據(jù)庫(kù)上下文中執(zhí)行，以下代碼將失敗： exec SP_heLP; - 因?yàn)镾P_heLP與sp_help不同，執(zhí)行失敗。 使用sys.system_objects和sys.system_parameters目錄視圖可以顯示確切的系統(tǒng)存儲(chǔ)過程名稱。（2 2）執(zhí)行系統(tǒng)擴(kuò)展存儲(chǔ)過程）執(zhí)行系統(tǒng)擴(kuò)展存儲(chǔ)過程 系統(tǒng)擴(kuò)展存儲(chǔ)過程以字符xp_開頭。它們物理上存儲(chǔ)在資源數(shù)據(jù)庫(kù)中，但邏輯上出現(xiàn)在SQL Server實(shí)例的每個(gè)系統(tǒng)定義和用戶定義數(shù)據(jù)庫(kù)的sys架構(gòu)中。例如：EXEC sys.xp_s

34、ubdirs c:;（3 3）執(zhí)行用戶定義存儲(chǔ)過程）執(zhí)行用戶定義存儲(chǔ)過程 執(zhí)行用戶定義存儲(chǔ)過程，強(qiáng)烈建議至少用架構(gòu)名稱限定存儲(chǔ)過程名稱。9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程27SQL Server 2005返回本節(jié)首頁(yè)（4 4）自動(dòng)執(zhí)行存儲(chǔ)過程）自動(dòng)執(zhí)行存儲(chǔ)過程nSQL Server 2005啟動(dòng)時(shí)將執(zhí)行標(biāo)記為要自動(dòng)執(zhí)行的存儲(chǔ)過程。n作用：定期執(zhí)行操作，或者作為后臺(tái)進(jìn)程運(yùn)行存儲(chǔ)過程。使該存儲(chǔ)過程完成tempdb中的系統(tǒng)或維護(hù)任務(wù)，如創(chuàng)建全局臨時(shí)表。n只有系統(tǒng)管理員只有系統(tǒng)管理員(sa)(sa)可以將存儲(chǔ)過程標(biāo)記為自動(dòng)執(zhí)行。另外，可以將存儲(chǔ)過程標(biāo)

35、記為自動(dòng)執(zhí)行。另外，該存儲(chǔ)過程必須在該存儲(chǔ)過程必須在master master 數(shù)據(jù)庫(kù)中并由數(shù)據(jù)庫(kù)中并由 sa sa 所有，而且不能有所有，而且不能有輸入或輸出參數(shù)。輸入或輸出參數(shù)。使用sp_procoption可以：1）將現(xiàn)有存儲(chǔ)過程指定為啟動(dòng)過程；2）阻止過程在SQL Server啟動(dòng)時(shí)執(zhí)行。使用sp_configure設(shè)置SQL Server scan for startup procs配置選項(xiàng)以防止在SQL Server啟動(dòng)時(shí)自動(dòng)執(zhí)行所有存儲(chǔ)過程。9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程28SQL Server 2005返回本節(jié)首頁(yè)n刪除并

36、重新創(chuàng)建存儲(chǔ)過程時(shí)，與其關(guān)聯(lián)的所有權(quán)限都將丟失。刪除并重新創(chuàng)建存儲(chǔ)過程時(shí)，與其關(guān)聯(lián)的所有權(quán)限都將丟失。n更改存儲(chǔ)過程或參數(shù)定義，但為該存儲(chǔ)過程定義的權(quán)限將保留，更改存儲(chǔ)過程或參數(shù)定義，但為該存儲(chǔ)過程定義的權(quán)限將保留，并且不會(huì)影響任何相關(guān)的存儲(chǔ)過程或觸發(fā)器。并且不會(huì)影響任何相關(guān)的存儲(chǔ)過程或觸發(fā)器。n修改存儲(chǔ)過程以加密其定義或使其在每次執(zhí)行時(shí)重新編譯。修改存儲(chǔ)過程以加密其定義或使其在每次執(zhí)行時(shí)重新編譯。n更改過程名稱或定義可能導(dǎo)致所有相關(guān)對(duì)象在執(zhí)行時(shí)失敗。更改過程名稱或定義可能導(dǎo)致所有相關(guān)對(duì)象在執(zhí)行時(shí)失敗。 ALTER PROCEDURE procedure_name ;number parame

37、ter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-修改存儲(chǔ)過程修改存儲(chǔ)過程29SQL Server 2005返回本節(jié)首頁(yè)系統(tǒng)存儲(chǔ)過程和目錄視圖可提供有關(guān)存儲(chǔ)過程的信息：系統(tǒng)存儲(chǔ)過程和目錄視圖可提供有關(guān)存儲(chǔ)過程的信息：1）查看存儲(chǔ)過程的定義。即查看用于創(chuàng)建存儲(chǔ)過程的 T-SQL 語(yǔ)句。這對(duì)于沒有用于創(chuàng)建存儲(chǔ)過程的 T-SQL

38、 腳本文件的用戶是很有用的。 2）獲得有關(guān)存儲(chǔ)過程的信息（如存儲(chǔ)過程的架構(gòu)、創(chuàng)建時(shí)間及其參數(shù)）。3）列出指定存儲(chǔ)過程所使用的對(duì)象及使用指定存儲(chǔ)過程的過程。此信息可用來識(shí)別那些受數(shù)據(jù)庫(kù)中某個(gè)對(duì)象的更改或刪除影響的過程。n若要查看存儲(chǔ)過程的定義：若要查看存儲(chǔ)過程的定義：sys.sql_modules、OBJECT_DEFINITION、sp_helptext。n查看有關(guān)存儲(chǔ)過程的信息：查看有關(guān)存儲(chǔ)過程的信息：sys.objects 、cedures 、sys.parameters 、sys.numbered_procedures 、sys.numbered_procedure_par

39、ameters 、sp_help 。n查看存儲(chǔ)過程的依賴關(guān)系：查看存儲(chǔ)過程的依賴關(guān)系：sys.sql_dependencies 、sp_depends。n查看有關(guān)擴(kuò)展存儲(chǔ)過程的信息：查看有關(guān)擴(kuò)展存儲(chǔ)過程的信息：sp_helpextendedproc。9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-查看存儲(chǔ)過程查看存儲(chǔ)過程30SQL Server 2005返回本節(jié)首頁(yè)刪除存儲(chǔ)過程命令為：刪除存儲(chǔ)過程命令為：DROP PROC|PROCEDURE schema_cedure,.n；刪除擴(kuò)展存儲(chǔ)過程的命令為：刪除擴(kuò)展存儲(chǔ)過程的命令為：sp_dropextendedpro

40、c functname=procedure。9.1.3 9.1.3 實(shí)現(xiàn)和管理存儲(chǔ)過程實(shí)現(xiàn)和管理存儲(chǔ)過程-刪除存儲(chǔ)過程刪除存儲(chǔ)過程31SQL Server 20059.2 DML 9.2 DML 觸發(fā)器觸發(fā)器 9.2.1 DML 觸發(fā)器概述 9.2.2 設(shè)計(jì)DML觸發(fā)器 9.2.3 實(shí)現(xiàn)和管理 DML 觸發(fā)器 返回本節(jié)首頁(yè)32SQL Server 2005返回本節(jié)首頁(yè)1 1、了解、了解 DMLDML觸發(fā)器觸發(fā)器n兩種機(jī)制強(qiáng)制執(zhí)行業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性：約束和觸發(fā)器。n觸發(fā)器是特殊的存儲(chǔ)過程，由DBMS自動(dòng)執(zhí)行。nSQL ServerSQL Server包括兩大類觸發(fā)器：包括兩大類觸發(fā)器：DDL觸

41、發(fā)器是SQL Server 2005的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義語(yǔ)言(DDL)事件時(shí)將調(diào)用這些觸發(fā)器。當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言(DML)事件時(shí)將調(diào)用 DML 觸發(fā)器。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語(yǔ)句、UPDATE語(yǔ)句或DELETE語(yǔ)句。nDML觸發(fā)器可以查詢其它表，還可以包含復(fù)雜的T-SQL語(yǔ)句。n將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待，錯(cuò)誤發(fā)生，則整個(gè)事務(wù)即自動(dòng)回滾。9.2.1 DML 9.2.1 DML 觸發(fā)器概述觸發(fā)器概述33SQL Server 2005返回本節(jié)首頁(yè)DMLDML觸發(fā)器在以下方面非常有用：觸發(fā)器在以下方面非常有用：

42、 DML觸發(fā)器可通過數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。不過，通過級(jí)聯(lián)引用完整性約束可以更有效地進(jìn)行這些更改； DML觸發(fā)器可以防止惡意或錯(cuò)誤的INSERT、UPDATE以及DELETE操作，并強(qiáng)制執(zhí)行比CHECK約束定義的限制更為復(fù)雜的其它限制。與CHECK約束不同，DML觸發(fā)器可以引用其它表中的列。 DML觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施；1.一個(gè)表中的多個(gè)同類DML觸發(fā)器（INSERT、UPDATE或DELETE）允許采取多個(gè)不同的操作來響應(yīng)同一個(gè)修改語(yǔ)句。9.2.1 DML 9.2.1 DML 觸發(fā)器概述觸發(fā)器概述34SQL Server 2005返回本節(jié)首頁(yè)2 2、

43、DML DML 觸發(fā)器的類型觸發(fā)器的類型lAFTER/FORAFTER/FOR觸發(fā)器：觸發(fā)器：在INSERT、UPDATE或DELETE語(yǔ)句操作之后執(zhí)行AFTER觸發(fā)器，只能在表上指定該類觸發(fā)器。l INSTEAD OFINSTEAD OF觸發(fā)器：觸發(fā)器：執(zhí)行INSTEAD OF觸發(fā)器代替通常的觸發(fā)動(dòng)作?？蔀橐粋€(gè)或多個(gè)基表的視圖定義INSTEAD OF觸發(fā)器，而這些觸發(fā)器能夠擴(kuò)展視圖可支持的更新類型。 CLRCLR觸發(fā)器：觸發(fā)器：CLR觸發(fā)器可以是AFTER觸發(fā)器或INSTEAD OF觸發(fā)器。CLR觸發(fā)器還可以是DDL觸發(fā)器。CLR觸發(fā)器將執(zhí)行在托管代碼（在.NET Framework 中創(chuàng)建

44、并在SQL Server中上載的程序集的成員）中編寫的方法，而不用執(zhí)行T-SQL存儲(chǔ)過程。9.2.1 DML 9.2.1 DML 觸發(fā)器概述觸發(fā)器概述- DML - DML 觸發(fā)器的類型觸發(fā)器的類型35SQL Server 2005返回本節(jié)首頁(yè)1 1、DMLDML觸發(fā)器計(jì)劃指南觸發(fā)器計(jì)劃指南 SQL Server 2005提供了兩個(gè)用于設(shè)計(jì)DML觸發(fā)器的選項(xiàng)：1）執(zhí)行INSTEAD OF觸發(fā)器代替通常的觸發(fā)操作，可對(duì)帶有一個(gè)或多個(gè)基表的視圖定義INSTEAD OF觸發(fā)器，以擴(kuò)展視圖可支持的更新類型；2）在執(zhí)行INSERT、UPDATE或DELETE語(yǔ)句操作之后執(zhí)行AFTER觸發(fā)器。2 2、指定

45、、指定DMLDML觸發(fā)器何時(shí)激發(fā)觸發(fā)器何時(shí)激發(fā)1）AFTER 觸發(fā)器在處理觸發(fā)操作（INSERT、UPDATE或DELETE）、INSTEAD OF 觸發(fā)器和約束之后激發(fā)?？赏ㄟ^指定AFTER或FOR來請(qǐng)求AFTER觸發(fā)器。2）INSTEAD OF將在處理約束前激發(fā)，以替代觸發(fā)操作。如果表有AFTER觸發(fā)器，它們將在處理約束之后激發(fā)。如果違反了約束，將回滾INSTEAD OF觸發(fā)器操作并且不執(zhí)行 AFTER 觸發(fā)器；3）每個(gè)表或視圖針對(duì)每個(gè)觸發(fā)操作（UPDATE、DELETE和INSERT）可有一個(gè)相應(yīng)的 INSTEAD OF 觸發(fā)器，可有多個(gè)相應(yīng)的AFTER觸發(fā)器。9.2.2 9.2.2 設(shè)

46、計(jì)設(shè)計(jì)DMLDML觸發(fā)器觸發(fā)器36SQL Server 2005返回本節(jié)首頁(yè)3 3、DMLDML觸發(fā)器執(zhí)行觸發(fā)器執(zhí)行n如果違反了約束，則永遠(yuǎn)不會(huì)執(zhí)行如果違反了約束，則永遠(yuǎn)不會(huì)執(zhí)行AFTERAFTER觸發(fā)器；因此，觸發(fā)器；因此，這些觸發(fā)器不能用于任何可能防止違反約束的處理。這些觸發(fā)器不能用于任何可能防止違反約束的處理。n執(zhí)行執(zhí)行INSTEAD OFINSTEAD OF觸發(fā)器，而不執(zhí)行觸發(fā)操作。觸發(fā)器，而不執(zhí)行觸發(fā)操作。 nDMLDML觸發(fā)器的性能開銷通常很低。觸發(fā)器的性能開銷通常很低。運(yùn)行DML觸發(fā)器所花時(shí)間大都用于引用其它表，這些表可能位于內(nèi)存中，也可能位于數(shù)據(jù)庫(kù)設(shè)備上。刪除的表(delete

47、d)和插入的表(inserted)始終位于內(nèi)存中。觸發(fā)器所引用的其它表的位置將確定操作所需的時(shí)間。9.2.2 9.2.2 設(shè)計(jì)設(shè)計(jì)DMLDML觸發(fā)器觸發(fā)器37SQL Server 2005返回本節(jié)首頁(yè)4 4、設(shè)計(jì)、設(shè)計(jì)INSTEAD OF INSTEAD OF 觸發(fā)器觸發(fā)器nINSTEAD OF觸發(fā)器主要優(yōu)點(diǎn)是可使不能更新的視圖支持更新。n基于多個(gè)基表的視圖必須使用INSTEAD OF觸發(fā)器來支持引用多個(gè)表中數(shù)據(jù)的插入、更新和刪除操作。nINSTEAD OF觸發(fā)器另一個(gè)優(yōu)點(diǎn)是可編寫這樣的邏輯代碼：在允許批處理的其它部分成功的同時(shí)拒絕批處理中的某些部分。n對(duì)于含有使用DELETE或UPDATE級(jí)

48、聯(lián)操作定義的外鍵的表，不能定義INSTEAD OF DELETE和INSTEAD OF UPDATE觸發(fā)器。9.2.2 9.2.2 設(shè)計(jì)設(shè)計(jì)DMLDML觸發(fā)器觸發(fā)器38SQL Server 2005返回本節(jié)首頁(yè)這里先說明，創(chuàng)建觸發(fā)器命令為create trigger，其語(yǔ)法為： CREATE TRIGGERCREATE TRIGGER trigger_name ON ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION ASAS IF UP

49、DATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n sql_statement .n 9.2.2 9.2.2 設(shè)計(jì)設(shè)計(jì)DMLDML觸發(fā)器觸發(fā)器39SQL Server 2005返回本節(jié)首頁(yè)CREATE TABLE PersonCREATE TABLE Person(SSN char(11) PRIMARY KEY,Name nvarchar(100),Add

50、ress nvarchar(100),Birthdate datetime)CREATE TABLE EmployeeTableCREATE TABLE EmployeeTable(EmployeeID int PRIMARY KEY,SSN char(11) UNIQUE, Department nvarchar(10),Salary money,CONSTRAINT FKEmpPer FOREIGN KEY (SSN) REFERENCES Person (SSN)CREATE TABLE PersonDuplicatesCREATE TABLE PersonDuplicates (SSN

51、 char(11),Name nvarchar(100),Address nvarchar(100),Birthdate datetime,InsertSNAME nchar(100),WhenInserted datetime)CREATE VIEW EmployeeCREATE VIEW Employee AS SELECT P.SSN as SSN,Name,Address,Birthdate, EmployeeID,Department,Salary FROM Person P,EmployeeTable E WHERE P.SSN=E.SSN 9.2.2 9.2.2 設(shè)計(jì)設(shè)計(jì)DMLD

52、ML觸發(fā)器觸發(fā)器40SQL Server 2005返回本節(jié)首頁(yè)INSTEAD OF INSTEAD OF 觸發(fā)器將行插入到單個(gè)視圖的多個(gè)基表中。在觸發(fā)器將行插入到單個(gè)視圖的多個(gè)基表中。在PersonDuplicatesPersonDuplicates表中記錄了插入具有重復(fù)社會(huì)保障號(hào)的行的嘗試。表中記錄了插入具有重復(fù)社會(huì)保障號(hào)的行的嘗試。EmployeeTable EmployeeTable 中的重復(fù)行中的重復(fù)行將更改為更新語(yǔ)句。將更改為更新語(yǔ)句。CREATE TRIGGERCREATE TRIGGER IO_Trig_INS_Employee ON Employee INSTEAD OF IN

53、SERT ASASBEGINBEGINSET NOCOUNT ON - 檢查人員重復(fù)情況，不重復(fù)則插入IF (NOT EXISTS (SELECT P.SSN FROM Person P,inserted I WHERE P.SSN=I.SSN) INSERT INTO Person SELECT SSN,Name,Address,Birthdate FROM inserted ELSE -重復(fù)則日志記錄于“人員重復(fù)表”中 INSERT INTO PersonDuplicates SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()

54、 FROM inserted9.2.2 9.2.2 設(shè)計(jì)設(shè)計(jì)DMLDML觸發(fā)器觸發(fā)器41SQL Server 2005返回本節(jié)首頁(yè)- 檢查雇員重復(fù)情況，不重復(fù)則插入IF (NOT EXISTS (SELECT E.SSN FROM EmployeeTable E, inserted WHERE E.SSN=inserted.SSN) INSERT INTO EmployeeTable SELECT EmployeeID,SSN, Department, Salary FROM insertedinsertedELSE -重復(fù)則改為對(duì)表EmployeeTable做修改操作 UPDATE Empl

55、oyeeTable SET EmployeeID = I.EmployeeID, Department = I.Department,Salary = I.Salary FROM EmployeeTable E, inserted I WHERE E.SSN = I.SSNENDEND9.2.2 9.2.2 設(shè)計(jì)設(shè)計(jì)DMLDML觸發(fā)器觸發(fā)器42SQL Server 2005返回本節(jié)首頁(yè)5 5、管理觸發(fā)器安全性、管理觸發(fā)器安全性n默認(rèn)情況下，在調(diào)用觸發(fā)器的用戶的上下文中執(zhí)行DML和DDL觸發(fā)器。n觸發(fā)器的調(diào)用方是執(zhí)行使觸發(fā)器運(yùn)行的語(yǔ)句的用戶。例如，如果用戶Mary執(zhí)行可以使DML觸發(fā)器 DML_

56、trigMary 運(yùn)行的DELETE 語(yǔ)句，則 DML_trigMary中的代碼將在Mary的用戶特權(quán)上下文中執(zhí)行。希望向數(shù)據(jù)庫(kù)或服務(wù)器實(shí)例中引入惡意代碼的用戶可以使用此默認(rèn)行為。 下面由用戶下面由用戶JohnDoeJohnDoe創(chuàng)建的創(chuàng)建的DDL DDL 觸發(fā)器：觸發(fā)器： CREATE TRIGGER DDL_trigJohnDoe ON DATABASE FOR ALTER_TABLE AS GRANT CONTROL SERVER TO JohnDoe; 9.2.2 9.2.2 設(shè)計(jì)設(shè)計(jì)DMLDML觸發(fā)器觸發(fā)器43SQL Server 2005返回本節(jié)首頁(yè) 創(chuàng)建創(chuàng)建DMLDML觸發(fā)器前應(yīng)

57、考慮下列問題：觸發(fā)器前應(yīng)考慮下列問題：lCREATE TRIGGER語(yǔ)句必須是批處理中的第一個(gè)語(yǔ)句，該語(yǔ)句后面的所有其它語(yǔ)句被解釋為CREATE TRIGGER語(yǔ)句定義的一部分；l創(chuàng)建DML觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，不能轉(zhuǎn)給其他用戶；lDML觸發(fā)器為數(shù)據(jù)庫(kù)對(duì)象，其名稱必須遵循標(biāo)識(shí)符的命名規(guī)則；lDML觸發(fā)器可引用當(dāng)前DB以外對(duì)象，但只能在當(dāng)前DB中創(chuàng)建它；l雖然DML觸發(fā)器可以引用臨時(shí)表，但不能對(duì)臨時(shí)表或系統(tǒng)表創(chuàng)建 DML 觸發(fā)器。不應(yīng)引用系統(tǒng)表，而應(yīng)使用信息架構(gòu)視圖；l對(duì)于含有用DELETE或UPDATE操作定義的外鍵的表，不能定義INSTEAD OF DELETE和INSTEAD

58、OF UPDATE觸發(fā)器；l雖然 TRUNCATE TABLE 語(yǔ)句類似于不帶 WHERE 子句的 DELETE 語(yǔ)句（用于刪除所有行），但它并不會(huì)觸發(fā)DELETE觸發(fā)器，因?yàn)門RUNCATE TABLE語(yǔ)句沒有記錄；1.WRITETEXT語(yǔ)句不會(huì)觸發(fā)INSERT或UPDATE觸發(fā)器。9.2.3 9.2.3 實(shí)現(xiàn)和管理實(shí)現(xiàn)和管理 DML DML 觸發(fā)器觸發(fā)器 44SQL Server 2005返回本節(jié)首頁(yè)創(chuàng)建創(chuàng)建DMLDML觸發(fā)器時(shí)需指定：觸發(fā)器時(shí)需指定：l名稱；l定義觸發(fā)器時(shí)所基于的表；l觸發(fā)器被觸發(fā)的時(shí)間；l激活觸發(fā)器語(yǔ)句為INSERT、UPDATE或DELETE的任意組合。l執(zhí)行觸發(fā)器操

59、作的編程語(yǔ)句。n一個(gè)表中可有多個(gè)給定類型的AFTER觸發(fā)器，每個(gè)觸發(fā)器只能應(yīng)用于一個(gè)表。n一個(gè)表只能具有一個(gè)給定類型的INSTEAD OF觸發(fā)器。n用定義觸發(fā)器時(shí)所基于的表或視圖的名稱架構(gòu)創(chuàng)建觸發(fā)器。nCREATE TRIGGER權(quán)限默認(rèn)授予定義觸發(fā)器的表所有者、sysadmin固定服務(wù)器角色以及db_owner和db_ddladmin固定數(shù)據(jù)庫(kù)角色的成員，并且不可轉(zhuǎn)讓。1.如果對(duì)某個(gè)視圖創(chuàng)建INSTEAD OF觸發(fā)器，如果視圖所有者不同時(shí)擁有視圖和觸發(fā)器所引用的基表，所有權(quán)鏈將斷開。9.2.3 9.2.3 實(shí)現(xiàn)和管理實(shí)現(xiàn)和管理 DML DML 觸發(fā)器觸發(fā)器 45SQL Server 2005

60、返回本節(jié)首頁(yè) 1 1、對(duì)、對(duì)DMLDML觸發(fā)器進(jìn)行編程觸發(fā)器進(jìn)行編程n幾乎所有可以編寫成批處理的 T-SQL 語(yǔ)句都可用于創(chuàng)建 DML 觸發(fā)器，下列語(yǔ)句除外：ALTER DATABASE、CREATE DATABASE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG。n此外，在對(duì)作為觸發(fā)操作的目標(biāo)的表或視圖使用下列T-SQL語(yǔ)句時(shí)，將不允許在DML觸發(fā)器的主體內(nèi)使用這些語(yǔ)句：CREATE INDEX、ALTER INDEX、DROP INDEX、DBCC DBREINDEX、ALTER PART