第10章 存儲(chǔ)過(guò)程和觸發(fā)器

1、第第10章章 存儲(chǔ)過(guò)程、觸發(fā)器存儲(chǔ)過(guò)程、觸發(fā)器n存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)是存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)是SQL ServerSQL Server數(shù)據(jù)庫(kù)的三個(gè)重要組成數(shù)據(jù)庫(kù)的三個(gè)重要組成部分。部分。SQL Server 2008SQL Server 2008使用它們從不同方面提高數(shù)據(jù)處理能使用它們從不同方面提高數(shù)據(jù)處理能力。力。n在在SQL Server 2008SQL Server 2008中，可以像其他程序設(shè)計(jì)語(yǔ)言一樣定義子中，可以像其他程序設(shè)計(jì)語(yǔ)言一樣定義子程序，稱(chēng)為存儲(chǔ)過(guò)程程序，稱(chēng)為存儲(chǔ)過(guò)程。運(yùn)。運(yùn)用用它可它可以創(chuàng)建健壯、安全且具有良好以創(chuàng)建健壯、安全且具有良好性能的數(shù)據(jù)庫(kù)，可以為用戶實(shí)性能的數(shù)

2、據(jù)庫(kù)，可以為用戶實(shí)現(xiàn)復(fù)現(xiàn)復(fù)雜的商業(yè)事務(wù)。雜的商業(yè)事務(wù)。n觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程：它通過(guò)事件觸發(fā)而被自動(dòng)觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程：它通過(guò)事件觸發(fā)而被自動(dòng)執(zhí)行。自動(dòng)執(zhí)行意味著更少的手工操作以及更小的出錯(cuò)機(jī)率。執(zhí)行。自動(dòng)執(zhí)行意味著更少的手工操作以及更小的出錯(cuò)機(jī)率。觸發(fā)器用于強(qiáng)制復(fù)雜的完整性檢查，審核更改，維護(hù)不規(guī)范的觸發(fā)器用于強(qiáng)制復(fù)雜的完整性檢查，審核更改，維護(hù)不規(guī)范的數(shù)據(jù)等等。數(shù)據(jù)等等。SQL Server 2008SQL Server 2008允許允許DMLDML語(yǔ)句和語(yǔ)句和DDLDDL語(yǔ)句創(chuàng)建觸發(fā)語(yǔ)句創(chuàng)建觸發(fā)器，可以引發(fā)器，可以引發(fā)AFTERAFTER或者或者INSTEAD OF

3、INSTEAD OF觸發(fā)事件。觸發(fā)事件。n游標(biāo)主要用于實(shí)現(xiàn)一些不能使用面向集合的語(yǔ)句實(shí)現(xiàn)的操作。游標(biāo)主要用于實(shí)現(xiàn)一些不能使用面向集合的語(yǔ)句實(shí)現(xiàn)的操作。通過(guò)游標(biāo)，通過(guò)游標(biāo)，SQL ServerSQL Server提供了一個(gè)對(duì)結(jié)果集進(jìn)行逐行處理的能提供了一個(gè)對(duì)結(jié)果集進(jìn)行逐行處理的能力?？梢园延螛?biāo)看為一種特殊的指針，它可以指向結(jié)果集中的力?？梢园延螛?biāo)看為一種特殊的指針，它可以指向結(jié)果集中的任意位置，在查詢數(shù)據(jù)的同時(shí)對(duì)數(shù)據(jù)進(jìn)行處理。任意位置，在查詢數(shù)據(jù)的同時(shí)對(duì)數(shù)據(jù)進(jìn)行處理。本章學(xué)習(xí)目標(biāo)：本章學(xué)習(xí)目標(biāo)：n了解存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)的基本概念與特點(diǎn)了解存儲(chǔ)過(guò)程、觸發(fā)器和游標(biāo)的基本概念與特點(diǎn)n掌握存儲(chǔ)過(guò)程的

4、基本類(lèi)型和相關(guān)操作掌握存儲(chǔ)過(guò)程的基本類(lèi)型和相關(guān)操作n掌握觸發(fā)器的類(lèi)型與相關(guān)操作掌握觸發(fā)器的類(lèi)型與相關(guān)操作10.1 10.1 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程n存儲(chǔ)過(guò)程是一組完成特定功能的存儲(chǔ)過(guò)程是一組完成特定功能的SQL SQL 語(yǔ)句集合，經(jīng)編語(yǔ)句集合，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中。譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中。n存儲(chǔ)過(guò)程作為一個(gè)單元進(jìn)行處理并以一個(gè)名稱(chēng)來(lái)標(biāo)識(shí)存儲(chǔ)過(guò)程作為一個(gè)單元進(jìn)行處理并以一個(gè)名稱(chēng)來(lái)標(biāo)識(shí)。它能向用戶返回?cái)?shù)據(jù)、向數(shù)據(jù)庫(kù)表中寫(xiě)入或修改數(shù)。它能向用戶返回?cái)?shù)據(jù)、向數(shù)據(jù)庫(kù)表中寫(xiě)入或修改數(shù)據(jù)、執(zhí)行系統(tǒng)函數(shù)和進(jìn)行管理操作。據(jù)、執(zhí)行系統(tǒng)函數(shù)和進(jìn)行管理操作。n用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存用戶通過(guò)指定存儲(chǔ)過(guò)程

5、的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：n1. 1. 減少網(wǎng)絡(luò)流量：減少網(wǎng)絡(luò)流量：存儲(chǔ)過(guò)程在數(shù)據(jù)庫(kù)服務(wù)器端執(zhí)行，只向客存儲(chǔ)過(guò)程在數(shù)據(jù)庫(kù)服務(wù)器端執(zhí)行，只向客戶端返回執(zhí)行結(jié)果。因此可以將在網(wǎng)絡(luò)中要發(fā)送的數(shù)百行代碼，戶端返回執(zhí)行結(jié)果。因此可以將在網(wǎng)絡(luò)中要發(fā)送的數(shù)百行代碼，編寫(xiě)為一條存儲(chǔ)過(guò)程，這樣客戶端只需要提交存儲(chǔ)過(guò)程的名稱(chēng)編寫(xiě)為一條存儲(chǔ)過(guò)程，這樣客戶端只需要提交存儲(chǔ)過(guò)程的名稱(chēng)和參數(shù)，即可實(shí)現(xiàn)相應(yīng)功能，節(jié)省了網(wǎng)絡(luò)流量，提高了執(zhí)行的和參數(shù)，即可實(shí)現(xiàn)相應(yīng)功能，節(jié)省了網(wǎng)絡(luò)流量，提高了執(zhí)行的效率。此外，由于所有的操作都在服務(wù)器端完成，避免

6、了在客效率。此外，由于所有的操作都在服務(wù)器端完成，避免了在客戶端和服務(wù)器端之間的多次往返。存儲(chǔ)過(guò)程只需要將最終結(jié)果戶端和服務(wù)器端之間的多次往返。存儲(chǔ)過(guò)程只需要將最終結(jié)果通過(guò)網(wǎng)絡(luò)傳輸?shù)娇蛻舳?。通過(guò)網(wǎng)絡(luò)傳輸?shù)娇蛻舳?。n2. 2. 提高系統(tǒng)性能提高系統(tǒng)性能：一般：一般T-SQLT-SQL語(yǔ)句每執(zhí)行一次就需要編譯一次，語(yǔ)句每執(zhí)行一次就需要編譯一次，而存儲(chǔ)過(guò)程只在創(chuàng)建時(shí)進(jìn)行編譯，被編譯后存放在數(shù)據(jù)庫(kù)服務(wù)而存儲(chǔ)過(guò)程只在創(chuàng)建時(shí)進(jìn)行編譯，被編譯后存放在數(shù)據(jù)庫(kù)服務(wù)器的過(guò)程高速緩存中，當(dāng)使用時(shí)，服務(wù)器不必再重新分析和編器的過(guò)程高速緩存中，當(dāng)使用時(shí)，服務(wù)器不必再重新分析和編譯它們。因此，當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)（如

7、對(duì)多個(gè)表進(jìn)行譯它們。因此，當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)（如對(duì)多個(gè)表進(jìn)行UPDATEUPDATE、INSERTINSERT或或DELETEDELETE操作時(shí)），可將這些復(fù)雜操作用存儲(chǔ)操作時(shí)），可將這些復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用，節(jié)省了過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用，節(jié)省了分析、解析和優(yōu)化代碼所需的分析、解析和優(yōu)化代碼所需的CPUCPU資源和時(shí)間。資源和時(shí)間。存存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：儲(chǔ)過(guò)程的優(yōu)點(diǎn)：n3. 3. 安全性高安全性高：使用存儲(chǔ)過(guò)程可以完成所有數(shù)據(jù)庫(kù)操作，并且：使用存儲(chǔ)過(guò)程可以完成所有數(shù)據(jù)庫(kù)操作，并且可授予沒(méi)有直接執(zhí)行存儲(chǔ)過(guò)程中語(yǔ)句的權(quán)限的用戶，也可

8、執(zhí)可授予沒(méi)有直接執(zhí)行存儲(chǔ)過(guò)程中語(yǔ)句的權(quán)限的用戶，也可執(zhí)行該存儲(chǔ)過(guò)程的權(quán)限。另外可以防止用戶直接訪問(wèn)表，強(qiáng)制行該存儲(chǔ)過(guò)程的權(quán)限。另外可以防止用戶直接訪問(wèn)表，強(qiáng)制用戶使用存儲(chǔ)過(guò)程執(zhí)行特定的任務(wù)。用戶使用存儲(chǔ)過(guò)程執(zhí)行特定的任務(wù)。n4. 4. 可重用性：可重用性：存儲(chǔ)過(guò)程只需創(chuàng)建并存儲(chǔ)在數(shù)據(jù)庫(kù)中，以后即存儲(chǔ)過(guò)程只需創(chuàng)建并存儲(chǔ)在數(shù)據(jù)庫(kù)中，以后即可任意在程序中調(diào)用該過(guò)程。存儲(chǔ)過(guò)程可獨(dú)立于程序源代碼可任意在程序中調(diào)用該過(guò)程。存儲(chǔ)過(guò)程可獨(dú)立于程序源代碼而單獨(dú)修改，減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量。而單獨(dú)修改，減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量。n5 5. . 可自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)可自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)：存儲(chǔ)過(guò)

9、程可以在系統(tǒng)啟：存儲(chǔ)過(guò)程可以在系統(tǒng)啟動(dòng)時(shí)自動(dòng)執(zhí)行，完成一些需要預(yù)先執(zhí)行的任務(wù)，而不必在系動(dòng)時(shí)自動(dòng)執(zhí)行，完成一些需要預(yù)先執(zhí)行的任務(wù)，而不必在系統(tǒng)啟動(dòng)后再進(jìn)行人工操作。統(tǒng)啟動(dòng)后再進(jìn)行人工操作。存儲(chǔ)過(guò)程的類(lèi)型存儲(chǔ)過(guò)程的類(lèi)型1. 1. 系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程 系統(tǒng)存儲(chǔ)過(guò)程是系統(tǒng)已定義好的完成特定功能的存儲(chǔ)過(guò)系統(tǒng)存儲(chǔ)過(guò)程是系統(tǒng)已定義好的完成特定功能的存儲(chǔ)過(guò)程，用戶可直接調(diào)用。系統(tǒng)存儲(chǔ)過(guò)程以程，用戶可直接調(diào)用。系統(tǒng)存儲(chǔ)過(guò)程以sp_sp_為前綴。為前綴。2. 2. 用戶自定義存儲(chǔ)過(guò)程用戶自定義存儲(chǔ)過(guò)程 用戶自定義存儲(chǔ)過(guò)程在用戶數(shù)據(jù)庫(kù)中創(chuàng)建，通常與數(shù)據(jù)用戶自定義存儲(chǔ)過(guò)程在用戶數(shù)據(jù)庫(kù)中創(chuàng)建，通常與數(shù)據(jù)庫(kù)對(duì)象進(jìn)

10、行交互，用于完成特定數(shù)據(jù)庫(kù)操作任務(wù)，可以庫(kù)對(duì)象進(jìn)行交互，用于完成特定數(shù)據(jù)庫(kù)操作任務(wù)，可以接受和返回用戶提供的參數(shù)，名稱(chēng)不能以接受和返回用戶提供的參數(shù)，名稱(chēng)不能以sp_sp_為前綴。為前綴。n1. 1. 使用使用SQL Server Management StudioSQL Server Management Studio創(chuàng)建存儲(chǔ)創(chuàng)建存儲(chǔ)過(guò)程過(guò)程n2.2.使用使用CREATE PROCEDURECREATE PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程，語(yǔ)語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程，語(yǔ)法格式為法格式為：CREATE PROCEDURE procedure_name parameter data_type = def

11、aultOUTPUT ,n AS sql_statement n 110.1.1 10.1.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程 其中：其中：qparameterparameter：過(guò)程中的參數(shù)。在該語(yǔ)句中可以聲明過(guò)程中的參數(shù)。在該語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。用戶必須在執(zhí)行過(guò)程時(shí)提供每個(gè)一個(gè)或多個(gè)參數(shù)。用戶必須在執(zhí)行過(guò)程時(shí)提供每個(gè)所聲明參數(shù)的值，使用所聲明參數(shù)的值，使用 符號(hào)作為第一個(gè)字符來(lái)指符號(hào)作為第一個(gè)字符來(lái)指定參數(shù)名稱(chēng)。定參數(shù)名稱(chēng)。 qdata_typedata_type：參數(shù)的數(shù)據(jù)類(lèi)型。參數(shù)的數(shù)據(jù)類(lèi)型。qD Defaultefault：參數(shù)的默認(rèn)值。參數(shù)的默認(rèn)值。qOUTPUTOUTPUT：

12、表明參數(shù)是返回參數(shù)。表明參數(shù)是返回參數(shù)?！纠?創(chuàng)建一個(gè)存儲(chǔ)過(guò)程創(chuàng)建一個(gè)存儲(chǔ)過(guò)程stud_degreestud_degree，用于檢索所有學(xué)生的，用于檢索所有學(xué)生的成績(jī)記錄，包括學(xué)號(hào)、姓名、課程名和分?jǐn)?shù)。成績(jī)記錄，包括學(xué)號(hào)、姓名、課程名和分?jǐn)?shù)。解：對(duì)應(yīng)的程序如下：解：對(duì)應(yīng)的程序如下：CREATE PROCEDURE CREATE PROCEDURE stud_degreestud_degree ASAS SELECT SELECT student.student.學(xué)號(hào)學(xué)號(hào), ,姓名姓名, ,課程名課程名, ,分?jǐn)?shù)分?jǐn)?shù) FROM FROM student,course,scorestudent

13、,course,score WHERE student. WHERE student.學(xué)號(hào)學(xué)號(hào)=score.=score.學(xué)號(hào)學(xué)號(hào) AND course. AND course.課程號(hào)課程號(hào)=score.=score.課程號(hào)課程號(hào) ORDER BY student.ORDER BY student.學(xué)號(hào)學(xué)號(hào)n執(zhí)行存儲(chǔ)過(guò)程使用執(zhí)行存儲(chǔ)過(guò)程使用EXECUTEEXECUTE語(yǔ)句，其格式如下：語(yǔ)句，其格式如下： EXEC UTE return_status = procedure_name parameter = value | variable OUTPUT | DEFAULT , n 10.1.2

14、 10.1.2 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程 q各個(gè)參數(shù)的含義：各個(gè)參數(shù)的含義：l return_statusreturn_status 保存存儲(chǔ)過(guò)程的返回狀態(tài)保存存儲(chǔ)過(guò)程的返回狀態(tài)lprocedure_nameprocedure_name 調(diào)用的存儲(chǔ)過(guò)程的名稱(chēng)調(diào)用的存儲(chǔ)過(guò)程的名稱(chēng)lparameter parameter 過(guò)程參數(shù)過(guò)程參數(shù)lValue Value 過(guò)程參數(shù)的值過(guò)程參數(shù)的值lvariable variable 用來(lái)保存參數(shù)或返回參數(shù)的變量用來(lái)保存參數(shù)或返回參數(shù)的變量lOUTPUTOUTPUT 指定存儲(chǔ)過(guò)程必須返回的一個(gè)參數(shù)指定存儲(chǔ)過(guò)程必須返回的一個(gè)參數(shù)lDEFAULT DEFAULT

15、 默認(rèn)參數(shù)值默認(rèn)參數(shù)值n在調(diào)用存儲(chǔ)過(guò)程時(shí)的兩種傳遞參數(shù)的方式：在調(diào)用存儲(chǔ)過(guò)程時(shí)的兩種傳遞參數(shù)的方式：q方式一：方式一： EXEC EXEC 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名 實(shí)參列表實(shí)參列表q方式二：方式二： EXEC EXEC 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名 參數(shù)參數(shù)1=1=值值1,1,參數(shù)參數(shù)2=2=值值2,2,【例例】 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程maxdegreemaxdegree并查看輸出的結(jié)果。并查看輸出的結(jié)果。 create procedure maxderee create procedure maxderee as as begin begin select max(degree) as select

16、 max(degree) as 最高分最高分 from score from score end end執(zhí)行執(zhí)行maxdegreemaxdegree存儲(chǔ)過(guò)程的程序如下：存儲(chǔ)過(guò)程的程序如下：EXEC maxdegreeEXEC maxdegree其執(zhí)行結(jié)果如圖所示：其執(zhí)行結(jié)果如圖所示： n1. 1. 使用參數(shù)使用參數(shù)帶參數(shù)的存儲(chǔ)過(guò)程的一般格式如下：帶參數(shù)的存儲(chǔ)過(guò)程的一般格式如下：CREATE PROCEDURE CREATE PROCEDURE 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名( ( 參數(shù)列表參數(shù)列表 ) )AS SQLAS SQL語(yǔ)句語(yǔ)句10.1.3 10.1.3 存儲(chǔ)過(guò)程的參數(shù)存儲(chǔ)過(guò)程的參數(shù)【例例】 設(shè)

17、計(jì)一個(gè)存儲(chǔ)過(guò)程設(shè)計(jì)一個(gè)存儲(chǔ)過(guò)程maxnomaxno，以學(xué)號(hào)為參數(shù)，輸出指定，以學(xué)號(hào)為參數(shù)，輸出指定學(xué)號(hào)學(xué)生的所有課程中最高分和對(duì)應(yīng)的課程名及成績(jī)。學(xué)號(hào)學(xué)生的所有課程中最高分和對(duì)應(yīng)的課程名及成績(jī)。解：解：CREATE PROCEDURE CREATE PROCEDURE maxnomaxno(no char(10) (no char(10) ASAS SELECT s. SELECT s.學(xué)號(hào)學(xué)號(hào),s.,s.姓名姓名,c.,c.課程名課程名,sc.,sc.分?jǐn)?shù)分?jǐn)?shù) FROM student FROM student s,courses,course c,scorec,score scsc WHE

18、RE s. WHERE s.學(xué)號(hào)學(xué)號(hào)=no AND s.=no AND s.學(xué)號(hào)學(xué)號(hào)=sc.=sc.學(xué)號(hào)學(xué)號(hào) AND c. AND c.課程號(hào)課程號(hào)=sc.=sc.課程號(hào)課程號(hào) AND sc. AND sc.分?jǐn)?shù)分?jǐn)?shù)= = (SELECT MAX( (SELECT MAX(分?jǐn)?shù)分?jǐn)?shù)) FROM score WHERE ) FROM score WHERE 學(xué)號(hào)學(xué)號(hào)=no)=no)GOGO采用方式一執(zhí)行存儲(chǔ)過(guò)程采用方式一執(zhí)行存儲(chǔ)過(guò)程maxnomaxno的程序如下：的程序如下：EXEC EXEC maxnomaxno 103 103采用方式二執(zhí)行存儲(chǔ)過(guò)程采用方式二執(zhí)行存儲(chǔ)過(guò)程maxnomaxno的

19、程序如下：的程序如下：EXEC EXEC maxnomaxno no=103 no=103n2. 2. 使用默認(rèn)參數(shù)使用默認(rèn)參數(shù)q在設(shè)計(jì)存儲(chǔ)過(guò)程時(shí)，可以為參數(shù)提供一個(gè)默認(rèn)值，在設(shè)計(jì)存儲(chǔ)過(guò)程時(shí)，可以為參數(shù)提供一個(gè)默認(rèn)值，默認(rèn)值必須為常量或者默認(rèn)值必須為常量或者NULLNULL。其一般格式如下：。其一般格式如下：CREATE PROCEDURE CREATE PROCEDURE 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名( ( 參數(shù)參數(shù)1=1=默認(rèn)值默認(rèn)值1, 1, 參參數(shù)數(shù)2=2=默認(rèn)值默認(rèn)值2, )2, )AS SQLAS SQL語(yǔ)句語(yǔ)句q在調(diào)用存儲(chǔ)過(guò)程時(shí)，如果不指定對(duì)應(yīng)的實(shí)參值，則在調(diào)用存儲(chǔ)過(guò)程時(shí)，如果不指定對(duì)應(yīng)

20、的實(shí)參值，則自動(dòng)用對(duì)應(yīng)的默認(rèn)值代替。自動(dòng)用對(duì)應(yīng)的默認(rèn)值代替?！纠?設(shè)計(jì)一個(gè)存儲(chǔ)過(guò)程設(shè)計(jì)一個(gè)存儲(chǔ)過(guò)程maxnomaxno，以學(xué)號(hào)為參數(shù)，輸出指定，以學(xué)號(hào)為參數(shù)，輸出指定學(xué)號(hào)學(xué)生的所有課程中最高分和對(duì)應(yīng)的課程名，學(xué)號(hào)默認(rèn)值為學(xué)號(hào)學(xué)生的所有課程中最高分和對(duì)應(yīng)的課程名，學(xué)號(hào)默認(rèn)值為101101。解：解：CREATE PROCEDURE maxno1(no CREATE PROCEDURE maxno1(no intint=101) =101) AS AS SELECT s. SELECT s.學(xué)號(hào)學(xué)號(hào),s.,s.姓名姓名,c.,c.課程名課程名,sc.,sc.分?jǐn)?shù)分?jǐn)?shù) FROM student F

21、ROM student s,courses,course c,scorec,score scsc WHERE s. WHERE s.學(xué)號(hào)學(xué)號(hào)=no AND s.=no AND s.學(xué)號(hào)學(xué)號(hào)=sc.=sc.學(xué)號(hào)學(xué)號(hào) AND c. AND c.課程號(hào)課程號(hào)=sc.=sc.課程號(hào)課程號(hào) AND sc. AND sc.分?jǐn)?shù)分?jǐn)?shù)= = (SELECT MAX( (SELECT MAX(分?jǐn)?shù)分?jǐn)?shù)) FROM score WHERE ) FROM score WHERE 學(xué)號(hào)學(xué)號(hào)=no)=no)當(dāng)不指定實(shí)參調(diào)用當(dāng)不指定實(shí)參調(diào)用maxno1maxno1存儲(chǔ)過(guò)程時(shí)，其結(jié)果如圖所示：存儲(chǔ)過(guò)程時(shí)，其結(jié)果如圖所示：

22、當(dāng)指定實(shí)參為當(dāng)指定實(shí)參為105105調(diào)用調(diào)用maxno1maxno1存儲(chǔ)過(guò)程時(shí)，其結(jié)果如圖所：存儲(chǔ)過(guò)程時(shí)，其結(jié)果如圖所：n3. 3. 使用返回參數(shù)使用返回參數(shù) 在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，可以定義返回參數(shù)。在執(zhí)在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，可以定義返回參數(shù)。在執(zhí)行存儲(chǔ)過(guò)程時(shí)，可以將結(jié)果給返回參數(shù)。返回參行存儲(chǔ)過(guò)程時(shí)，可以將結(jié)果給返回參數(shù)。返回參數(shù)用數(shù)用OUTPUTOUTPUT進(jìn)行說(shuō)明。進(jìn)行說(shuō)明?！纠?創(chuàng)建一個(gè)存儲(chǔ)過(guò)程創(chuàng)建一個(gè)存儲(chǔ)過(guò)程averageaverage，它返回兩個(gè)參數(shù)，它返回兩個(gè)參數(shù)st_namest_name和和st_avgst_avg，分別代表了姓名和平均分。并編寫(xiě)，分別代表了姓名和平均分。并編寫(xiě)T

23、-T-SQLSQL語(yǔ)句執(zhí)行該存儲(chǔ)過(guò)程和查看輸出的結(jié)果。語(yǔ)句執(zhí)行該存儲(chǔ)過(guò)程和查看輸出的結(jié)果。解：建立存儲(chǔ)過(guò)程解：建立存儲(chǔ)過(guò)程averageaverage的程序如下：的程序如下：CREATE PROCEDURE averageCREATE PROCEDURE average( ( st_nost_no intint, , st_namest_name char(8) char(8) OUTPUTOUTPUT, , st_avgst_avg float float OUTPUTOUTPUT) AS) AS SELECT SELECT st_namest_name=student.=student.姓

24、名姓名,st_avgst_avg=AVG(score.=AVG(score.分?jǐn)?shù)分?jǐn)?shù)) ) FROM FROM student,scorestudent,score WHERE student. WHERE student.學(xué)號(hào)學(xué)號(hào)=score.=score.學(xué)號(hào)學(xué)號(hào) GROUP BY student.GROUP BY student.學(xué)號(hào)學(xué)號(hào),student.,student.姓名姓名 HAVING student.HAVING student.學(xué)號(hào)學(xué)號(hào)=st_nost_no執(zhí)行該存儲(chǔ)過(guò)程，來(lái)查詢學(xué)號(hào)為執(zhí)行該存儲(chǔ)過(guò)程，來(lái)查詢學(xué)號(hào)為“105”105”的學(xué)生姓名和平均分：的學(xué)生姓名和平均分：DE

25、CLARE DECLARE st_namest_name char(10) char(10)DECLARE DECLARE st_avgst_avg float floatEXEC average 105,st_name EXEC average 105,st_name OUTPUTOUTPUT, , st_avgst_avg OUTPUTOUTPUTSELECT SELECT 姓名姓名=st_namest_name,平均分平均分=st_avgst_avgn4. 4. 存儲(chǔ)過(guò)程的返回值存儲(chǔ)過(guò)程的返回值q存儲(chǔ)過(guò)程在執(zhí)行后都會(huì)返回一個(gè)整型值（稱(chēng)為存儲(chǔ)過(guò)程在執(zhí)行后都會(huì)返回一個(gè)整型值（稱(chēng)為“返返回代碼

26、回代碼”），指示存儲(chǔ)過(guò)程的執(zhí)行狀態(tài)。），指示存儲(chǔ)過(guò)程的執(zhí)行狀態(tài)。q如果執(zhí)行成功，返回如果執(zhí)行成功，返回0 0；否則返回；否則返回-1-1-99-99之間的數(shù)之間的數(shù)值（例如值（例如-1-1表示找不到對(duì)象，表示找不到對(duì)象，-2-2表示數(shù)據(jù)類(lèi)型錯(cuò)誤表示數(shù)據(jù)類(lèi)型錯(cuò)誤，-5-5表示語(yǔ)法錯(cuò)誤等）。表示語(yǔ)法錯(cuò)誤等）。q也可以使用也可以使用RETURNRETURN語(yǔ)句指定一個(gè)返回值。語(yǔ)句指定一個(gè)返回值?！纠?編寫(xiě)一個(gè)程序，創(chuàng)建存儲(chǔ)過(guò)程編寫(xiě)一個(gè)程序，創(chuàng)建存儲(chǔ)過(guò)程test_rettest_ret，根據(jù)輸入，根據(jù)輸入的參數(shù)來(lái)判斷返回值。的參數(shù)來(lái)判斷返回值。解：建立存儲(chǔ)過(guò)程解：建立存儲(chǔ)過(guò)程test_rettes

27、t_ret如下：如下：CREATE PROC ret(id int = 0)CREATE PROC ret(id int = 0) IF id=0 IF id=0 RETURN 0 RETURN 0 IF id0 IF id0 RETURN 1000 RETURN 1000 IF id0 IF id0 RETURN -1000 RETURN -1000 執(zhí)行以上存儲(chǔ)過(guò)程：執(zhí)行以上存儲(chǔ)過(guò)程：declare r intdeclare r intexec r=ret 1exec r=ret 1print rprint r運(yùn)行結(jié)果：運(yùn)行結(jié)果：10.1.4 10.1.4 查看、修改和刪除存儲(chǔ)過(guò)程查看、修

28、改和刪除存儲(chǔ)過(guò)程n使用使用SQL ServerSQL Server管理控制器查看或修改存儲(chǔ)過(guò)管理控制器查看或修改存儲(chǔ)過(guò)程程n使用使用sp_helptextsp_helptext存儲(chǔ)過(guò)程來(lái)查看存儲(chǔ)過(guò)程的定存儲(chǔ)過(guò)程來(lái)查看存儲(chǔ)過(guò)程的定義信息義信息n使用使用SQL ServerSQL Server管理控制器刪除存儲(chǔ)過(guò)程管理控制器刪除存儲(chǔ)過(guò)程n使用使用DROP PROCEDUREDROP PROCEDURE刪除存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程【例例】 使用相關(guān)系統(tǒng)存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程使用相關(guān)系統(tǒng)存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程stud_degreestud_degree的的相關(guān)內(nèi)容。相關(guān)內(nèi)容。解：對(duì)應(yīng)的程序如下：解：對(duì)應(yīng)的程序

29、如下：USE schoolUSE schoolGOGOEXEC sp_helptext stud_degreeEXEC sp_helptext stud_degree10.2 10.2 觸發(fā)器觸發(fā)器n觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程。觸發(fā)器可包含復(fù)觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程。觸發(fā)器可包含復(fù)雜的雜的T-SQLT-SQL語(yǔ)句。觸發(fā)器不能通過(guò)名稱(chēng)被直接調(diào)用，語(yǔ)句。觸發(fā)器不能通過(guò)名稱(chēng)被直接調(diào)用，也不允許設(shè)置參數(shù)。它是在插入、刪除和修改指定表也不允許設(shè)置參數(shù)。它是在插入、刪除和修改指定表中的數(shù)據(jù)時(shí)觸發(fā)執(zhí)行。中的數(shù)據(jù)時(shí)觸發(fā)執(zhí)行。n觸發(fā)器可以強(qiáng)制執(zhí)行一定的業(yè)務(wù)規(guī)則，以保持?jǐn)?shù)據(jù)完觸發(fā)器可以強(qiáng)制執(zhí)行一定的業(yè)務(wù)

30、規(guī)則，以保持?jǐn)?shù)據(jù)完整性、檢查數(shù)據(jù)有效性、實(shí)現(xiàn)數(shù)據(jù)庫(kù)管理任務(wù)和一些整性、檢查數(shù)據(jù)有效性、實(shí)現(xiàn)數(shù)據(jù)庫(kù)管理任務(wù)和一些附加功能。附加功能。n對(duì)于數(shù)據(jù)庫(kù)中約束所不能保證的復(fù)雜的參照完整性和對(duì)于數(shù)據(jù)庫(kù)中約束所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性可使用觸發(fā)器來(lái)實(shí)現(xiàn)。數(shù)據(jù)的一致性可使用觸發(fā)器來(lái)實(shí)現(xiàn)。10.2.1 10.2.1 觸發(fā)器概述觸發(fā)器概述1. 1. 觸發(fā)器的功能觸發(fā)器的功能q在在SQL ServerSQL Server內(nèi)部，觸發(fā)器被看作是存儲(chǔ)過(guò)程，它與存內(nèi)部，觸發(fā)器被看作是存儲(chǔ)過(guò)程，它與存儲(chǔ)過(guò)程所經(jīng)歷的處理過(guò)程類(lèi)似。但是觸發(fā)器沒(méi)有輸入?yún)?chǔ)過(guò)程所經(jīng)歷的處理過(guò)程類(lèi)似。但是觸發(fā)器沒(méi)有輸入?yún)?shù)和輸出參數(shù)，

31、因而不能被顯示調(diào)用。它作為語(yǔ)句的執(zhí)數(shù)和輸出參數(shù)，因而不能被顯示調(diào)用。它作為語(yǔ)句的執(zhí)行結(jié)果自動(dòng)引發(fā)，而存儲(chǔ)過(guò)程則是通過(guò)存儲(chǔ)過(guò)程名稱(chēng)而行結(jié)果自動(dòng)引發(fā)，而存儲(chǔ)過(guò)程則是通過(guò)存儲(chǔ)過(guò)程名稱(chēng)而被直接調(diào)用。被直接調(diào)用。q觸發(fā)器與表緊密相連，當(dāng)用戶對(duì)表進(jìn)行諸如觸發(fā)器與表緊密相連，當(dāng)用戶對(duì)表進(jìn)行諸如UPDATEUPDATE、INSERTINSERT和和DELETEDELETE這些操作時(shí)，系統(tǒng)會(huì)自動(dòng)執(zhí)行觸發(fā)器所這些操作時(shí)，系統(tǒng)會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的定義的SQLSQL語(yǔ)句，從而確保對(duì)數(shù)據(jù)的處理符合由這些語(yǔ)句，從而確保對(duì)數(shù)據(jù)的處理符合由這些SQL SQL 語(yǔ)句所定義的規(guī)則。語(yǔ)句所定義的規(guī)則。 q強(qiáng)化約束：觸發(fā)器能夠?qū)?/p>

32、現(xiàn)比強(qiáng)化約束：觸發(fā)器能夠?qū)崿F(xiàn)比CHECK CHECK 語(yǔ)句更為復(fù)雜的約束：語(yǔ)句更為復(fù)雜的約束：l觸發(fā)器可以很方便地引用其他表的列，去進(jìn)行邏輯上的觸發(fā)器可以很方便地引用其他表的列，去進(jìn)行邏輯上的檢查；檢查；l觸發(fā)器是在觸發(fā)器是在CHECKCHECK之后執(zhí)行的；之后執(zhí)行的；l觸發(fā)器可以插入，刪除，更新多行。觸發(fā)器可以插入，刪除，更新多行。q跟蹤變化：觸發(fā)器可以偵測(cè)數(shù)據(jù)庫(kù)內(nèi)的操作從而禁止數(shù)據(jù)跟蹤變化：觸發(fā)器可以偵測(cè)數(shù)據(jù)庫(kù)內(nèi)的操作從而禁止數(shù)據(jù)庫(kù)中未經(jīng)許可的更新和變化，確保輸入表中的數(shù)據(jù)的有效庫(kù)中未經(jīng)許可的更新和變化，確保輸入表中的數(shù)據(jù)的有效性。例如在庫(kù)存系統(tǒng)中，觸發(fā)器可以檢測(cè)到當(dāng)實(shí)際庫(kù)存下性。例如在庫(kù)

33、存系統(tǒng)中，觸發(fā)器可以檢測(cè)到當(dāng)實(shí)際庫(kù)存下降到了需要再進(jìn)貨的臨界量，就給出管理員相應(yīng)提示信息降到了需要再進(jìn)貨的臨界量，就給出管理員相應(yīng)提示信息或自動(dòng)生成給供應(yīng)商的訂單；或自動(dòng)生成給供應(yīng)商的訂單；q級(jí)聯(lián)運(yùn)行：觸發(fā)器可以偵測(cè)數(shù)據(jù)庫(kù)內(nèi)的操作，并自動(dòng)地級(jí)級(jí)聯(lián)運(yùn)行：觸發(fā)器可以偵測(cè)數(shù)據(jù)庫(kù)內(nèi)的操作，并自動(dòng)地級(jí)聯(lián)影響整個(gè)數(shù)據(jù)庫(kù)的不同表中的各項(xiàng)內(nèi)容。如：設(shè)置一個(gè)聯(lián)影響整個(gè)數(shù)據(jù)庫(kù)的不同表中的各項(xiàng)內(nèi)容。如：設(shè)置一個(gè)觸發(fā)器，當(dāng)觸發(fā)器，當(dāng)studentstudent表中刪除一個(gè)學(xué)號(hào)信息時(shí)，對(duì)應(yīng)的表中刪除一個(gè)學(xué)號(hào)信息時(shí)，對(duì)應(yīng)的scorescore表中相應(yīng)的學(xué)號(hào)信息也被刪除；表中相應(yīng)的學(xué)號(hào)信息也被刪除；q調(diào)用存儲(chǔ)過(guò)程：為了響應(yīng)

34、數(shù)據(jù)庫(kù)更新，觸發(fā)器可以調(diào)用一調(diào)用存儲(chǔ)過(guò)程：為了響應(yīng)數(shù)據(jù)庫(kù)更新，觸發(fā)器可以調(diào)用一個(gè)或多個(gè)存儲(chǔ)過(guò)程。個(gè)或多個(gè)存儲(chǔ)過(guò)程。2. 2. 觸發(fā)器的種類(lèi)觸發(fā)器的種類(lèi)nSQL Server 2008SQL Server 2008支持兩種類(lèi)型的觸發(fā)器：支持兩種類(lèi)型的觸發(fā)器：DMLDML觸發(fā)器和觸發(fā)器和DDLDDL觸發(fā)器。觸發(fā)器。qDMLDML觸發(fā)器觸發(fā)器：如果用戶要通過(guò)數(shù)據(jù)操作語(yǔ)言：如果用戶要通過(guò)數(shù)據(jù)操作語(yǔ)言 (DML)(DML)編輯編輯數(shù)據(jù)，則執(zhí)行數(shù)據(jù)，則執(zhí)行 DML DML 觸發(fā)器。觸發(fā)器。DML DML 事件是針對(duì)表或視圖事件是針對(duì)表或視圖的的 INSERTINSERT、UPDATEUPDATE和和DEL

35、ETE DELETE 語(yǔ)句，即語(yǔ)句，即DMLDML觸發(fā)器在數(shù)觸發(fā)器在數(shù)據(jù)修改時(shí)被執(zhí)行。系統(tǒng)將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可據(jù)修改時(shí)被執(zhí)行。系統(tǒng)將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到錯(cuò)誤（例在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到錯(cuò)誤（例如，磁盤(pán)空間不足），則整個(gè)事務(wù)自動(dòng)回滾；如，磁盤(pán)空間不足），則整個(gè)事務(wù)自動(dòng)回滾；qDDLDDL觸發(fā)器：觸發(fā)器：為了響應(yīng)各種數(shù)據(jù)定義語(yǔ)言為了響應(yīng)各種數(shù)據(jù)定義語(yǔ)言 (DDL) (DDL) 事件而事件而激發(fā)。激發(fā)。DDLDDL事件主要與以關(guān)鍵字事件主要與以關(guān)鍵字 CREATECREATE、ALTER ALTER 和和 DROP DROP 開(kāi)頭

36、的開(kāi)頭的 T-SQL T-SQL 語(yǔ)句對(duì)應(yīng)。它們可以用于在數(shù)據(jù)庫(kù)語(yǔ)句對(duì)應(yīng)。它們可以用于在數(shù)據(jù)庫(kù)中執(zhí)行管理任務(wù)，例如，審核以及規(guī)范數(shù)據(jù)庫(kù)操作。中執(zhí)行管理任務(wù)，例如，審核以及規(guī)范數(shù)據(jù)庫(kù)操作。10.2.2 DML10.2.2 DML觸發(fā)器的創(chuàng)建和應(yīng)用觸發(fā)器的創(chuàng)建和應(yīng)用n1. DML1. DML觸發(fā)器的分類(lèi)觸發(fā)器的分類(lèi)qAFTERAFTER觸發(fā)器：觸發(fā)器：這類(lèi)觸發(fā)器是在記錄已經(jīng)被改變完，相這類(lèi)觸發(fā)器是在記錄已經(jīng)被改變完，相關(guān)事務(wù)提交后，才會(huì)被觸發(fā)執(zhí)行。主要是用于記錄變更關(guān)事務(wù)提交后，才會(huì)被觸發(fā)執(zhí)行。主要是用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯(cuò)誤，可以用后的處理或檢查，一旦發(fā)現(xiàn)錯(cuò)誤，可以用ROLLBAC

37、K ROLLBACK TRANSACTIONTRANSACTION語(yǔ)句來(lái)回滾本次的操作。對(duì)同一個(gè)表達(dá)操語(yǔ)句來(lái)回滾本次的操作。對(duì)同一個(gè)表達(dá)操作，可定義多個(gè)作，可定義多個(gè)AFTERAFTER觸發(fā)器，并定義各種觸發(fā)器執(zhí)行觸發(fā)器，并定義各種觸發(fā)器執(zhí)行的先后順序。的先后順序。qINSTEAD OFINSTEAD OF觸發(fā)器：觸發(fā)器：這類(lèi)觸發(fā)器并不去執(zhí)行其所定義的這類(lèi)觸發(fā)器并不去執(zhí)行其所定義的操作（操作（INSERTINSERT、UPDATEUPDATE、DELETEDELETE），而去執(zhí)行觸發(fā)器本），而去執(zhí)行觸發(fā)器本身所定義的操作。這類(lèi)觸發(fā)器一般是用來(lái)取代原本的操身所定義的操作。這類(lèi)觸發(fā)器一般是用來(lái)取代

38、原本的操作，在記錄變更之前被觸發(fā)的。作，在記錄變更之前被觸發(fā)的。n2. 2. 觸發(fā)器中的邏輯（虛擬）表觸發(fā)器中的邏輯（虛擬）表q當(dāng)表被修改，無(wú)論是插入、修改還是刪除，被操作的記當(dāng)表被修改，無(wú)論是插入、修改還是刪除，被操作的記錄會(huì)保存在兩個(gè)系統(tǒng)的邏輯表中，這兩個(gè)邏輯表是錄會(huì)保存在兩個(gè)系統(tǒng)的邏輯表中，這兩個(gè)邏輯表是insertedinserted（插入）表和（插入）表和deleteddeleted（刪除）表。（刪除）表。q這兩個(gè)表是建在數(shù)據(jù)庫(kù)服務(wù)器的內(nèi)存中的，是由系統(tǒng)管這兩個(gè)表是建在數(shù)據(jù)庫(kù)服務(wù)器的內(nèi)存中的，是由系統(tǒng)管理的邏輯表，而不是真正存儲(chǔ)在數(shù)據(jù)庫(kù)中的物理表。對(duì)理的邏輯表，而不是真正存儲(chǔ)在數(shù)據(jù)庫(kù)

39、中的物理表。對(duì)于這兩個(gè)表，用戶只有讀取的權(quán)限，沒(méi)有修改的權(quán)限。于這兩個(gè)表，用戶只有讀取的權(quán)限，沒(méi)有修改的權(quán)限。當(dāng)觸發(fā)器的工作完成之后，這兩個(gè)表將會(huì)從內(nèi)存中刪除。當(dāng)觸發(fā)器的工作完成之后，這兩個(gè)表將會(huì)從內(nèi)存中刪除。qinsertedinserted表里存放的是新插入的記錄：對(duì)于表里存放的是新插入的記錄：對(duì)于INSERTINSERT操作來(lái)說(shuō)，操作來(lái)說(shuō)，INSERTINSERT觸發(fā)器執(zhí)行，新的記錄插入到觸發(fā)器表和觸發(fā)器執(zhí)行，新的記錄插入到觸發(fā)器表和insertedinserted表表中。在進(jìn)行中。在進(jìn)行INSERTINSERT和和UPDATEUPDATE觸發(fā)器時(shí)，觸發(fā)器時(shí)，insertedinsert

40、ed表中有數(shù)表中有數(shù)據(jù)，而在據(jù)，而在DELETEDELETE觸發(fā)器中觸發(fā)器中insertedinserted表是空的。表是空的。qDeletedDeleted表里存放的是已從表中刪除的記錄：對(duì)于表里存放的是已從表中刪除的記錄：對(duì)于DELETEDELETE操操作來(lái)說(shuō)，作來(lái)說(shuō)，DELETEDELETE觸發(fā)器執(zhí)行，被刪除的舊記錄存放到觸發(fā)器執(zhí)行，被刪除的舊記錄存放到DeletedDeleted表中。表中。qUPDATEUPDATE操作等價(jià)于插入一條新記錄，同時(shí)刪除舊記錄。對(duì)于操作等價(jià)于插入一條新記錄，同時(shí)刪除舊記錄。對(duì)于UPDATEUPDATE操作來(lái)說(shuō)，操作來(lái)說(shuō)，UPDATEUPDATE觸發(fā)器執(zhí)行，

41、表中原記錄被移動(dòng)到觸發(fā)器執(zhí)行，表中原記錄被移動(dòng)到DeletedDeleted表中（更新完后即被刪除），修改后的記錄插入到表中（更新完后即被刪除），修改后的記錄插入到InsertedInserted表中。表中。qinsertedinserted和和deleteddeleted表的結(jié)構(gòu)與觸發(fā)器所在數(shù)據(jù)表的結(jié)構(gòu)是表的結(jié)構(gòu)與觸發(fā)器所在數(shù)據(jù)表的結(jié)構(gòu)是完全一致的。它們的操作和普通表的操作也一致。例如，若完全一致的。它們的操作和普通表的操作也一致。例如，若要檢索要檢索 deleted deleted 表中的所有值，則使用語(yǔ)句：表中的所有值，則使用語(yǔ)句： SELECT SELECT * * FROM dele

42、ted FROM deletedn3. 3. 創(chuàng)建創(chuàng)建DMLDML觸發(fā)器的語(yǔ)法規(guī)則觸發(fā)器的語(yǔ)法規(guī)則q創(chuàng)建創(chuàng)建DMLDML觸發(fā)器的語(yǔ)法規(guī)則如下：觸發(fā)器的語(yǔ)法規(guī)則如下：CREATE TRIGGER CREATE TRIGGER 觸發(fā)器名稱(chēng)觸發(fā)器名稱(chēng)ON table | view ON table | view FOR |AFTER | INSTEAD OF FOR |AFTER | INSTEAD OF INSERT , UPDATE , INSERT , UPDATE , DELETE DELETE AS AS SQLSQL語(yǔ)句語(yǔ)句,nnu其中：其中： AFTERAFTER 指定觸發(fā)器只有在觸發(fā)

43、指定觸發(fā)器只有在觸發(fā) SQL SQL 語(yǔ)句中指定的所有語(yǔ)句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級(jí)聯(lián)操操作都已成功執(zhí)行后才激發(fā)。所有的引用級(jí)聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。如果僅指定發(fā)器。如果僅指定 FOR FOR 關(guān)鍵字，則關(guān)鍵字，則 AFTER AFTER 是默是默認(rèn)設(shè)置。認(rèn)設(shè)置。 INSTEAD OFINSTEAD OF 指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā) SQL SQL 語(yǔ)句，從語(yǔ)句，從而替代觸發(fā)語(yǔ)句的操作。而替代觸發(fā)語(yǔ)句的操作?！纠?使用使用SSMSSSMS在在studentstuden

44、t表上創(chuàng)建一個(gè)觸發(fā)器表上創(chuàng)建一個(gè)觸發(fā)器trigoptrigop，其，其功能是在用戶插入、修改或刪除該表中行中輸出所有的行。功能是在用戶插入、修改或刪除該表中行中輸出所有的行。 CREATE TRIGGER CREATE TRIGGER tr1tr1ON student AFTER INSERT,DELETE,UPDATEON student AFTER INSERT,DELETE,UPDATEAS AS BEGINBEGINSET NOCOUNT ONSET NOCOUNT ONSELECT SELECT * * FROM student FROM studentENDENDGOGO在觸發(fā)器在

45、觸發(fā)器tr1tr1創(chuàng)建創(chuàng)建完畢，當(dāng)對(duì)完畢，當(dāng)對(duì)studentstudent表進(jìn)行記錄插入、修改表進(jìn)行記錄插入、修改或刪除操作時(shí)，觸發(fā)器或刪除操作時(shí)，觸發(fā)器tr1tr1都會(huì)都會(huì)被自動(dòng)執(zhí)行。被自動(dòng)執(zhí)行。例如，執(zhí)行以下語(yǔ)句：例如，執(zhí)行以下語(yǔ)句：INSERT student INSERT student VALUES(1, VALUES(1,劉明劉明,男男,1991-12-12,1035),1991-12-12,1035)當(dāng)當(dāng)向向studentstudent表中插入一個(gè)記錄時(shí)自動(dòng)執(zhí)行觸發(fā)器表中插入一個(gè)記錄時(shí)自動(dòng)執(zhí)行觸發(fā)器tr1tr1輸出輸出其其所有記錄，輸出結(jié)果如圖所示，從中看到新記錄已經(jīng)插入到所有記

46、錄，輸出結(jié)果如圖所示，從中看到新記錄已經(jīng)插入到studentstudent表中了。表中了。 例：創(chuàng)建觸發(fā)器例：創(chuàng)建觸發(fā)器stu_deletestu_delete，實(shí)現(xiàn)如下功能：當(dāng)按照學(xué)號(hào)刪，實(shí)現(xiàn)如下功能：當(dāng)按照學(xué)號(hào)刪除除studentstudent表中的某學(xué)生記錄后，對(duì)應(yīng)的該學(xué)生在表中的某學(xué)生記錄后，對(duì)應(yīng)的該學(xué)生在scorescore表表中的記錄也被自動(dòng)刪除中的記錄也被自動(dòng)刪除。T-SQLT-SQL語(yǔ)句為：語(yǔ)句為：CREATE TRIGGER CREATE TRIGGER stu_Deletestu_Delete ON student ON studentFOR DELETEFOR DELET

47、EASASDELETE FROM score WHERE DELETE FROM score WHERE snosno=(SELECT =(SELECT snosno FROM FROM deleted)deleted)在在studentstudent表中執(zhí)行數(shù)據(jù)刪除語(yǔ)句：表中執(zhí)行數(shù)據(jù)刪除語(yǔ)句：DELETE FROM student WHERE DELETE FROM student WHERE snosno=20070102=20070102 執(zhí)行上述語(yǔ)句設(shè)定的觸發(fā)器被觸發(fā)，執(zhí)行上述語(yǔ)句設(shè)定的觸發(fā)器被觸發(fā)，scorescore表中的相應(yīng)數(shù)據(jù)表中的相應(yīng)數(shù)據(jù)被自動(dòng)刪除?？赏ㄟ^(guò)查詢被自動(dòng)刪除?？赏?/p>

48、過(guò)查詢studentstudent表和表和scorescore表刪除前后的該表刪除前后的該生記錄進(jìn)行觸發(fā)器的驗(yàn)證。生記錄進(jìn)行觸發(fā)器的驗(yàn)證。n上面例題的執(zhí)行過(guò)程如下：上面例題的執(zhí)行過(guò)程如下：（1 1）當(dāng)系統(tǒng)接收到一個(gè)要執(zhí)行）當(dāng)系統(tǒng)接收到一個(gè)要執(zhí)行studentstudent表刪除操作表刪除操作的的T-SQLT-SQL語(yǔ)句時(shí)，系統(tǒng)將要?jiǎng)h除的記錄存放在刪除表語(yǔ)句時(shí)，系統(tǒng)將要?jiǎng)h除的記錄存放在刪除表DeletedDeleted中；中；（2 2）把數(shù)據(jù)表）把數(shù)據(jù)表studentstudent中的相應(yīng)記錄刪除；中的相應(yīng)記錄刪除；（3 3）刪除操作激活了事先編制的）刪除操作激活了事先編制的AFTERAFTER

49、觸發(fā)器，系觸發(fā)器，系統(tǒng)執(zhí)行統(tǒng)執(zhí)行AFTERAFTER觸發(fā)器中觸發(fā)器中ASAS后的后的T-SQLT-SQL語(yǔ)句；語(yǔ)句；（4 4）觸發(fā)器執(zhí)行完畢之后，刪除內(nèi)存中的）觸發(fā)器執(zhí)行完畢之后，刪除內(nèi)存中的DeletedDeleted表，結(jié)束整個(gè)操作。若觸發(fā)器語(yǔ)句執(zhí)行失敗，則整表，結(jié)束整個(gè)操作。若觸發(fā)器語(yǔ)句執(zhí)行失敗，則整個(gè)過(guò)程回滾，恢復(fù)到初始狀態(tài)。個(gè)過(guò)程回滾，恢復(fù)到初始狀態(tài)?！纠?在數(shù)據(jù)庫(kù)在數(shù)據(jù)庫(kù)testtest中建立一個(gè)表中建立一個(gè)表table10table10，在該表上創(chuàng)，在該表上創(chuàng)建一個(gè)觸發(fā)器建一個(gè)觸發(fā)器trigtesttrigtest。在。在table10table10表中插入、修改和刪除記表中

50、插入、修改和刪除記錄時(shí)，自動(dòng)顯示表中的所有記錄。并用相關(guān)數(shù)據(jù)進(jìn)行測(cè)試。錄時(shí)，自動(dòng)顯示表中的所有記錄。并用相關(guān)數(shù)據(jù)進(jìn)行測(cè)試。解：創(chuàng)建表和觸發(fā)器的語(yǔ)句如下：解：創(chuàng)建表和觸發(fā)器的語(yǔ)句如下：USE testUSE testGOGOCREATE TABLE table10CREATE TABLE table10 -創(chuàng)建表創(chuàng)建表table10table10( (c1 c1 intint, ,c2 char(30)c2 char(30)GOGOCREATE TRIGGER CREATE TRIGGER trigtesttrigtest -創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器trigtesttrigtestON table1

51、0 AFTER INSERT,UPDATE,DELETE ON table10 AFTER INSERT,UPDATE,DELETE ASASSELECT SELECT * * FROM table10 FROM table10GOGO在執(zhí)行下面的語(yǔ)句時(shí)：在執(zhí)行下面的語(yǔ)句時(shí)：USE testUSE testINSERT Table10 VALUES(1,Name1)INSERT Table10 VALUES(1,Name1)GOGO結(jié)果會(huì)顯示出結(jié)果會(huì)顯示出table10table10表中的行如圖所示：表中的行如圖所示： 在執(zhí)行下面的語(yǔ)句時(shí)：在執(zhí)行下面的語(yǔ)句時(shí)：USE testUSE testU

52、PDATE Table10 SET c2=Name2 WHERE c1=1UPDATE Table10 SET c2=Name2 WHERE c1=1GOGO結(jié)果會(huì)顯示出結(jié)果會(huì)顯示出table10table10表中的記錄行如圖所示：表中的記錄行如圖所示：【例例】 下面一段下面一段T-SQLT-SQL語(yǔ)句說(shuō)明語(yǔ)句說(shuō)明insertedinserted表和表和deleteddeleted表表的作用。的作用。CREATE TRIGGER CREATE TRIGGER trigtesttrigtest ON table10 AFTER INSERT,UPDATE,DELETE ON table10 AF

53、TER INSERT,UPDATE,DELETEASAS PRINT inserted PRINT inserted表表: SELECT SELECT * * FROM inserted FROM inserted PRINT deleted PRINT deleted表表: SELECT SELECT * * FROM deleted FROM deletedGOGO如果此時(shí)執(zhí)行下面的如果此時(shí)執(zhí)行下面的INSERTINSERT語(yǔ)句：語(yǔ)句：INSERT table10 VALUES(2,Name3INSERT table10 VALUES(2,Name3)其執(zhí)行結(jié)果如圖所示：其執(zhí)行結(jié)果如圖所示

54、：如果此時(shí)接著執(zhí)行下面的如果此時(shí)接著執(zhí)行下面的UPDATEUPDATE語(yǔ)句：語(yǔ)句：UPDATE table10 SET c2=Name4 WHERE c1=2UPDATE table10 SET c2=Name4 WHERE c1=2其執(zhí)行結(jié)果如圖所示其執(zhí)行結(jié)果如圖所示: : 如果此時(shí)接著執(zhí)行下面的如果此時(shí)接著執(zhí)行下面的DELETEDELETE語(yǔ)句：語(yǔ)句：DELETE table10 WHERE c1=2DELETE table10 WHERE c1=2其執(zhí)行結(jié)果如如圖所示：其執(zhí)行結(jié)果如如圖所示：【例例】 建立一個(gè)觸發(fā)器建立一個(gè)觸發(fā)器tntn，當(dāng)向，當(dāng)向studentstudent表中插入數(shù)據(jù)

55、時(shí)，表中插入數(shù)據(jù)時(shí)，如果出現(xiàn)姓名重復(fù)的情況，則回滾該事務(wù)。如果出現(xiàn)姓名重復(fù)的情況，則回滾該事務(wù)。解：創(chuàng)建觸發(fā)器解：創(chuàng)建觸發(fā)器trignametrigname的程序如下：的程序如下：CREATE TRIGGER tnCREATE TRIGGER tn ON student AFTER INSERT ON student AFTER INSERT ASASBEGINBEGINDECLARE name char(10)DECLARE name char(10)SELECT name=inserted.SELECT name=inserted.姓名姓名 FROM insertedFROM insert

56、edIF EXISTS(SELECT IF EXISTS(SELECT 姓名姓名 FROM student FROM student WHERE WHERE 姓名姓名=name)=name)BEGINBEGINRAISERROR(RAISERROR(姓名重復(fù)姓名重復(fù), ,不能插入不能插入,16,1),16,1)ROLLBACKROLLBACKENDENDENDEND執(zhí)行以下程序：執(zhí)行以下程序：INSERT INTO student(INSERT INTO student(學(xué)號(hào)學(xué)號(hào), ,姓名姓名, ,性別性別) ) VALUES(102, VALUES(102,王麗王麗,女女)出現(xiàn)如圖所示的消息

57、，提示插入的記錄出錯(cuò)。出現(xiàn)如圖所示的消息，提示插入的記錄出錯(cuò)。 再打開(kāi)再打開(kāi)studentstudent表，從中看到，由于進(jìn)行了事務(wù)回滾，表，從中看到，由于進(jìn)行了事務(wù)回滾，所以并不會(huì)真正向所以并不會(huì)真正向studentstudent表中插入學(xué)號(hào)為表中插入學(xué)號(hào)為102102的新記錄。的新記錄。 【例例】 建立一個(gè)觸發(fā)器建立一個(gè)觸發(fā)器tsts，當(dāng)向，當(dāng)向studentstudent表中插入數(shù)據(jù)時(shí)，表中插入數(shù)據(jù)時(shí)，如果出現(xiàn)性別不正確的情況，不回滾該事務(wù)，只提示錯(cuò)誤消息。如果出現(xiàn)性別不正確的情況，不回滾該事務(wù)，只提示錯(cuò)誤消息。解：創(chuàng)建觸發(fā)器解：創(chuàng)建觸發(fā)器trignsextrignsex的程序如下：的程

58、序如下：CREATE TRIGGER CREATE TRIGGER tstsON student AFTER INSERTON student AFTER INSERTASASDECLARE s1 char(1)DECLARE s1 char(1)SELECT s1=SELECT s1=性別性別 FROM INSERTEDFROM INSERTEDIF s1IF s1男男 OR s1 OR s1女女 RAISERROR(RAISERROR(性別只能取男或女性別只能取男或女,16,1),16,1)GOGO當(dāng)執(zhí)行以下程序：當(dāng)執(zhí)行以下程序：INSERT student VALUES(2,INSERT

59、 student VALUES(2,許濤許濤,M,1992-10-16,1035),M,1992-10-16,1035)出現(xiàn)如圖所示的消息，提示插入的記錄出錯(cuò)。出現(xiàn)如圖所示的消息，提示插入的記錄出錯(cuò)。 再打開(kāi)再打開(kāi)studentstudent表，從中看到，由于沒(méi)有進(jìn)行事務(wù)回滾，表，從中看到，由于沒(méi)有進(jìn)行事務(wù)回滾，盡管要插入的記錄不正確，但仍然插入到盡管要插入的記錄不正確，但仍然插入到studentstudent表中了。表中了。 【例例】 建立一個(gè)修改觸發(fā)器建立一個(gè)修改觸發(fā)器trignotrigno，該觸發(fā)器防止用戶，該觸發(fā)器防止用戶修改表修改表studentstudent的學(xué)號(hào)。的學(xué)號(hào)。解：創(chuàng)

60、建觸發(fā)器解：創(chuàng)建觸發(fā)器trignnotrignno的程序如下：的程序如下：CREATE TRIGGER CREATE TRIGGER trignotrigno ON student ON student AFTER UPDATE AFTER UPDATEASAS IF UPDATE( IF UPDATE(學(xué)號(hào)學(xué)號(hào)) )BEGINBEGIN RAISERROR( RAISERROR(不能修改學(xué)號(hào)不能修改學(xué)號(hào),16,2),16,2) ROLLBACK ROLLBACKENDENDGOGO當(dāng)執(zhí)行以下程序：當(dāng)執(zhí)行以下程序：UPDATE student SET UPDATE student SET 學(xué)號(hào)