存儲(chǔ)過程和觸發(fā)器管理介紹和應(yīng)用

1、存儲(chǔ)過程觸發(fā)器1本章要點(diǎn)存儲(chǔ)過程概述 創(chuàng)建存儲(chǔ)過程 執(zhí)行存儲(chǔ)過程 存儲(chǔ)過程的參數(shù) 存儲(chǔ)過程的返回值 查看和修改存儲(chǔ)過程 刪除存儲(chǔ)過程 觸發(fā)器概述 inserted和deleted表 事務(wù)的概念及應(yīng)用 創(chuàng)建觸發(fā)器 修改和重命名觸發(fā)器 刪除觸發(fā)器 27.1 存儲(chǔ)過程在創(chuàng)建SQL Server數(shù)據(jù)庫應(yīng)用程序時(shí)，Transact-SQL語言是應(yīng)用程序和SQL Server數(shù)據(jù)庫之間的主要編程接口。可用如下兩種方法存儲(chǔ)和執(zhí)行Transact-SQL語句。(1) 將Transact-SQL程序保存在本地，創(chuàng)建向SQL Server發(fā)送命令并處理結(jié)果的應(yīng)用程序。(2) 可以將Transact-SQL程序保存

2、在SQL Server中，即存儲(chǔ)過程，在本地創(chuàng)建執(zhí)行存儲(chǔ)過程及處理結(jié)果的應(yīng)用程序。任何一組Transact-SQL語句構(gòu)成的代碼塊，都可以作為存儲(chǔ)過程保存起來。它在服務(wù)器端對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行處理，并將結(jié)果返回到客戶端。這樣就避免了從客戶端多次連接并訪問數(shù)據(jù)庫的操作，減少了網(wǎng)絡(luò)上的傳輸量，同時(shí)也提高了客戶端的工作效率（因?yàn)檫@些操作都是在服務(wù)器端完成的）。37.1.1 存儲(chǔ)過程概述 存儲(chǔ)過程是集中存儲(chǔ)在SQL Server中的SQL語句和流程控制語句的預(yù)編譯集合，用以實(shí)現(xiàn)某種任務(wù)（如查詢）。這些語句在一個(gè)名稱下存儲(chǔ)并作為一個(gè)單元進(jìn)行處理。4使用存儲(chǔ)過程的優(yōu)勢 可以減少客戶端代碼的重復(fù)。只需創(chuàng)建存

3、儲(chǔ)過程一次并將其存儲(chǔ)在數(shù)據(jù)庫中，以后即可在客戶端程序中多次調(diào)用該存儲(chǔ)過程。存儲(chǔ)過程可由在數(shù)據(jù)庫編程方面有專長的人員創(chuàng)建，并可獨(dú)立于程序源代碼而單獨(dú)修改。允許更快地執(zhí)行。如果某操作需要大量的Transact-SQL代碼或需要重復(fù)執(zhí)行，使用存儲(chǔ)過程將比在客戶端執(zhí)行Transact-SQL批代碼的執(zhí)行速度要快。存儲(chǔ)過程在服務(wù)器端經(jīng)過預(yù)編譯，生成查詢計(jì)劃，可以直接執(zhí)行，而在客戶端每次運(yùn)行Transact-SQL語句時(shí)，都要從客戶端重復(fù)發(fā)送，并且在SQL Server每次執(zhí)行這些語句時(shí)，都要對(duì)其進(jìn)行編譯和優(yōu)化。因此，存儲(chǔ)過程執(zhí)行速度更快。5使用存儲(chǔ)過程的優(yōu)勢 減少網(wǎng)絡(luò)流量。例如，一個(gè)需要數(shù)百行Trans

4、act-SQL代碼的操作由一條執(zhí)行過程代碼的單獨(dú)語句就可實(shí)現(xiàn)，而不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼?？梢院喕瘮?shù)據(jù)庫管理。例如，要修改某種查詢，如果查詢重復(fù)存放在客戶機(jī)上，則要在所有客戶機(jī)上修改查詢，而使用存儲(chǔ)過程可以集中修改?？勺鳛榘踩珯C(jī)制使用。例如，即使對(duì)于沒有直接執(zhí)行存儲(chǔ)過程中語句的權(quán)限的用戶，也可授予他們執(zhí)行該存儲(chǔ)過程的權(quán)限。6存儲(chǔ)過程的分類 系統(tǒng)存儲(chǔ)過程: SQL Server內(nèi)置的存儲(chǔ)過程，存儲(chǔ)在master庫中，主要用途是執(zhí)行SQL Server的某些管理功能、顯示有關(guān)數(shù)據(jù)庫和用戶的信息。系統(tǒng)存儲(chǔ)過程名以SP_開頭，可以在任何數(shù)據(jù)庫中執(zhí)行系統(tǒng)存儲(chǔ)過程。用戶存儲(chǔ)過程：用戶自行創(chuàng)建并存儲(chǔ)在用

5、戶數(shù)據(jù)庫中的存儲(chǔ)過程。臨時(shí)存儲(chǔ)過程：分為局部臨時(shí)存儲(chǔ)過程和全局臨時(shí)存儲(chǔ)過程。7存儲(chǔ)過程的分類 局部臨時(shí)存儲(chǔ)過程名稱以#開頭，存放在tempdb數(shù)據(jù)庫中，只由創(chuàng)建并連接的用戶使用，當(dāng)該用戶斷開連接時(shí)將自動(dòng)刪除局部臨時(shí)存儲(chǔ)過程。全局臨時(shí)存儲(chǔ)過程名稱以#開頭，存放在tempdb數(shù)據(jù)庫中，允許所有連接的用戶使用，在所有用戶斷開連接時(shí)自動(dòng)被刪除。遠(yuǎn)程存儲(chǔ)過程：位于遠(yuǎn)程服務(wù)器上的存儲(chǔ)過程。擴(kuò)展存儲(chǔ)過程：利用外部語言（如C）編寫的存儲(chǔ)過程，以彌補(bǔ)SQL Server的不足之處，擴(kuò)展新的功能87.1.2 創(chuàng)建存儲(chǔ)過程 使用CREATE PROCEDURE語句 使用企業(yè)管理器中的菜單命令 9CREATE PRO

6、CEDURE語句 CREATE PROC EDURE 存儲(chǔ)過程名;編號(hào) 參數(shù) 數(shù)據(jù)類型 VARYING = 默認(rèn)值 OUTPUT ,.nWITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTIONAS SQL語句 .n 10參數(shù)說明 存儲(chǔ)過程名必須符合標(biāo)識(shí)符的命名規(guī)則，且對(duì)于數(shù)據(jù)庫及其所有者必須是惟一的。要?jiǎng)?chuàng)建局部臨時(shí)存儲(chǔ)過程，可以在存儲(chǔ)過程名前面加一個(gè)#號(hào)，要?jiǎng)?chuàng)建全局臨時(shí)過程，可以在存儲(chǔ)過程名前面加兩個(gè)#號(hào)。完整的名稱（包括 # 或 #）不能超過128個(gè)字符。編號(hào)：可選整數(shù)，用來對(duì)同名的存儲(chǔ)過程分組，以便用一條DROP PROCEDURE語句即可將同組

7、的存儲(chǔ)過程一起刪除。11參數(shù)說明 參數(shù)：過程中的參數(shù)。在CREATE PROCEDURE語句中可以聲明一個(gè)或多個(gè)參數(shù)。用戶必須在執(zhí)行存儲(chǔ)過程時(shí)提供每個(gè)所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。參數(shù)名稱前需要使用符號(hào)。參數(shù)名稱必須符合標(biāo)識(shí)符的命名規(guī)則。每個(gè)過程的參數(shù)僅用于該過程本身。在其他過程中可以使用相同的參數(shù)名稱。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其他數(shù)據(jù)庫對(duì)象的名稱。12參數(shù)說明 數(shù)據(jù)類型：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括text、ntext和image）均可以用作存儲(chǔ)過程的參數(shù)。不過，cursor數(shù)據(jù)類型只能用于OUTPUT參數(shù)。如果指定的數(shù)據(jù)類型為cursor

8、，也必須同時(shí)指定VARYING和OUTPUT關(guān)鍵字。VARYING：指定作為輸出參數(shù)支持的結(jié)果集（由存儲(chǔ)過程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。13參數(shù)說明 默認(rèn)值：參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行存儲(chǔ)過程。默認(rèn)值必須是常量或NULL。如果要在存儲(chǔ)過程中對(duì)該參數(shù)使用LIKE關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、和）。OUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給EXECUTE。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。text、ntext和image參數(shù)可用作OUTPUT參數(shù)。使用OUTPUT關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。14參數(shù)說明 RE

9、COMPILE：表明不保存該存儲(chǔ)過程的執(zhí)行計(jì)劃，該存儲(chǔ)過程將在運(yùn)行時(shí)重新編譯。ENCRYPTION：指定SQL Server對(duì)syscomments表中包含本CREATE PROCEDURE語句文本的條目進(jìn)行加密AS：用于指定該存儲(chǔ)過程要執(zhí)行的操作。SQL語句：存儲(chǔ)過程中要包含的Transact-SQL語句。15例7-1 創(chuàng)建存儲(chǔ)過程“增加成績”，將表“學(xué)生”中所有學(xué)生的入學(xué)成績增加10%。 USE 學(xué)生管理 GO CREATE PROCEDURE 增加成績 AS UPDATE 學(xué)生 SET 入學(xué)成績 = 入學(xué)成績 + 10 GO 16例7-1 在創(chuàng)建一個(gè)存儲(chǔ)過程時(shí)，如果已經(jīng)存在同名的存儲(chǔ)過程

10、，則不允許創(chuàng)建新的存儲(chǔ)過程?？梢詫⒁陨洗a改寫為： USE 學(xué)生管理 -如果存在名稱為“增加成績”的存儲(chǔ)過程，則將其刪除 IF EXISTS (SELECT name FROM sysobjects WHERE name = 增加成績 AND type = P) DROP PROCEDURE 增加成績 GO CREATE PROCEDURE 增加成績 AS UPDATE 學(xué)生 SET 入學(xué)成績 = 入學(xué)成績 + 10 GO1718使用企業(yè)管理器創(chuàng)建存儲(chǔ)過程 在該對(duì)話框的文本框中有以下的默認(rèn)語句： CREATE PROCEDURE OWNER.PROCEDURE NAME AS 這是CREATE

11、 PROCEDURE語句的開始部分，存儲(chǔ)過程的具體文本需要用戶自己輸入。例如，將以上默認(rèn)語句修改成： CREATE PROCEDURE 增加成績 AS UPDATE 學(xué)生 SET 入學(xué)成績 = 入學(xué)成績 + 10 GO197.1.3 執(zhí)行存儲(chǔ)過程 EXEC UTE 返回狀態(tài) = 存儲(chǔ)過程名 | 存儲(chǔ)過程名變量 參數(shù)名稱 = 值 | 變量 OUTPUT | DEFAULT ,.n WITH RECOMPILE 20參數(shù)說明返回狀態(tài)：是一個(gè)可選的整型變量，保存存儲(chǔ)過程的返回狀態(tài)。這個(gè)變量在用于EXECUTE語句前，必須在批處理、存儲(chǔ)過程或函數(shù)中聲明過。存儲(chǔ)過程名：要調(diào)用的存儲(chǔ)過程的名稱。存儲(chǔ)過程名

12、變量：局部變量名，代表存儲(chǔ)過程的名稱。參數(shù)名稱：存儲(chǔ)過程的參數(shù)，在CREATE PROCEDURE語句中定義。參數(shù)名稱前必須加上符號(hào)。在使用格式“參數(shù)= 值”時(shí)，參數(shù)名稱和常量不一定按照CREATE PROCEDURE語句中定義的順序出現(xiàn)。但是，如果有一個(gè)參數(shù)使用“參數(shù)= 值”格式，則其他所有參數(shù)都必須使用這種格式。21參數(shù)說明值：過程中參數(shù)的值。如果沒有指定參數(shù)名稱，參數(shù)值必須以CREATE PROCEDURE語句中定義的順序給出。如果在CREATE PROCEDURE語句中定義了默認(rèn)值，用戶執(zhí)行該存儲(chǔ)過程時(shí)可以不必指定對(duì)應(yīng)的參數(shù)。如果該存儲(chǔ)過程使用了帶LIKE關(guān)鍵字的參數(shù)名稱，則默認(rèn)值必須

13、是常量，并且可以包含 %、_、 及 通配符。默認(rèn)值也可以為NULL。通常，定義存儲(chǔ)過程時(shí)會(huì)指定當(dāng)參數(shù)值為NULL時(shí)應(yīng)該執(zhí)行的操作。變量：是用來保存參數(shù)或者返回參數(shù)的變量。22參數(shù)說明OUTPUT：指定存儲(chǔ)過程必須返回一個(gè)參數(shù)。該存儲(chǔ)過程的匹配參數(shù)也必須由關(guān)鍵字OUTPUT創(chuàng)建。使用游標(biāo)變量作參數(shù)時(shí)使用該關(guān)鍵字。DEFAULT：根據(jù)存儲(chǔ)過程的定義，提供參數(shù)的默認(rèn)值。當(dāng)過程需要的參數(shù)值沒有事先定義好的默認(rèn)值，或缺少參數(shù)，或指定了DEFAULT關(guān)鍵字時(shí)，就會(huì)出錯(cuò)。WITH RECOMPILE：強(qiáng)制編譯新的計(jì)劃。如果所提供的參數(shù)為非典型參數(shù)或者數(shù)據(jù)有很大的改變，使用該選項(xiàng)。在以后的程序執(zhí)行中使用更改過

14、的計(jì)劃。該選項(xiàng)不能用于擴(kuò)展存儲(chǔ)過程。建議盡量少使用該選項(xiàng)，因?yàn)樗妮^多系統(tǒng)資源。23例7-2 執(zhí)行存儲(chǔ)過程“增加成績”，并查看執(zhí)行結(jié)果。 USE 學(xué)生管理 GO EXEC 增加成績 SELECT * FROM 學(xué)生 GO執(zhí)行結(jié)果:247.1.4 存儲(chǔ)過程的參數(shù) 為了提高存儲(chǔ)過程的靈活性，SQL Server 2000支持在存儲(chǔ)過程中使用參數(shù)。存儲(chǔ)過程的參數(shù)分為輸入?yún)?shù)和輸出參數(shù)兩種類型，輸入?yún)?shù)用于向存儲(chǔ)過程中帶入數(shù)據(jù)，而輸出參數(shù)則能將存儲(chǔ)過程中的數(shù)據(jù)返回到調(diào)用程序。在定義存儲(chǔ)過程時(shí),可以同時(shí)指定參數(shù),格式如下： 參數(shù)名 數(shù)據(jù)類型 =默認(rèn)值 OUTPUT, . n如果參數(shù)后面使用OUTPUT

15、關(guān)鍵字，則表明它是輸出參數(shù)。25例7-3 創(chuàng)建存儲(chǔ)過程add_proc，用于計(jì)算兩個(gè)參數(shù)之和并將其輸出。 CREATE PROCEDURE add_proc num1 INT = 0, num2 INT = 0 AS DECLARE num3 INT SET num3 = num1 + num2 PRINT num3該存儲(chǔ)過程定義了兩個(gè)參數(shù)num1和num2，它們都是輸入?yún)?shù),參數(shù)類型為INT,默認(rèn)值為0。26執(zhí)行例7-3不帶參數(shù)執(zhí)行： EXEC add_proc 運(yùn)行結(jié)果為： 0帶參數(shù)執(zhí)行： EXEC add_proc 13, 25 運(yùn)行結(jié)果為： 38 有時(shí)需要將存儲(chǔ)過程中的計(jì)算結(jié)果返回到調(diào)

16、用程序中，以便進(jìn)行進(jìn)一步的處理，此時(shí)就需要在存儲(chǔ)過程中使用輸出參數(shù)。 27例7-4 創(chuàng)建存儲(chǔ)過程add_proc1，用于計(jì)算兩個(gè)參數(shù)之和，并使用輸出參數(shù)返回結(jié)果。 CREATE PROCEDURE add_proc1 num1 INT = 0, num2 INT = 0, num3 INT OUTPUT -num3為輸出參數(shù) AS SET num3 = num1 + num228執(zhí)行例7-4 DECLARE num AS INTEXEC add_proc1 12, 23, num OUTPUTPRINT num運(yùn)行結(jié)果為：3829例7-5創(chuàng)建存儲(chǔ)過程AvgScore，用于根據(jù)給定的院系和班級(jí)名

17、稱計(jì)算平均成績，并使用輸出參數(shù)返回結(jié)果。 CREATE PROCEDURE AvgScore org varchar(100), -院系名稱，輸入?yún)?shù) class varchar(50), -班級(jí)名稱，輸入?yún)?shù) score float OUTPUT - 成績，輸出參數(shù) AS DECLARE orgid int SET orgid = 030例7-5- 根據(jù)參數(shù)中指定的院系名稱org, 獲取院系編號(hào)SELECT orgid = 記錄編號(hào) FROM 院系 WHERE 院系名稱=orgIF orgid = 0 BEGIN SET score = 0 PRINT 指定的院系記錄不存在 ENDELSE

18、BEGIN SELECT score = AVG(入學(xué)成績) FROM 學(xué)生 WHERE 所屬院系=orgid AND 班級(jí)=class GROUP BY 所屬院系, 班級(jí) END31執(zhí)行例7-5DECLARE score floatEXEC AvgScore 軟件系, 二班, score OUTPUTPRINT score運(yùn)行結(jié)果為： 584327.1.5 存儲(chǔ)過程的返回值 可以在存儲(chǔ)過程中使用RETURN語句返回一個(gè)狀態(tài)值，返回值只能是整數(shù)。33例7-6 創(chuàng)建存儲(chǔ)過程AvgScore1，根據(jù)給定的院系和班級(jí)名稱計(jì)算平均成績，并將結(jié)果使用輸出參數(shù)返回。如果指定的院系存在，則返回1，否則返回0

19、。 CREATE PROCEDURE AvgScore1 org varchar(100), class varchar(50), score float OUTPUT AS DECLARE orgid int SET orgid = 034例7-6 - 根據(jù)參數(shù)中指定的院系名稱org, 獲取院系編號(hào)SELECT orgid = 記錄編號(hào) FROM 院系 WHERE 院系名稱=orgIF orgid = 0 RETURN 0ELSE BEGIN SELECT score = AVG(入學(xué)成績) FROM 學(xué)生 WHERE 所屬院系=orgid AND 班級(jí)=class GROUP BY 所屬院

20、系, 班級(jí) RETURN 1 ENDGO35執(zhí)行例7-6 DECLARE score floatDECLARE result intEXEC result = AvgScore1 軟件系, 二班, score OUTPUT- 檢查返回值IF result = 1PRINT scoreELSEPRINT 沒有對(duì)應(yīng)的記錄367.1.6 查看和修改存儲(chǔ)過程 使用企業(yè)管理器查看和修改存儲(chǔ)過程 使用查詢分析器查看和修改存儲(chǔ)過程 使用企業(yè)管理器重命名存儲(chǔ)過程 使用ALTER PROCEDURE語句修改存儲(chǔ)過程使用sp_rename重命名存儲(chǔ)過程371. 使用企業(yè)管理器查看和修改存儲(chǔ)過程 在企業(yè)管理器中展開

21、指定的數(shù)據(jù)庫，選中“存儲(chǔ)過程”項(xiàng)，可以查看指定數(shù)據(jù)庫中定義的所有存儲(chǔ)過程。381. 使用企業(yè)管理器查看和修改存儲(chǔ)過程 雙擊指定的存儲(chǔ)過程，可以打開存儲(chǔ)過程屬性對(duì)話框，查看存儲(chǔ)過程的定義情況。用戶可以在存儲(chǔ)過程屬性對(duì)話框中修改存儲(chǔ)過程的代碼。 392. 使用查詢分析器查看和修改存儲(chǔ)過程 右鍵單擊要編輯的存儲(chǔ)過程，在彈出菜單中選擇“編輯”，可以在右鍵的窗格中顯示指定存儲(chǔ)過程的代碼。403. 使用企業(yè)管理器重命名存儲(chǔ)過程 在企業(yè)管理器，展開“數(shù)據(jù)庫”文件夾，選擇存儲(chǔ)過程所在的數(shù)據(jù)庫，單擊“存儲(chǔ)過程”文件夾。在右側(cè)的窗口中，列出了選擇數(shù)據(jù)庫的所有存儲(chǔ)過程。右擊要重命名的存儲(chǔ)過程，選擇“重命名”菜單項(xiàng)，

22、就可以在當(dāng)前位置上修改存儲(chǔ)過程的名字。414. 使用ALTER PROCEDURE語句修改存儲(chǔ)過程 ALTER PROC EDURE 存儲(chǔ)過程名;編號(hào) 參數(shù)名 數(shù)據(jù)類型 VARYING = 默認(rèn)值 OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTIONASSQL語句 .n 各參數(shù)含義與CREATE PROCEDURE語句相同42例7-7 使用ALTER PROCEDURE語句修改存儲(chǔ)過程“增加成績”，對(duì)其進(jìn)行加密處理。 USE 學(xué)生管理 GO ALTER PROCEDURE 增加成績 WITH ENCRYPTION AS UP

23、DATE 學(xué)生 SET 入學(xué)成績 = 入學(xué)成績 + 10指定以上語句后，在企業(yè)管理器中查看“增加成績”存儲(chǔ)過程，將彈出一個(gè)對(duì)話框，提示用戶存儲(chǔ)過程已經(jīng)加密，不能夠查看。435. 使用sp_rename重命名存儲(chǔ)過程 系統(tǒng)存儲(chǔ)過程sp_rename的功能是更改當(dāng)前數(shù)據(jù)庫中用戶創(chuàng)建對(duì)象（如表、列或用戶定義數(shù)據(jù)類型）的名稱。使用sp_rename重命名存儲(chǔ)過程的語法結(jié)構(gòu)如下： sp_rename objname = 對(duì)象名, newname = 新對(duì)象名 參數(shù)說明：objname = 對(duì)象名：指定存儲(chǔ)過程的當(dāng)前名稱。newname = 新對(duì)象名：指定存儲(chǔ)過程的新名稱。 44例7-8將存儲(chǔ)過程add_

24、proc重命名為add_proc2，則可以使用以下命令： EXEC sp_rename add_proc, add_proc2運(yùn)行結(jié)果為： 注意: 更改對(duì)象名的任一部分都可能破壞腳本和存儲(chǔ)過程。 object 已重命名為 add_proc2。457.1.7 刪除存儲(chǔ)過程 使用企業(yè)管理器 使用DROP PROCEDURE 語句46使用企業(yè)管理器 在企業(yè)管理器中，右擊要?jiǎng)h除的存儲(chǔ)過程，選擇“刪除”命令，并確認(rèn)刪除。47使用DROP PROCEDURE語句 DROP PROCEDURE 存儲(chǔ)過程名 ,.n 48例7-9刪除存儲(chǔ)過程add_proc，可以使用以下命令： DROP PROCEDURE a

25、dd_proc49例7-10 刪除一組存儲(chǔ)過程設(shè)某“職工”數(shù)據(jù)庫中有一個(gè)“職工工資”表，該表包含有“基本工資”、“獎(jiǎng)金”和“實(shí)發(fā)工資”等列。創(chuàng)建一組存儲(chǔ)過程，求“職工工資”表的平均基本工資、平均獎(jiǎng)金和平均實(shí)發(fā)工資。 USE 職工 GO CREATE PROC AveSalary;1 AS SELECT AVG(基本工資) FROM 職工工資 GO CREATE PROC AveSalary;2 AS SELECT AVG(獎(jiǎng)金) FROM 職工工資 GO CREATE PROC AveSalary;3 AS SELECT AVG(實(shí)發(fā)工資) FROM 職工工資 GO50例7-10 刪除一組存儲(chǔ)

26、過程使用以下語句執(zhí)行以上存儲(chǔ)過程。 EXEC AveSalary;1 EXEC AveSalary;2 EXEC AveSalary;3要?jiǎng)h除以上創(chuàng)建的三個(gè)存儲(chǔ)過程，可以使用語句： DROP PROCEDURE AveSalary注意，不能寫成： DROP PROCEDURE AveSalary;1 DROP PROCEDURE AveSalary;2 DROP PROCEDURE AveSalary;3517.2 觸發(fā)器觸發(fā)器概述 inserted和deleted表 事務(wù)的概念及應(yīng)用 創(chuàng)建觸發(fā)器 修改和重命名觸發(fā)器 刪除觸發(fā)器 527.2.1 觸發(fā)器的基本概念 觸發(fā)器與普通存儲(chǔ)過程的區(qū)別：觸

27、發(fā)器的執(zhí)行是由事件觸發(fā)的，而普通存儲(chǔ)過程是由命令調(diào)用執(zhí)行的。使用觸發(fā)器有助于強(qiáng)制保持?jǐn)?shù)據(jù)庫的數(shù)據(jù)完整性。例如，在觸發(fā)器中可以完成如下功能：不允許刪除或更新特定的記錄。不允許插入不符合邏輯關(guān)系的記錄。在刪除一條記錄的同時(shí)刪除其他表中與其相關(guān)的記錄。在修改一條記錄的同時(shí)修改其他表中與其相關(guān)的記錄。53SQL Server 2000提供了兩種觸發(fā)器 INSTEAD OF觸發(fā)器：INSTEAD OF觸發(fā)器在指定的操作（INSERT、UPDATE或DELETE語句）之前被執(zhí)行，它的功能是不執(zhí)行指定的操作，而是執(zhí)行INSTEAD OF觸發(fā)器中定義的操作。可以在表和視圖上定義INSTEAD OF觸發(fā)器。AF

28、TER觸發(fā)器。在執(zhí)行了INSERT、UPDATE或DELETE語句操作之后執(zhí)行AFTER觸發(fā)器。AFTER觸發(fā)器只能在表上指定。 54觸發(fā)器的主要優(yōu)點(diǎn) 觸發(fā)器是自動(dòng)執(zhí)行的，不需要管理員手動(dòng)維護(hù)數(shù)據(jù)庫的數(shù)據(jù)完整性。觸發(fā)器可以對(duì)數(shù)據(jù)庫中的相關(guān)表進(jìn)行級(jí)聯(lián)更改。例如，可以在表“院系”中定義觸發(fā)器，當(dāng)用戶刪除表“院系”中的記錄時(shí)，觸發(fā)器將刪除表“學(xué)生”中對(duì)應(yīng)院系的記錄。觸發(fā)器可以限制向表中插入無效的數(shù)據(jù)，這一點(diǎn)與CHECK約束的功能相似。但在CHECK約束中不能使用到其他表中的字段，而在觸發(fā)器中則沒有此限制。例如，可以在表“學(xué)生”中定義觸發(fā)器，限制插入的記錄其“院系編號(hào)”字段值必須在表“院系”中存在對(duì)應(yīng)

29、的記錄。 557.2.2 inserted和deleted表 deleted表用于存儲(chǔ)DELETE和UPDATE語句所影響的行的復(fù)本。在執(zhí)行DELETE或UPDATE語句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。deleted表和觸發(fā)器表通常沒有相同的行。inserted表用于存儲(chǔ)INSERT和UPDATE語句所影響的行的副本。在一個(gè)插入或更新事務(wù)處理中，新建行被同時(shí)添加到inserted表和觸發(fā)器表中。inserted表中的行是觸發(fā)器表中新行的副本。56inserted和deleted表的變化在設(shè)置觸發(fā)器條件時(shí)，應(yīng)當(dāng)為引發(fā)觸發(fā)器的操作恰當(dāng)使用inserted和deleted表。通常

30、在插入數(shù)據(jù)時(shí)，可以從insert表中讀取新插入的值，此時(shí)delete表不會(huì)發(fā)生變化。在刪除數(shù)據(jù)時(shí)，可以從delete表中讀取已經(jīng)刪除或修改的值，而insert表不會(huì)發(fā)生變化。在更新數(shù)據(jù)時(shí)，insert表和delete表都發(fā)生變化。可以從delete表中讀取原有的值，從insert表中讀取修改后的值。 577.2.3 事務(wù)的概念及應(yīng)用 在觸發(fā)器中經(jīng)常會(huì)取消用戶先前進(jìn)行的操作，例如不允許插入不符合條件的數(shù)據(jù)。SQL Server提供了一種叫做事務(wù)的機(jī)制，它可以保證指定的對(duì)數(shù)據(jù)庫的一系列操作作為一個(gè)整體被執(zhí)行，在最終提交操作之間，用戶可以隨時(shí)取消前面的操作，將數(shù)據(jù)庫還原到?jīng)]有執(zhí)行操作前的狀態(tài)。 58

31、事務(wù)的屬性原子性: 事務(wù)必須是原子工作單元。它對(duì)數(shù)據(jù)庫所進(jìn)行的操作，要么全都執(zhí)行，要么全都不執(zhí)行。一致性: 事務(wù)在完成時(shí)，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。在相關(guān)數(shù)據(jù)庫中，所有規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持所有數(shù)據(jù)的完整性。事務(wù)結(jié)束時(shí)，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。隔離性: 由并發(fā)事務(wù)所作的修改必須與任何其他并發(fā)事務(wù)所作的修改隔離。事務(wù)查看數(shù)據(jù)時(shí)數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，要么是另一事務(wù)修改它之后的狀態(tài)，事務(wù)不會(huì)查看中間狀態(tài)的數(shù)據(jù)。持久性: 事務(wù)完成之后，它對(duì)于系統(tǒng)的影響是永久性的。該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。59日常生活中事務(wù)的例子用戶在網(wǎng)上商場選擇商品

32、，然后向商家提交購物請求，并進(jìn)行網(wǎng)上支付。此時(shí)，交易狀態(tài)為提交。商家獲得用戶提交的購物請求，在確認(rèn)收到網(wǎng)上支付的金額后向用戶郵寄商品。此時(shí)，交易狀態(tài)為已處理。用戶在收到商品并確認(rèn)商品無質(zhì)量后，在網(wǎng)上商場確認(rèn)已收到商品。此時(shí)，交易狀態(tài)為成功。定義一個(gè)事務(wù)需要3種操作，即啟動(dòng)事務(wù)、回滾事務(wù)和提交事務(wù)。啟動(dòng)事務(wù)相當(dāng)于用戶提交購物請求之前的狀態(tài)，回滾事務(wù)相當(dāng)于用戶取消當(dāng)前交易，提交事務(wù)相當(dāng)于用戶確認(rèn)交易成功。60啟動(dòng)事務(wù) SQL Server中包括2種啟動(dòng)事務(wù)的模式，即顯式事務(wù)和隱式事務(wù)。顯式事務(wù)：通過BEGIN TRANSACTION語句顯式啟動(dòng)事務(wù)。BEGIN TRANSACTION語句的基本語法

33、如下： BEGIN TRANSACTION 事務(wù)名 在顯式事務(wù)中，事務(wù)名是可選項(xiàng)。事務(wù)名必須符合標(biāo)識(shí)符命名規(guī)則。隱式事務(wù)：當(dāng)用戶沒有顯式地定義事務(wù)時(shí)，SQL Server按其默認(rèn)的規(guī)定自動(dòng)劃分事務(wù)。61回滾事務(wù)如果服務(wù)器錯(cuò)誤使事務(wù)無法成功完成，SQL Server將自動(dòng)回滾該事務(wù)，并釋放該事務(wù)占用的所有資源。如果客戶端與SQL Server的網(wǎng)絡(luò)連接中斷了，那么當(dāng)網(wǎng)絡(luò)告知SQL Server該中斷時(shí)，將回滾該連接的所有未完成事務(wù)。如果用戶需要手動(dòng)回滾事務(wù)，可以使用語句：ROLLBACK TRANSACTION 事務(wù)名 其中，“事務(wù)名”是給 BEGIN TRANSACTION 上的事務(wù)指派的名稱

34、。62提交事務(wù)COMMIT TRANSACTION語句可以標(biāo)志一個(gè)成功的隱性事務(wù)或顯式事務(wù)的結(jié)束，它的基本語法如下： COMMIT TRANSACTION 事務(wù)名注意，不能在發(fā)出COMMIT TRANSACTION語句之后回滾事務(wù)，因?yàn)閿?shù)據(jù)修改已經(jīng)成為數(shù)據(jù)庫的永久部分。63例7-11定義一個(gè)事務(wù)，向“學(xué)生”表中插入兩條記錄。其中，第1條INSERT語句是正確的，而第2條INSERT語句是錯(cuò)誤的。執(zhí)行此事務(wù)語句后，查看“學(xué)生”表中的數(shù)據(jù)，確認(rèn)第1條語句沒有被執(zhí)行。 USE 學(xué)生管理 GO BEGIN TRANSACTION INSERT INTO 學(xué)生 VALUES(test1, 1, 一班,

35、500, 2) INSERT INTO 學(xué)生 VALUES(10, test2, 1, 二班, 500, 2) COMMIT TRANSACTION GO64例7-11第2條插入語句因?yàn)橹付藰?biāo)識(shí)列的值，所以產(chǎn)生錯(cuò)誤。執(zhí)行此事務(wù)語句的結(jié)果如下： 服務(wù)器: 消息 8101，級(jí)別 16，狀態(tài) 1，行 1在查詢分析器中執(zhí)行如下語句： SELECT * FROM 學(xué)生 可以看到，因?yàn)榈?條INSERT語句出現(xiàn)錯(cuò)誤，導(dǎo)致事務(wù)回滾，所以第1條語句所插入的記錄也沒有出現(xiàn)在結(jié)果集中。65例7-12定義一個(gè)事務(wù)，向“學(xué)生”表中插入一條記錄，然后將事務(wù)回滾。執(zhí)行此事務(wù)語句后，查看“學(xué)生”表中的數(shù)據(jù)，確認(rèn)INSER

36、T語句插入的數(shù)據(jù)不在結(jié)果集中。 USE 學(xué)生管理 GO BEGIN TRANSACTION INSERT INTO 學(xué)生 VALUES(test1, 1, 一班, 500, 2) ROLLBACK TRANSACTION GO66例7-12執(zhí)行此事務(wù)語句的結(jié)果如下：（所影響的行數(shù)為 1 行） 證明INSERT語句已經(jīng)被執(zhí)行。在查詢分析器中執(zhí)行如下語句： SELECT * FROM 學(xué)生 可以看到，因?yàn)閳?zhí)行了ROLLBACK TRANSACTION語句，導(dǎo)致事務(wù)回滾，所以INSERT語句所插入的記錄也沒有出現(xiàn)在結(jié)果集中。677.2.4 創(chuàng)建觸發(fā)器在企業(yè)管理器中手動(dòng)地創(chuàng)建 使用CREATE TRI

37、GGER語句創(chuàng)建 68在創(chuàng)建觸發(fā)器之前，應(yīng)該考慮以下問題 CREATE TRIGGER語句必須是批處理中的第一條語句。SQL Server將該批處理中隨后的其他所有語句解釋為CREATE TRIGGER語句定義的一部分。創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)移給其他用戶。 觸發(fā)器為數(shù)據(jù)庫對(duì)象，其名稱必須遵循標(biāo)識(shí)符的命名規(guī)則。雖然觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對(duì)象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。69在創(chuàng)建觸發(fā)器之前，應(yīng)該考慮以下問題 雖然不能在臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時(shí)表，不應(yīng)引用系統(tǒng)表。如果已經(jīng)給一個(gè)表的外鍵定義了級(jí)聯(lián)刪除或級(jí)聯(lián)更新，則不能在該表上定義

38、INSTEAD OF DELETE或INSTEAD OF DELETE UPDATE 觸發(fā)器。雖然TRUNCATE TABLE語句（刪除表中的所有行）類似于沒有WHERE子句的DELETE語句，但它并不會(huì)引發(fā)DELETE觸發(fā)器。WRITETEXT語句（更新text、ntext或image類型的列）不會(huì)引發(fā)INSERT或UPDATE觸發(fā)器。70使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器CREATE TRIGGER 觸發(fā)器名ON 表名 | 視圖名 WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS IF UP

39、DATE ( 列 ) AND | OR UPDATE ( 列 ) .n SQL語句 .n 71參數(shù)說明觸發(fā)器名：必須符合標(biāo)識(shí)符的命名規(guī)則，并且在數(shù)據(jù)庫中必須唯一?？梢赃x擇是否指定觸發(fā)器所有者的名稱。 表名 | 視圖名：是在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱為觸發(fā)器表或觸發(fā)器視圖。可以選擇是否指定表或視圖的所有者名稱。WITH ENCRYPTION：加密syscomments表中包含CREATE TRIGGER語句文本的條目。AFTER：指定觸發(fā)器只有在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級(jí)聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。如果僅指定FOR關(guān)鍵字，則AF

40、TER是默認(rèn)設(shè)置。不能在視圖上定義AFTER觸發(fā)器。72參數(shù)說明INSTEAD OF：指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)SQL語句，從而替代觸發(fā)SQL語句的操作。在表或視圖上，每個(gè)INSERT、UPDATE或DELETE語句最多可以定義一個(gè)INSTEAD OF觸發(fā)器。然而，可以在每個(gè)具有INSTEAD OF觸發(fā)器的視圖上定義視圖。 DELETE , INSERT , UPDATE ：是指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語句時(shí)將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個(gè)選項(xiàng)。在觸發(fā)器定義中允許使用以任意順序組合的這些關(guān)鍵字。如果指定的選項(xiàng)多于一個(gè)，需用逗號(hào)分隔這些選項(xiàng)。73參數(shù)說明AS：引入觸發(fā)器要執(zhí)行的操作

41、。IF UPDATE (列)：用于判斷是否在指定的列上進(jìn)行了INSERT或UPDATE操作（不能用于DELETE操作），可以指定多列。因?yàn)樵贠N子句中指定了表名，所以在IF UPDATE子句中的列名前不要包含表名。對(duì)于INSERT操作，IF UPDATE將返回TRUE值，因?yàn)檫@些列插入了數(shù)據(jù)。SQL語句：當(dāng)嘗試 DELETE、INSERT或UPDATE操作時(shí)要執(zhí)行的Transact-SQL語句。74例7-13 INSERT觸發(fā)器 在“學(xué)生”表中創(chuàng)建一個(gè)INSERT觸發(fā)器，如果插入記錄的院系編號(hào)值在“院系”表中存在下級(jí)單位（例如，計(jì)算機(jī)學(xué)院包括軟件系），則不執(zhí)行插入操作，并提示用戶。 CREAT

42、E TRIGGER insert_student ON 學(xué)生 FOR INSERT AS DECLARE orgid int DECLARE orgname varchar(100) SELECT orgid = 所屬院系 FROM inserted 75例7-13 INSERT觸發(fā)器 - 判斷插入的院系記錄是否存在上級(jí)記錄 SELECT orgname=院系名稱 FROM 院系 WHERE 上級(jí)編號(hào)=orgid IF orgname -如果存在上級(jí)院系 BEGIN PRINT 指定院系存在下級(jí)單位，請選擇具體單位！ ROLLBACK TRANSACTION END GO76例7-13 INS

43、ERT觸發(fā)器 為了驗(yàn)證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語句： INSERT INTO 學(xué)生 VALUES(小朱, 0, 二班, 500, 1) 因?yàn)樵合稻幪?hào)為1的記錄存在下級(jí)單位，所以返回結(jié)果如下： 指定院系存在下級(jí)單位，請選擇具體單位！77例7-14 UPDATE觸發(fā)器在“學(xué)生”表中創(chuàng)建一個(gè)UPDATE觸發(fā)器，如果修改記錄的院系編號(hào)值在“院系”表中存在下級(jí)單位（例如，計(jì)算機(jī)學(xué)院包括軟件系），則不執(zhí)行修改操作，并提示用戶。 CREATE TRIGGER update_student ON 學(xué)生 FOR UPDATE AS - 從表inserted中獲取更新后記錄的院系編號(hào) DECLA

44、RE orgid int DECLARE orgname varchar(100) SELECT orgid = 所屬院系 FROM inserted78例7-14 UPDATE觸發(fā)器 - 判斷修改的院系記錄是否存在上級(jí)記錄 SELECT orgname=院系名稱 FROM 院系 WHERE 上級(jí)編號(hào)=orgid IF orgname BEGIN PRINT 指定院系存在下級(jí)單位，請選擇具體單位！ ROLLBACK TRANSACTION - 回滾操作 END GO79例7-14 UPDATE觸發(fā)器 為了驗(yàn)證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語句： UPDATE 學(xué)生 SET 所屬院系

45、=1 WHERE 所屬院系=2 因?yàn)樵合稻幪?hào)為1的記錄存在下級(jí)單位，所以返回結(jié)果如下： 指定院系存在下級(jí)單位，請選擇具體單位！80例7-15 DELETE觸發(fā)器在“院系”表中創(chuàng)建一個(gè)DELETE觸發(fā)器，如果刪除記錄的院系編號(hào)值在“院系”表中存在下級(jí)單位（例如，計(jì)算機(jī)學(xué)院包括軟件系），則不執(zhí)行刪除操作，并提示用戶。 CREATE TRIGGER delete_org ON 院系 FOR DELETE AS - 從表deleted中獲取刪除記錄的院系編號(hào) DECLARE orgid int DECLARE orgname varchar(100) SELECT orgid = 記錄編號(hào) FROM

46、deleted81例7-15 DELETE觸發(fā)器- 判斷刪除的院系記錄是否存在下級(jí)記錄SELECT orgname=院系名稱 FROM 院系 WHERE 上級(jí)編號(hào)=orgidIF orgname BEGIN PRINT 指定院系存在下級(jí)單位，不允許被刪除！ ROLLBACK TRANSACTION - 回滾操作 ENDGO 82例7-15 DELETE觸發(fā)器 為了驗(yàn)證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語句： DELETE FROM 院系 WHERE 記錄編號(hào)=1 因?yàn)樵合稻幪?hào)為1的記錄存在下級(jí)單位，所以返回結(jié)果如下： 指定院系存在下級(jí)單位，不允許被刪除！83例7-16 INSTEAD

47、OF觸發(fā)器使用INSTEAD OF觸發(fā)器實(shí)現(xiàn)例7-15的功能。 CREATE TRIGGER delete_org1 ON 院系 INSTEAD OF DELETE AS - 從表deleted中獲取刪除記錄的院系編號(hào) DECLARE orgid int DECLARE orgname varchar(100) SELECT orgid = 記錄編號(hào) FROM deleted84例7-16 INSTEAD OF觸發(fā)器- 判斷修改的院系記錄是否存在下級(jí)記錄SELECT orgname=院系名稱 FROM 院系 WHERE 上級(jí)編號(hào)=orgidIF orgname PRINT 指定院系存在下級(jí)單位

48、，不允許被刪除！ ELSE DELETE FROM 院系 WHERE 記錄編號(hào)=orgid GO85例7-16 INSTEAD OF觸發(fā)器 因?yàn)镮NSTEAD OF觸發(fā)器使用觸發(fā)器中定義的代碼取代原操作，所以不需要進(jìn)行回滾操作。當(dāng)然，如果原操作符合規(guī)定的條件，還需要在觸發(fā)器中重新執(zhí)行此操作。為了驗(yàn)證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語句： DELETE FROM 院系 WHERE 記錄編號(hào)=1 因?yàn)樵合稻幪?hào)為1的記錄存在下級(jí)單位，所以返回結(jié)果如下： 指定院系存在下級(jí)單位，不允許被刪除！ （所影響的行數(shù)為 1 行）86例7-17 對(duì)特定列進(jìn)行測試使用IF UPDATE(列名)子句實(shí)現(xiàn)例7-14的功能。CREATE TRIGGER update_student1