存儲過程與觸發(fā)器ppt

1、存儲過程與觸發(fā)器ppt1第1頁，共103頁，2022年，5月20日，16點9分，星期二本章要點存儲過程概述 創(chuàng)建存儲過程 執(zhí)行存儲過程 存儲過程的參數(shù) 存儲過程的返回值 查看和修改存儲過程 刪除存儲過程 觸發(fā)器概述 inserted和deleted表 事務(wù)的概念及應(yīng)用 創(chuàng)建觸發(fā)器 修改和重命名觸發(fā)器 刪除觸發(fā)器 2第2頁，共103頁，2022年，5月20日，16點9分，星期二7.1 存儲過程在創(chuàng)建SQL Server數(shù)據(jù)庫應(yīng)用程序時，Transact-SQL語言是應(yīng)用程序和SQL Server數(shù)據(jù)庫之間的主要編程接口?？捎萌缦聝煞N方法存儲和執(zhí)行Transact-SQL語句。(1) 將Trans

2、act-SQL程序保存在本地，創(chuàng)建向SQL Server發(fā)送命令并處理結(jié)果的應(yīng)用程序。(2) 可以將Transact-SQL程序保存在SQL Server中，即存儲過程，在本地創(chuàng)建執(zhí)行存儲過程及處理結(jié)果的應(yīng)用程序。任何一組Transact-SQL語句構(gòu)成的代碼塊，都可以作為存儲過程保存起來。它在服務(wù)器端對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行處理，并將結(jié)果返回到客戶端。這樣就避免了從客戶端多次連接并訪問數(shù)據(jù)庫的操作，減少了網(wǎng)絡(luò)上的傳輸量，同時也提高了客戶端的工作效率（因為這些操作都是在服務(wù)器端完成的）。3第3頁，共103頁，2022年，5月20日，16點9分，星期二7.1.1 存儲過程概述 存儲過程是集中存儲在S

3、QL Server中的SQL語句和流程控制語句的預(yù)編譯集合，用以實現(xiàn)某種任務(wù)（如查詢）。這些語句在一個名稱下存儲并作為一個單元進(jìn)行處理。4第4頁，共103頁，2022年，5月20日，16點9分，星期二使用存儲過程的優(yōu)勢 可以減少客戶端代碼的重復(fù)。只需創(chuàng)建存儲過程一次并將其存儲在數(shù)據(jù)庫中，以后即可在客戶端程序中多次調(diào)用該存儲過程。存儲過程可由在數(shù)據(jù)庫編程方面有專長的人員創(chuàng)建，并可獨立于程序源代碼而單獨修改。 允許更快地執(zhí)行。如果某操作需要大量的Transact-SQL代碼或需要重復(fù)執(zhí)行，使用存儲過程將比在客戶端執(zhí)行Transact-SQL批代碼的執(zhí)行速度要快。存儲過程在服務(wù)器端經(jīng)過預(yù)編譯，生成查

4、詢計劃，可以直接執(zhí)行，而在客戶端每次運行Transact-SQL語句時，都要從客戶端重復(fù)發(fā)送，并且在SQL Server每次執(zhí)行這些語句時，都要對其進(jìn)行編譯和優(yōu)化。因此，存儲過程執(zhí)行速度更快。5第5頁，共103頁，2022年，5月20日，16點9分，星期二使用存儲過程的優(yōu)勢 減少網(wǎng)絡(luò)流量。例如，一個需要數(shù)百行Transact-SQL代碼的操作由一條執(zhí)行過程代碼的單獨語句就可實現(xiàn)，而不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼?？梢院喕瘮?shù)據(jù)庫管理。例如，要修改某種查詢，如果查詢重復(fù)存放在客戶機上，則要在所有客戶機上修改查詢，而使用存儲過程可以集中修改?？勺鳛榘踩珯C制使用。例如，即使對于沒有直接執(zhí)行存儲過程中語句

5、的權(quán)限的用戶，也可授予他們執(zhí)行該存儲過程的權(quán)限。6第6頁，共103頁，2022年，5月20日，16點9分，星期二存儲過程的分類 系統(tǒng)存儲過程: SQL Server內(nèi)置的存儲過程，存儲在master庫中，主要用途是執(zhí)行SQL Server的某些管理功能、顯示有關(guān)數(shù)據(jù)庫和用戶的信息。系統(tǒng)存儲過程名以SP_開頭，可以在任何數(shù)據(jù)庫中執(zhí)行系統(tǒng)存儲過程。用戶存儲過程：用戶自行創(chuàng)建并存儲在用戶數(shù)據(jù)庫中的存儲過程。臨時存儲過程：分為局部臨時存儲過程和全局臨時存儲過程。7第7頁，共103頁，2022年，5月20日，16點9分，星期二存儲過程的分類 局部臨時存儲過程名稱以#開頭，存放在tempdb數(shù)據(jù)庫中，只由

6、創(chuàng)建并連接的用戶使用，當(dāng)該用戶斷開連接時將自動刪除局部臨時存儲過程。全局臨時存儲過程名稱以#開頭，存放在tempdb數(shù)據(jù)庫中，允許所有連接的用戶使用，在所有用戶斷開連接時自動被刪除。遠(yuǎn)程存儲過程：位于遠(yuǎn)程服務(wù)器上的存儲過程。擴展存儲過程：利用外部語言（如C）編寫的存儲過程，以彌補SQL Server的不足之處，擴展新的功能8第8頁，共103頁，2022年，5月20日，16點9分，星期二7.1.2 創(chuàng)建存儲過程 使用CREATE PROCEDURE語句 使用企業(yè)管理器中的菜單命令 9第9頁，共103頁，2022年，5月20日，16點9分，星期二CREATE PROCEDURE語句 CREATE

7、PROC EDURE 存儲過程名;編號 參數(shù) 數(shù)據(jù)類型 VARYING = 默認(rèn)值 OUTPUT ,.nWITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTIONAS SQL語句 .n 10第10頁，共103頁，2022年，5月20日，16點9分，星期二參數(shù)說明 存儲過程名必須符合標(biāo)識符的命名規(guī)則，且對于數(shù)據(jù)庫及其所有者必須是惟一的。要創(chuàng)建局部臨時存儲過程，可以在存儲過程名前面加一個#號，要創(chuàng)建全局臨時過程，可以在存儲過程名前面加兩個#號。完整的名稱（包括 # 或 #）不能超過128個字符。編號：可選整數(shù)，用來對同名的存儲過程分組，以便用一條DROP

8、PROCEDURE語句即可將同組的存儲過程一起刪除。11第11頁，共103頁，2022年，5月20日，16點9分，星期二參數(shù)說明 參數(shù)：過程中的參數(shù)。在CREATE PROCEDURE語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行存儲過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。參數(shù)名稱前需要使用符號。參數(shù)名稱必須符合標(biāo)識符的命名規(guī)則。每個過程的參數(shù)僅用于該過程本身。在其他過程中可以使用相同的參數(shù)名稱。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其他數(shù)據(jù)庫對象的名稱。12第12頁，共103頁，2022年，5月20日，16點9分，星期二參數(shù)說明 數(shù)據(jù)類型：參數(shù)的數(shù)據(jù)類型。所

9、有數(shù)據(jù)類型（包括text、ntext和image）均可以用作存儲過程的參數(shù)。不過，cursor數(shù)據(jù)類型只能用于OUTPUT參數(shù)。如果指定的數(shù)據(jù)類型為cursor，也必須同時指定VARYING和OUTPUT關(guān)鍵字。VARYING：指定作為輸出參數(shù)支持的結(jié)果集（由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。13第13頁，共103頁，2022年，5月20日，16點9分，星期二參數(shù)說明 默認(rèn)值：參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行存儲過程。默認(rèn)值必須是常量或NULL。如果要在存儲過程中對該參數(shù)使用LIKE關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、和）。OUTPUT：表明

10、參數(shù)是返回參數(shù)。該選項的值可以返回給EXECUTE。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。text、ntext和image參數(shù)可用作OUTPUT參數(shù)。使用OUTPUT關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。14第14頁，共103頁，2022年，5月20日，16點9分，星期二參數(shù)說明 RECOMPILE：表明不保存該存儲過程的執(zhí)行計劃，該存儲過程將在運行時重新編譯。ENCRYPTION：指定SQL Server對syscomments表中包含本CREATE PROCEDURE語句文本的條目進(jìn)行加密AS：用于指定該存儲過程要執(zhí)行的操作。SQL語句：存儲過程中要包含的Transact-SQL語句。1

11、5第15頁，共103頁，2022年，5月20日，16點9分，星期二存儲過程定義1.無參數(shù)存儲過程2.有參數(shù)存儲過程（輸入?yún)?shù)、輸出參數(shù)output）16第16頁，共103頁，2022年，5月20日，16點9分，星期二例題1：建立一個查詢存儲過程，實現(xiàn)查詢成績表中的所有及格成績。（無參數(shù)存儲過程）create proc seleproc as select * from 成績表where 成績=60執(zhí)行存儲過程： exec seleproc結(jié)果如圖：17第17頁，共103頁，2022年，5月20日，16點9分，星期二例2：創(chuàng)建一個向成績表添加記錄的存儲過程。（有參數(shù)存儲過程）create pro

12、c insertproc sno char(10),cno char(3),grade floatas insert into 成績表 values(sno,cno,grade)執(zhí)行存儲過程： exec insertproc 0009,03,9818第18頁，共103頁，2022年，5月20日，16點9分，星期二例3：創(chuàng)建一個存儲過程，要求根據(jù)輸入的課程號統(tǒng)計該課程平均分。create proc cnoavgscore cno char(2),avgscore float outputas declare cnon char(2) set cnon= select cnon=課程號from 成

13、績表 where 課程號=cno if cnon begin select avgscore=avg(成績) from 成績表 where 課程號=cno end else print 此課程號不存在 go19第19頁，共103頁，2022年，5月20日，16點9分，星期二運行該存儲過程：declare i floatexec cnoavgscore 02, i outputprint i20第20頁，共103頁，2022年，5月20日，16點9分，星期二說明：With encryption 子句的作用是將存儲過程的原代碼加密；With recompile子句的作用是對該存儲過程重編譯。21第

14、21頁，共103頁，2022年，5月20日，16點9分，星期二例4 創(chuàng)建存儲過程“增加成績”，將表“成績表”中所有學(xué)生的成績增加10%。 USE 學(xué)生管理 GO CREATE PROCEDURE 增加成績 AS UPDATE 成績表 SET 成績 = 成績 *1.1 GO執(zhí)行存儲過程：exec 增加成績 22第22頁，共103頁，2022年，5月20日，16點9分，星期二例5 （細(xì)節(jié)考慮）在創(chuàng)建一個存儲過程時，如果已經(jīng)存在同名的存儲過程，則不允許創(chuàng)建新的存儲過程。可以將以上代碼改寫為： USE 學(xué)生管理 -如果存在名稱為“增加成績”的存儲過程，則將其刪除 IF EXISTS (SELECT n

15、ame FROM sysobjects WHERE name = 增加成績 AND type = P) DROP PROCEDURE 增加成績 GO CREATE PROCEDURE 增加成績 AS UPDATE 成績表 SET 成績 = 成績 *1.1 GO23第23頁，共103頁，2022年，5月20日，16點9分，星期二使用企業(yè)管理器創(chuàng)建存儲過程在企業(yè)管理器中，展開要創(chuàng)建存儲過程的數(shù)據(jù)庫,用鼠標(biāo)右擊“存儲過程”，在彈出菜單中單擊“新建存儲過程”命令，打開新建存儲過程對話框，如下圖：24第24頁，共103頁，2022年，5月20日，16點9分，星期二使用企業(yè)管理器創(chuàng)建存儲過程 在該對話框的

16、文本框中有以下的默認(rèn)語句： CREATE PROCEDURE OWNER.PROCEDURE NAME AS 這是CREATE PROCEDURE語句的開始部分，存儲過程的具體文本需要用戶自己輸入。例如，將以上默認(rèn)語句修改成： CREATE PROCEDURE 增加成績 AS UPDATE 成績表 SET 成績 = 成績 *1.1 GO25第25頁，共103頁，2022年，5月20日，16點9分，星期二7.1.3 執(zhí)行存儲過程 EXEC UTE 返回狀態(tài) = 存儲過程名 | 存儲過程名變量 參數(shù)名稱 = 值 | 變量 OUTPUT | DEFAULT ,.n WITH RECOMPILE 26

17、第26頁，共103頁，2022年，5月20日，16點9分，星期二參數(shù)說明返回狀態(tài)：是一個可選的整型變量，保存存儲過程的返回狀態(tài)。這個變量在用于EXECUTE語句前，必須在批處理、存儲過程或函數(shù)中聲明過。存儲過程名：要調(diào)用的存儲過程的名稱。存儲過程名變量：局部變量名，代表存儲過程的名稱。參數(shù)名稱：存儲過程的參數(shù)，在CREATE PROCEDURE語句中定義。參數(shù)名稱前必須加上符號。在使用格式“參數(shù)= 值”時，參數(shù)名稱和常量不一定按照CREATE PROCEDURE語句中定義的順序出現(xiàn)。但是，如果有一個參數(shù)使用“參數(shù)= 值”格式，則其他所有參數(shù)都必須使用這種格式。27第27頁，共103頁，2022

18、年，5月20日，16點9分，星期二參數(shù)說明值：過程中參數(shù)的值。如果沒有指定參數(shù)名稱，參數(shù)值必須以CREATE PROCEDURE語句中定義的順序給出。如果在CREATE PROCEDURE語句中定義了默認(rèn)值，用戶執(zhí)行該存儲過程時可以不必指定對應(yīng)的參數(shù)。如果該存儲過程使用了帶LIKE關(guān)鍵字的參數(shù)名稱，則默認(rèn)值必須是常量，并且可以包含 %、_、 及 通配符。默認(rèn)值也可以為NULL。通常，定義存儲過程時會指定當(dāng)參數(shù)值為NULL時應(yīng)該執(zhí)行的操作。變量：是用來保存參數(shù)或者返回參數(shù)的變量。28第28頁，共103頁，2022年，5月20日，16點9分，星期二參數(shù)說明OUTPUT：指定存儲過程必須返回一個參數(shù)

19、。該存儲過程的匹配參數(shù)也必須由關(guān)鍵字OUTPUT創(chuàng)建。使用游標(biāo)變量作參數(shù)時使用該關(guān)鍵字。DEFAULT：根據(jù)存儲過程的定義，提供參數(shù)的默認(rèn)值。當(dāng)過程需要的參數(shù)值沒有事先定義好的默認(rèn)值，或缺少參數(shù)，或指定了DEFAULT關(guān)鍵字時，就會出錯。WITH RECOMPILE：強制編譯新的計劃。如果所提供的參數(shù)為非典型參數(shù)或者數(shù)據(jù)有很大的改變，使用該選項。在以后的程序執(zhí)行中使用更改過的計劃。該選項不能用于擴展存儲過程。建議盡量少使用該選項，因為它消耗較多系統(tǒng)資源。29第29頁，共103頁，2022年，5月20日，16點9分，星期二7.1.4 存儲過程的參數(shù) 為了提高存儲過程的靈活性，SQL Server

20、 2000支持在存儲過程中使用參數(shù)。存儲過程的參數(shù)分為輸入?yún)?shù)和輸出參數(shù)兩種類型，輸入?yún)?shù)用于向存儲過程中帶入數(shù)據(jù)，而輸出參數(shù)則能將存儲過程中的數(shù)據(jù)返回到調(diào)用程序。在定義存儲過程時,可以同時指定參數(shù),格式如下： 參數(shù)名 數(shù)據(jù)類型 =默認(rèn)值 OUTPUT, . n如果參數(shù)后面使用OUTPUT關(guān)鍵字，則表明它是輸出參數(shù)。30第30頁，共103頁，2022年，5月20日，16點9分，星期二例6創(chuàng)建存儲過程add_proc，用于計算兩個參數(shù)之和并將其輸出。 CREATE PROCEDURE add_proc num1 INT = 0, num2 INT = 0 AS DECLARE num3 INT

21、SET num3 = num1 + num2 PRINT num3該存儲過程定義了兩個參數(shù)num1和num2，它們都是輸入?yún)?shù),參數(shù)類型為INT,默認(rèn)值為0。31第31頁，共103頁，2022年，5月20日，16點9分，星期二執(zhí)行例6不帶參數(shù)執(zhí)行： EXEC add_proc 運行結(jié)果為： 0帶參數(shù)執(zhí)行： EXEC add_proc 13, 25 運行結(jié)果為： 38 有時需要將存儲過程中的計算結(jié)果返回到調(diào)用程序中，以便進(jìn)行進(jìn)一步的處理，此時就需要在存儲過程中使用輸出參數(shù)。 32第32頁，共103頁，2022年，5月20日，16點9分，星期二例7創(chuàng)建存儲過程add_proc1，用于計算兩個參數(shù)之

22、和，并使用輸出參數(shù)返回結(jié)果。 CREATE PROCEDURE add_proc1 num1 INT = 0, num2 INT = 0, num3 INT OUTPUT -num3為輸出參數(shù) AS SET num3 = num1 + num233第33頁，共103頁，2022年，5月20日，16點9分，星期二執(zhí)行例7DECLARE num AS INTEXEC add_proc1 12, 23, num OUTPUTPRINT num運行結(jié)果為：3834第34頁，共103頁，2022年，5月20日，16點9分，星期二例題8：編輯一個可自由運算的存儲過程create proc calculat

23、e num1 as float,num2 float,s char(2),num3 float output as set num3= case s when + then num1+num2 when - then num1-num2 when * then num1*num2 when / then num1/num2 end35第35頁，共103頁，2022年，5月20日，16點9分，星期二運行該存儲過程declare num3 floatexec calculate 2,4,/,num3 outputselect num336第36頁，共103頁，2022年，5月20日，16點9分，星

24、期二7.1.6 查看和修改存儲過程 使用企業(yè)管理器查看和修改存儲過程 使用查詢分析器查看和修改存儲過程 使用企業(yè)管理器重命名存儲過程 使用ALTER PROCEDURE語句修改存儲過程使用sp_rename重命名存儲過程37第37頁，共103頁，2022年，5月20日，16點9分，星期二1. 使用企業(yè)管理器查看和修改存儲過程 在企業(yè)管理器中展開指定的數(shù)據(jù)庫，選中“存儲過程”項，可以查看指定數(shù)據(jù)庫中定義的所有存儲過程。38第38頁，共103頁，2022年，5月20日，16點9分，星期二1. 使用企業(yè)管理器查看和修改存儲過程 雙擊指定的存儲過程，可以打開存儲過程屬性對話框，查看存儲過程的定義情況。

25、用戶可以在存儲過程屬性對話框中修改存儲過程的代碼。 39第39頁，共103頁，2022年，5月20日，16點9分，星期二2. 使用查詢分析器查看和修改存儲過程 右鍵單擊要編輯的存儲過程，在彈出菜單中選擇“編輯”，可以在右鍵的窗格中顯示指定存儲過程的代碼。40第40頁，共103頁，2022年，5月20日，16點9分，星期二3. 使用企業(yè)管理器重命名存儲過程 在企業(yè)管理器，展開“數(shù)據(jù)庫”文件夾，選擇存儲過程所在的數(shù)據(jù)庫，單擊“存儲過程”文件夾。在右側(cè)的窗口中，列出了選擇數(shù)據(jù)庫的所有存儲過程。右擊要重命名的存儲過程，選擇“重命名”菜單項，就可以在當(dāng)前位置上修改存儲過程的名字。41第41頁，共103頁

26、，2022年，5月20日，16點9分，星期二4. 使用ALTER PROCEDURE語句修改存儲過程 ALTER PROC EDURE 存儲過程名;編號 參數(shù)名 數(shù)據(jù)類型 VARYING = 默認(rèn)值 OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTIONASSQL語句 .n 各參數(shù)含義與CREATE PROCEDURE語句相同42第42頁，共103頁，2022年，5月20日，16點9分，星期二例9 使用ALTER PROCEDURE語句修改存儲過程“增加成績”，對其進(jìn)行加密處理。 USE 學(xué)生管理 GO ALTER PROCE

27、DURE 增加成績 WITH ENCRYPTION AS UPDATE 成績表 SET 成績 = 成績 + 10指定以上語句后，在企業(yè)管理器中查看“增加成績”存儲過程，將彈出一個對話框，提示用戶存儲過程已經(jīng)加密，不能夠查看。43第43頁，共103頁，2022年，5月20日，16點9分，星期二5. 使用sp_rename重命名存儲過程 系統(tǒng)存儲過程sp_rename的功能是更改當(dāng)前數(shù)據(jù)庫中用戶創(chuàng)建對象（如表、列或用戶定義數(shù)據(jù)類型）的名稱。使用sp_rename重命名存儲過程的語法結(jié)構(gòu)如下： sp_rename objname = 對象名, newname = 新對象名 參數(shù)說明：objname

28、= 對象名：指定存儲過程的當(dāng)前名稱。newname = 新對象名：指定存儲過程的新名稱。 44第44頁，共103頁，2022年，5月20日，16點9分，星期二例10將存儲過程add_proc重命名為add_proc2，則可以使用以下命令： EXEC sp_rename add_proc, add_proc2運行結(jié)果為： 注意: 更改對象名的任一部分都可能破壞腳本和存儲過程。 object 已重命名為 add_proc2。45第45頁，共103頁，2022年，5月20日，16點9分，星期二7.1.7 刪除存儲過程 使用企業(yè)管理器 使用DROP PROCEDURE 語句46第46頁，共103頁，2

29、022年，5月20日，16點9分，星期二使用企業(yè)管理器 在企業(yè)管理器中，右擊要刪除的存儲過程，選擇“刪除”命令，并確認(rèn)刪除。47第47頁，共103頁，2022年，5月20日，16點9分，星期二使用DROP PROCEDURE語句 DROP PROCEDURE 存儲過程名 ,.n 48第48頁，共103頁，2022年，5月20日，16點9分，星期二例11刪除存儲過程add_proc，可以使用以下命令： DROP PROCEDURE add_proc49第49頁，共103頁，2022年，5月20日，16點9分，星期二例12 刪除一組存儲過程設(shè)某“職工”數(shù)據(jù)庫中有一個“職工工資”表，該表包含有“基本

30、工資”、“獎金”和“實發(fā)工資”等列。創(chuàng)建一組存儲過程，求“職工工資”表的平均基本工資、平均獎金和平均實發(fā)工資。 USE 職工 GO CREATE PROC AveSalary;1 AS SELECT AVG(基本工資) FROM 職工工資 GO CREATE PROC AveSalary;2 AS SELECT AVG(獎金) FROM 職工工資 GO CREATE PROC AveSalary;3 AS SELECT AVG(實發(fā)工資) FROM 職工工資 GO50第50頁，共103頁，2022年，5月20日，16點9分，星期二例13刪除一組存儲過程使用以下語句執(zhí)行以上存儲過程。 EXEC

31、AveSalary;1 EXEC AveSalary;2 EXEC AveSalary;3要刪除以上創(chuàng)建的三個存儲過程，可以使用語句： DROP PROCEDURE AveSalary注意，不能寫成： DROP PROCEDURE AveSalary;1 DROP PROCEDURE AveSalary;2 DROP PROCEDURE AveSalary;351第51頁，共103頁，2022年，5月20日，16點9分，星期二7.2 觸發(fā)器觸發(fā)器概述 inserted和deleted表 事務(wù)的概念及應(yīng)用 創(chuàng)建觸發(fā)器 修改和重命名觸發(fā)器 刪除觸發(fā)器 52第52頁，共103頁，2022年，5月20

32、日，16點9分，星期二7.2.1 觸發(fā)器的基本概念 觸發(fā)器是一種特殊的存儲過程，與普通存儲過程的區(qū)別：觸發(fā)器的執(zhí)行是由事件觸發(fā)的，而普通存儲過程是由命令調(diào)用執(zhí)行的。使用觸發(fā)器有助于強制保持?jǐn)?shù)據(jù)庫的數(shù)據(jù)完整性。例如，在觸發(fā)器中可以完成如下功能：不允許刪除或更新特定的記錄。不允許插入不符合邏輯關(guān)系的記錄。在刪除一條記錄的同時刪除其他表中與其相關(guān)的記錄。在修改一條記錄的同時修改其他表中與其相關(guān)的記錄。53第53頁，共103頁，2022年，5月20日，16點9分，星期二SQL Server 2000提供了兩種觸發(fā)器 INSTEAD OF觸發(fā)器(前置觸發(fā)器)：INSTEAD OF觸發(fā)器在指定的操作（IN

33、SERT、UPDATE或DELETE語句）之前被執(zhí)行，它的功能是不執(zhí)行指定的操作，而是執(zhí)行INSTEAD OF觸發(fā)器中定義的操作?？梢栽诒砗鸵晥D上定義INSTEAD OF觸發(fā)器。AFTER/FOR觸發(fā)器（后置觸發(fā)器）。在執(zhí)行了INSERT、UPDATE或DELETE語句操作之后執(zhí)行AFTER觸發(fā)器。AFTER觸發(fā)器只能在表上指定。 54第54頁，共103頁，2022年，5月20日，16點9分，星期二觸發(fā)器的主要優(yōu)點 觸發(fā)器是自動執(zhí)行的，不需要管理員手動維護(hù)數(shù)據(jù)庫的數(shù)據(jù)完整性。觸發(fā)器可以對數(shù)據(jù)庫中的相關(guān)表進(jìn)行級聯(lián)更改。例如，可以在表“院系”中定義觸發(fā)器，當(dāng)用戶刪除表“院系”中的記錄時，觸發(fā)器將刪

34、除表“學(xué)生”中對應(yīng)院系的記錄。觸發(fā)器可以限制向表中插入無效的數(shù)據(jù)，這一點與CHECK約束的功能相似。但在CHECK約束中不能使用到其他表中的字段，而在觸發(fā)器中則沒有此限制。例如，可以在表“學(xué)生”中定義觸發(fā)器，限制插入的記錄其“院系編號”字段值必須在表“院系”中存在對應(yīng)的記錄。 55第55頁，共103頁，2022年，5月20日，16點9分，星期二7.2.2 inserted和deleted表 deleted表用于存儲DELETE和UPDATE語句所影響的行的復(fù)本。在執(zhí)行DELETE或UPDATE語句時，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。deleted表和觸發(fā)器表通常沒有相同的行。i

35、nserted表用于存儲INSERT和UPDATE語句所影響的行的副本。在一個插入或更新事務(wù)處理中，新建行被同時添加到inserted表和觸發(fā)器表中。inserted表中的行是觸發(fā)器表中新行的副本。56第56頁，共103頁，2022年，5月20日，16點9分，星期二inserted和deleted表的變化在設(shè)置觸發(fā)器條件時，應(yīng)當(dāng)為引發(fā)觸發(fā)器的操作恰當(dāng)使用inserted和deleted表。通常在插入數(shù)據(jù)時，可以從inserted表中讀取新插入的值，此時deleted表不會發(fā)生變化。在刪除數(shù)據(jù)時，可以從deleted表中讀取已經(jīng)刪除或修改的值，而inserted表不會發(fā)生變化。在更新數(shù)據(jù)時，in

36、serted表和deleted表都發(fā)生變化?？梢詮膁eleted表中讀取原有的值，從inserted表中讀取修改后的值。 57第57頁，共103頁，2022年，5月20日，16點9分，星期二7.2.3 事務(wù)的概念及應(yīng)用 在觸發(fā)器中經(jīng)常會取消用戶先前進(jìn)行的操作，例如不允許插入不符合條件的數(shù)據(jù)。SQL Server提供了一種叫做事務(wù)的機制，它可以保證指定的對數(shù)據(jù)庫的一系列操作作為一個整體被執(zhí)行，在最終提交操作之間，用戶可以隨時取消前面的操作，將數(shù)據(jù)庫還原到?jīng)]有執(zhí)行操作前的狀態(tài)。 58第58頁，共103頁，2022年，5月20日，16點9分，星期二事務(wù)的屬性原子性: 事務(wù)必須是原子工作單元。它對數(shù)據(jù)

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

38、商場選擇商品，然后向商家提交購物請求，并進(jìn)行網(wǎng)上支付。此時，交易狀態(tài)為提交。商家獲得用戶提交的購物請求，在確認(rèn)收到網(wǎng)上支付的金額后向用戶郵寄商品。此時，交易狀態(tài)為已處理。用戶在收到商品并確認(rèn)商品無質(zhì)量后，在網(wǎng)上商場確認(rèn)已收到商品。此時，交易狀態(tài)為成功。定義一個事務(wù)需要3種操作，即啟動事務(wù)、回滾事務(wù)和提交事務(wù)。啟動事務(wù)相當(dāng)于用戶提交購物請求之前的狀態(tài)，回滾事務(wù)相當(dāng)于用戶取消當(dāng)前交易，提交事務(wù)相當(dāng)于用戶確認(rèn)交易成功。60第60頁，共103頁，2022年，5月20日，16點9分，星期二啟動事務(wù) SQL Server中包括2種啟動事務(wù)的模式，即顯式事務(wù)和隱式事務(wù)。顯式事務(wù)：通過BEGIN TRANSA

39、CTION語句顯式啟動事務(wù)。BEGIN TRANSACTION語句的基本語法如下： BEGIN TRANSACTION 事務(wù)名 在顯式事務(wù)中，事務(wù)名是可選項。事務(wù)名必須符合標(biāo)識符命名規(guī)則。隱式事務(wù)：當(dāng)用戶沒有顯式地定義事務(wù)時，SQL Server按其默認(rèn)的規(guī)定自動劃分事務(wù)。61第61頁，共103頁，2022年，5月20日，16點9分，星期二回滾事務(wù)如果服務(wù)器錯誤使事務(wù)無法成功完成，SQL Server將自動回滾該事務(wù)，并釋放該事務(wù)占用的所有資源。如果客戶端與SQL Server的網(wǎng)絡(luò)連接中斷了，那么當(dāng)網(wǎng)絡(luò)告知SQL Server該中斷時，將回滾該連接的所有未完成事務(wù)。如果用戶需要手動回滾事務(wù)，

40、可以使用語句：ROLLBACK TRANSACTION 事務(wù)名 其中，“事務(wù)名”是給 BEGIN TRANSACTION 上的事務(wù)指派的名稱。62第62頁，共103頁，2022年，5月20日，16點9分，星期二提交事務(wù)COMMIT TRANSACTION語句可以標(biāo)志一個成功的隱性事務(wù)或顯式事務(wù)的結(jié)束，它的基本語法如下： COMMIT TRANSACTION 事務(wù)名注意，不能在發(fā)出COMMIT TRANSACTION語句之后回滾事務(wù)，因為數(shù)據(jù)修改已經(jīng)成為數(shù)據(jù)庫的永久部分。63第63頁，共103頁，2022年，5月20日，16點9分，星期二例7-11定義一個事務(wù)，向“學(xué)生”表中插入兩條記錄。其中，

41、第1條INSERT語句是正確的，而第2條INSERT語句是錯誤的。執(zhí)行此事務(wù)語句后，查看“學(xué)生”表中的數(shù)據(jù)，確認(rèn)第1條語句沒有被執(zhí)行。 USE 學(xué)生管理 GO BEGIN TRANSACTION INSERT INTO 學(xué)生 VALUES(test1, 1, 一班, 500, 2) INSERT INTO 學(xué)生 VALUES(10, test2, 1, 二班, 500, 2) COMMIT TRANSACTION GO64第64頁，共103頁，2022年，5月20日，16點9分，星期二例7-11第2條插入語句因為指定了標(biāo)識列的值，所以產(chǎn)生錯誤。執(zhí)行此事務(wù)語句的結(jié)果如下： 服務(wù)器: 消息 810

42、1，級別 16，狀態(tài) 1，行 1在查詢分析器中執(zhí)行如下語句： SELECT * FROM 學(xué)生 可以看到，因為第2條INSERT語句出現(xiàn)錯誤，導(dǎo)致事務(wù)回滾，所以第1條語句所插入的記錄也沒有出現(xiàn)在結(jié)果集中。65第65頁，共103頁，2022年，5月20日，16點9分，星期二例7-12定義一個事務(wù)，向“學(xué)生”表中插入一條記錄，然后將事務(wù)回滾。執(zhí)行此事務(wù)語句后，查看“學(xué)生”表中的數(shù)據(jù)，確認(rèn)INSERT語句插入的數(shù)據(jù)不在結(jié)果集中。 USE 學(xué)生管理 GO BEGIN TRANSACTION INSERT INTO 學(xué)生 VALUES(test1, 1, 一班, 500, 2) ROLLBACK TRA

43、NSACTION GO66第66頁，共103頁，2022年，5月20日，16點9分，星期二例7-12執(zhí)行此事務(wù)語句的結(jié)果如下：（所影響的行數(shù)為 1 行） 證明INSERT語句已經(jīng)被執(zhí)行。在查詢分析器中執(zhí)行如下語句： SELECT * FROM 學(xué)生 可以看到，因為執(zhí)行了ROLLBACK TRANSACTION語句，導(dǎo)致事務(wù)回滾，所以INSERT語句所插入的記錄也沒有出現(xiàn)在結(jié)果集中。67第67頁，共103頁，2022年，5月20日，16點9分，星期二7.2.4 創(chuàng)建觸發(fā)器在企業(yè)管理器中手動地創(chuàng)建 使用CREATE TRIGGER語句創(chuàng)建 68第68頁，共103頁，2022年，5月20日，16點9

44、分，星期二在創(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ù)庫對象，其名稱必須遵循標(biāo)識符的命名規(guī)則。雖然觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。69第69頁，共103頁，2022年，5月20日，16點9分，星期二在創(chuàng)建觸發(fā)器之前，應(yīng)該考慮以下問題 雖然不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時表，不應(yīng)引用系統(tǒng)表。如果已經(jīng)給一個

45、表的外鍵定義了級聯(lián)刪除或級聯(lián)更新，則不能在該表上定義INSTEAD OF DELETE或INSTEAD OF DELETE UPDATE 觸發(fā)器。雖然TRUNCATE TABLE語句（刪除表中的所有行）類似于沒有WHERE子句的DELETE語句，但它并不會引發(fā)DELETE觸發(fā)器。WRITETEXT語句（更新text、ntext或image類型的列）不會引發(fā)INSERT或UPDATE觸發(fā)器。70第70頁，共103頁，2022年，5月20日，16點9分，星期二使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器CREATE TRIGGER 觸發(fā)器名ON 表名 | 視圖名 WITH ENCRYPTION

46、FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS IF UPDATE ( 列 ) AND | OR UPDATE ( 列 ) .n SQL語句 .n 71第71頁，共103頁，2022年，5月20日，16點9分，星期二參數(shù)說明觸發(fā)器名：必須符合標(biāo)識符的命名規(guī)則，并且在數(shù)據(jù)庫中必須唯一?？梢赃x擇是否指定觸發(fā)器所有者的名稱。 表名 | 視圖名：是在其上執(zhí)行觸發(fā)器的表或視圖，有時稱為觸發(fā)器表或觸發(fā)器視圖?？梢赃x擇是否指定表或視圖的所有者名稱。WITH ENCRYPTION：加密syscomments表中包含CREATE TRIGGER語句文本的

47、條目。AFTER：指定觸發(fā)器只有在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。如果僅指定FOR關(guān)鍵字，則AFTER是默認(rèn)設(shè)置。不能在視圖上定義AFTER觸發(fā)器。72第72頁，共103頁，2022年，5月20日，16點9分，星期二參數(shù)說明INSTEAD OF：指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)SQL語句，從而替代觸發(fā)SQL語句的操作。在表或視圖上，每個INSERT、UPDATE或DELETE語句最多可以定義一個INSTEAD OF觸發(fā)器。然而，可以在每個具有INSTEAD OF觸發(fā)器的視圖上定義視圖。 DELETE , INSER

48、T , UPDATE ：是指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語句時將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個選項。在觸發(fā)器定義中允許使用以任意順序組合的這些關(guān)鍵字。如果指定的選項多于一個，需用逗號分隔這些選項。73第73頁，共103頁，2022年，5月20日，16點9分，星期二參數(shù)說明AS：引入觸發(fā)器要執(zhí)行的操作。IF UPDATE (列)：用于判斷是否在指定的列上進(jìn)行了INSERT或UPDATE操作（不能用于DELETE操作），可以指定多列。因為在ON子句中指定了表名，所以在IF UPDATE子句中的列名前不要包含表名。對于INSERT操作，IF UPDATE將返回TRUE值，因為這些列插入了數(shù)據(jù)

49、。SQL語句：當(dāng)嘗試 DELETE、INSERT或UPDATE操作時要執(zhí)行的Transact-SQL語句。74第74頁，共103頁，2022年，5月20日，16點9分，星期二圖書館管理案例讀者表讀者種類表圖書表借閱表75第75頁，共103頁，2022年，5月20日，16點9分，星期二案例：圖書借閱 Create TRIGGER inserttr1 ON dbo.借閱表 FOR INSERT,update AS/*自動填充應(yīng)還書日期值*/ declare day int select day=借書期限from 讀者種類信息表a,讀者表b,insertedwhere a. 種類編號=b.讀者種類a

50、nd b.讀者編號=inserted.讀者編號update 借閱表set 應(yīng)還書日期=出借日期+day where 讀者編號=(select 讀者編號from inserted)76第76頁，共103頁，2022年，5月20日，16點9分，星期二/*自動更新罰金*/declare retudate datetimeset retudate=select retudate=還書日期 from inserteddeclare num intif retudate if exists(select * from inserted where 應(yīng)還書日期還書日期) begin update 借閱表 s

51、et 罰金=0.1*datediff(day,應(yīng)還書日期,retudate) where 讀者編號=(select 讀者編號from inserted) end else begin update 借閱表 set 罰金=0 where 讀者編號=(select 讀者編號from inserted) end 77第77頁，共103頁，2022年，5月20日，16點9分，星期二/*自動更新圖書表中的已借出量*/select num=借閱數(shù)量, retudate=還書日期from insertedif retudate begin update 圖書表 set 已借出量=已借出量-num where

52、 圖書編號=(select 圖書編號from inserted) endelse begin update 圖書表 set 已借出量=已借出量+num where 圖書編號=(select 圖書編號from inserted) end 78第78頁，共103頁，2022年，5月20日，16點9分，星期二例7-13 INSERT觸發(fā)器 在“學(xué)生”表中創(chuàng)建一個INSERT觸發(fā)器，如果插入記錄的院系編號值在“院系”表中存在下級單位（例如，計算機學(xué)院包括軟件系），則不執(zhí)行插入操作，并提示用戶。 CREATE TRIGGER insert_student ON 學(xué)生 FOR INSERT AS DECL

53、ARE orgid int DECLARE orgname varchar(100) SELECT orgid = 所屬院系 FROM inserted 79第79頁，共103頁，2022年，5月20日，16點9分，星期二例7-13 INSERT觸發(fā)器 - 判斷插入的院系記錄是否存在上級記錄 SELECT orgname=院系名稱 FROM 院系 WHERE 上級編號=orgid IF orgname -如果存在上級院系 BEGIN PRINT 指定院系存在下級單位，請選擇具體單位！ ROLLBACK TRANSACTION END GO80第80頁，共103頁，2022年，5月20日，16點

54、9分，星期二例7-13 INSERT觸發(fā)器 為了驗證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語句： INSERT INTO 學(xué)生 VALUES(小朱, 0, 二班, 500, 1) 因為院系編號為1的記錄存在下級單位，所以返回結(jié)果如下： 指定院系存在下級單位，請選擇具體單位！81第81頁，共103頁，2022年，5月20日，16點9分，星期二例7-14 UPDATE觸發(fā)器在“學(xué)生”表中創(chuàng)建一個UPDATE觸發(fā)器，如果修改記錄的院系編號值在“院系”表中存在下級單位（例如，計算機學(xué)院包括軟件系），則不執(zhí)行修改操作，并提示用戶。 CREATE TRIGGER update_student ON 學(xué)

55、生 FOR UPDATE AS - 從表inserted中獲取更新后記錄的院系編號 DECLARE orgid int DECLARE orgname varchar(100) SELECT orgid = 所屬院系 FROM inserted82第82頁，共103頁，2022年，5月20日，16點9分，星期二例7-14 UPDATE觸發(fā)器 - 判斷修改的院系記錄是否存在上級記錄 SELECT orgname=院系名稱 FROM 院系 WHERE 上級編號=orgid IF orgname BEGIN PRINT 指定院系存在下級單位，請選擇具體單位！ ROLLBACK TRANSACTION

56、 - 回滾操作 END GO83第83頁，共103頁，2022年，5月20日，16點9分，星期二例7-14 UPDATE觸發(fā)器 為了驗證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語句： UPDATE 學(xué)生 SET 所屬院系=1 WHERE 所屬院系=2 因為院系編號為1的記錄存在下級單位，所以返回結(jié)果如下： 指定院系存在下級單位，請選擇具體單位！84第84頁，共103頁，2022年，5月20日，16點9分，星期二例7-15 DELETE觸發(fā)器在“院系”表中創(chuàng)建一個DELETE觸發(fā)器，如果刪除記錄的院系編號值在“院系”表中存在下級單位（例如，計算機學(xué)院包括軟件系），則不執(zhí)行刪除操作，并提示用戶。

57、 CREATE TRIGGER delete_org ON 院系 FOR DELETE AS - 從表deleted中獲取刪除記錄的院系編號 DECLARE orgid int DECLARE orgname varchar(100) SELECT orgid = 記錄編號 FROM deleted85第85頁，共103頁，2022年，5月20日，16點9分，星期二例7-15 DELETE觸發(fā)器- 判斷刪除的院系記錄是否存在下級記錄SELECT orgname=院系名稱 FROM 院系 WHERE 上級編號=orgidIF orgname BEGIN PRINT 指定院系存在下級單位，不允許被

58、刪除！ ROLLBACK TRANSACTION - 回滾操作 ENDGO 86第86頁，共103頁，2022年，5月20日，16點9分，星期二例7-15 DELETE觸發(fā)器 為了驗證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語句： DELETE FROM 院系 WHERE 記錄編號=1 因為院系編號為1的記錄存在下級單位，所以返回結(jié)果如下： 指定院系存在下級單位，不允許被刪除！87第87頁，共103頁，2022年，5月20日，16點9分，星期二例7-16 INSTEAD OF觸發(fā)器使用INSTEAD OF觸發(fā)器實現(xiàn)例7-15的功能。 CREATE TRIGGER delete_org1 ON

59、 院系 INSTEAD OF DELETE AS - 從表deleted中獲取刪除記錄的院系編號 DECLARE orgid int DECLARE orgname varchar(100) SELECT orgid = 記錄編號 FROM deleted88第88頁，共103頁，2022年，5月20日，16點9分，星期二例7-16 INSTEAD OF觸發(fā)器- 判斷修改的院系記錄是否存在下級記錄SELECT orgname=院系名稱 FROM 院系 WHERE 上級編號=orgidIF orgname PRINT 指定院系存在下級單位，不允許被刪除！ ELSE DELETE FROM 院系

60、WHERE 記錄編號=orgid GO89第89頁，共103頁，2022年，5月20日，16點9分，星期二例7-16 INSTEAD OF觸發(fā)器 因為INSTEAD OF觸發(fā)器使用觸發(fā)器中定義的代碼取代原操作，所以不需要進(jìn)行回滾操作。當(dāng)然，如果原操作符合規(guī)定的條件，還需要在觸發(fā)器中重新執(zhí)行此操作。為了驗證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語句： DELETE FROM 院系 WHERE 記錄編號=1 因為院系編號為1的記錄存在下級單位，所以返回結(jié)果如下： 指定院系存在下級單位，不允許被刪除！ （所影響的行數(shù)為 1 行）90第90頁，共103頁，2022年，5月20日，16點9分，星期二