第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ǔ)過(guò)程程序，稱為存儲(chǔ)過(guò)程。運(yùn)。運(yùn)用用它可它可以創(chuàng)建健壯、安全且具有良好以創(chuàng)建健壯、安全且具有良好 性能的數(shù)據(jù)庫(kù)，可以為用

2、戶實(shí)性能的數(shù)據(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或者或

3、者INSTEAD OFINSTEAD 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ā)器和游

4、標(biāo)的基本概念與特點(diǎn) n掌握存儲(chǔ)過(guò)程的基本類(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è)名稱來(lái)標(biāo)識(shí)存儲(chǔ)過(guò)程作為一個(gè)單元進(jìn)行處理并以一個(gè)名稱來(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ò)指定存

5、儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(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ò)程的名稱編寫(xiě)為一條存儲(chǔ)過(guò)程，這樣客戶端只需要提交存儲(chǔ)過(guò)程的名稱 和參數(shù)，即可實(shí)現(xiàn)相應(yīng)功能，節(jié)省了網(wǎng)絡(luò)流量，提高了執(zhí)行的和參數(shù)，即可實(shí)現(xiàn)相應(yīng)功能，節(jié)省了網(wǎng)絡(luò)流

6、量，提高了執(zhí)行的 效率。此外，由于所有的操作都在服務(wù)器端完成，避免了在客效率。此外，由于所有的操作都在服務(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ò)程高速緩存中，

7、當(dāng)使用時(shí)，服務(wù)器不必再重新分析和編 譯它們。因此，當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)（如對(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ù)操作，并且：使

8、用存儲(chǔ)過(guò)程可以完成所有數(shù)據(jù)庫(kù)操作，并且 可授予沒(méi)有直接執(zhí)行存儲(chǔ)過(guò)程中語(yǔ)句的權(quán)限的用戶，也可執(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ù)

9、庫(kù)開(kāi)發(fā)人員的工作量。 n5 5. . 可自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)可自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)：存儲(chǔ)過(guò)程可以在系統(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ò)程用戶自定義

10、存儲(chǔ)過(guò)程 用戶自定義存儲(chǔ)過(guò)程在用戶數(shù)據(jù)庫(kù)中創(chuàng)建，通常與數(shù)據(jù)用戶自定義存儲(chǔ)過(guò)程在用戶數(shù)據(jù)庫(kù)中創(chuàng)建，通常與數(shù)據(jù) 庫(kù)對(duì)象進(jìn)行交互，用于完成特定數(shù)據(jù)庫(kù)操作任務(wù)，可以庫(kù)對(duì)象進(jìn)行交互，用于完成特定數(shù)據(jù)庫(kù)操作任務(wù)，可以 接受和返回用戶提供的參數(shù)，名稱不能以接受和返回用戶提供的參數(shù)，名稱不能以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ǔ) 法格式

11、為法格式為： CREATE PROCEDURE procedure_name parameter data_type = defaultOUTPUT ,n AS sql_statement n 1 10.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ù)名稱。定參數(shù)名稱。 qdata_typeda

12、ta_type：參數(shù)的數(shù)據(jù)類(lèi)型。參數(shù)的數(shù)據(jù)類(lèi)型。 qD Defaultefault：參數(shù)的默認(rèn)值。參數(shù)的默認(rèn)值。 qOUTPUTOUTPUT：表明參數(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 studen

13、t.student.學(xué)號(hào)學(xué)號(hào), ,姓名姓名, ,課程名課程名, ,分?jǐn)?shù)分?jǐn)?shù) FROM FROM student,course,scorestudent,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_stat

14、us = procedure_name parameter = value | variable OUTPUT | DEFAULT , n 10.1.2 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ò)程的名稱調(diào)用的存儲(chǔ)過(guò)程的名稱 lparameter parameter 過(guò)程參數(shù)過(guò)程參數(shù) lValue Value 過(guò)程參數(shù)的值過(guò)程參數(shù)的值 lvariable variable 用來(lái)保存參數(shù)或

15、返回參數(shù)的變量用來(lái)保存參數(shù)或返回參數(shù)的變量 lOUTPUTOUTPUT 指定存儲(chǔ)過(guò)程必須返回的一個(gè)參數(shù)指定存儲(chǔ)過(guò)程必須返回的一個(gè)參數(shù) lDEFAULT DEFAULT 默認(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 proc

16、edure maxderee create procedure maxderee as as begin begin select max(degree) as select 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 C

17、REATE 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è)計(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.

18、,s.姓名姓名,c.,c.課程名課程名,sc.,sc.分?jǐn)?shù)分?jǐn)?shù) FROM student FROM 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) GOGO 采用方式一

19、執(zhí)行存儲(chǔ)過(guò)程采用方式一執(zhí)行存儲(chǔ)過(guò)程maxnomaxno的程序如下：的程序如下： EXEC EXEC maxnomaxno 103 103 采用方式二執(zhí)行存儲(chǔ)過(guò)程采用方式二執(zhí)行存儲(chǔ)過(guò)程maxnomaxno的程序如下：的程序如下： EXEC EXEC maxnomaxno no=103 no=103 n2. 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ò)程

20、名存儲(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)的實(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

21、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 FROM 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

22、 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é)果如圖所示： 當(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)行

23、說(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-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

24、 char(8) char(8) OUTPUTOUTPUT, , st_avgst_avg float float OUTPUTOUTPUT ) AS) AS SELECT SELECT st_namest_name=student.=student.姓名姓名,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)

25、,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é)生姓名和平均分： DECLARE DECLARE st_namest_name char(10) char(10) DECLARE DECLARE st_avgst_avg float float EXEC average 105,st_name EXEC average 105,st_name OUTPUTOUTPUT, , st_avgst_avg OUTP

26、UTOUTPUT SELECT SELECT 姓名姓名=st_namest_name,平均分平均分=st_avgst_avg n4. 4. 存儲(chǔ)過(guò)程的返回值存儲(chǔ)過(guò)程的返回值 q存儲(chǔ)過(guò)程在執(zhí)行后都會(huì)返回一個(gè)整型值（稱為存儲(chǔ)過(guò)程在執(zhí)行后都會(huì)返回一個(gè)整型值（稱為“返返 回代碼回代碼”），指示存儲(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也可以使用也可

27、以使用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_rettest_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 RETUR

28、N -1000 RETURN -1000 執(zhí)行以上存儲(chǔ)過(guò)程：執(zhí)行以上存儲(chǔ)過(guò)程： declare r intdeclare r int exec r=ret 1exec r=ret 1 print rprint r 運(yùn)行結(jié)果：運(yùn)行結(jié)果： 10.1.4 10.1.4 查看、修改和刪除存儲(chǔ)過(guò)程查看、修改和刪除存儲(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管理

29、控制器刪除存儲(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)的程序如下： USE schoolUSE school GOGO EXEC sp_helptext stud_degreeEXEC sp_helptext stud_degree 10.2 10.2 觸發(fā)器觸發(fā)器 n觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程。觸發(fā)器可包含復(fù)觸發(fā)器是一種特殊類(lèi)型的存儲(chǔ)過(guò)程

30、。觸發(fā)器可包含復(fù) 雜的雜的T-SQLT-SQL語(yǔ)句。觸發(fā)器不能通過(guò)名稱被直接調(diào)用，語(yǔ)句。觸發(fā)器不能通過(guò)名稱被直接調(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ù)規(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ù)

31、的一致性可使用觸發(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ù)，因而不能被顯示調(diào)用。它作為語(yǔ)句的執(zhí)數(shù)和輸出參數(shù)，因而不能被顯示調(diào)用。它作為語(yǔ)句的執(zhí) 行結(jié)果自動(dòng)引發(fā)，而存儲(chǔ)過(guò)程則是通過(guò)存儲(chǔ)過(guò)程名稱而行結(jié)果自動(dòng)引發(fā)，而存儲(chǔ)過(guò)程則是通過(guò)存儲(chǔ)過(guò)程名稱而 被直接調(diào)用。被直接調(diào)用。 q觸發(fā)器與表緊密相連，當(dāng)用戶對(duì)表進(jìn)行諸如觸

32、發(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ū)崿F(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ā)器是在CHEC

33、KCHECK之后執(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ù)存系統(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ù)

34、庫(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)數(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 2

35、008支持兩種類(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和和DELETE 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ì)待

36、。如果檢測(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)頭的開(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ù)操作。

37、 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ò)誤，可以用ROLLBACK ROLLBACK TRANSACTIONTRANSACTION語(yǔ)句來(lái)回滾本次的操作。對(duì)同一個(gè)表達(dá)操語(yǔ)句來(lái)回滾本次的操作。對(duì)同一個(gè)表達(dá)操 作，可定義多個(gè)作，可定義多個(gè)AFTE

38、RAFTER觸發(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)取代原本的操 作，在記錄變更之前被觸發(fā)的。作，在記錄變更之前被觸發(fā)的。 n2. 2. 觸發(fā)器中的邏輯（虛擬）表觸發(fā)器中的邏輯（虛擬）表 q當(dāng)表被修改，無(wú)論是插入、修改還是

39、刪除，被操作的記當(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ù)中的物理表。對(duì) 于這兩個(gè)表，用戶只有讀取的權(quán)限，沒(méi)有修改的權(quán)限。于這兩個(gè)表，用戶只有讀取的權(quán)限，沒(méi)有修改的權(quán)限。 當(dāng)觸發(fā)器的工作完成之后，這兩個(gè)表將會(huì)從

40、內(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í)，insertedinserted表中有數(shù)表中有數(shù) 據(jù)，而在據(jù)，而在DELETEDELETE觸發(fā)器中觸發(fā)器中insertedinserted表是空的。表是空的。 qDele

41、tedDeleted表里存放的是已從表中刪除的記錄：對(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í)行，表中原記錄被移動(dòng)到觸發(fā)器執(zhí)行，表中原記錄被移動(dòng)到 DeletedDeleted表中（更新完后即被刪除），修改后的記錄插入到表中（更

42、新完后即被刪除），修改后的記錄插入到 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 deleted FROM deleted n3. 3. 創(chuàng)建創(chuàng)建DMLDML觸發(fā)器的語(yǔ)法規(guī)則觸發(fā)器的語(yǔ)法規(guī)則 q創(chuàng)建創(chuàng)建DMLD

43、ML觸發(fā)器的語(yǔ)法規(guī)則如下：觸發(fā)器的語(yǔ)法規(guī)則如下： CREATE TRIGGER CREATE TRIGGER 觸發(fā)器名稱觸發(fā)器名稱 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ǔ)句,nn u其中：其中： AFTERAFTER 指定觸發(fā)器只有在觸發(fā)指定觸發(fā)器只有在觸發(fā) SQL SQL 語(yǔ)句中指定的所有語(yǔ)句中指定的所有 操作都已成功執(zhí)行后才激發(fā)。所有的引用級(jí)

44、聯(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在在studentstudent表上創(chuàng)建一個(gè)觸發(fā)器表上創(chuàng)建一個(gè)觸發(fā)器trigoptrigop，其，其 功能是在用戶插入、修改或刪

45、除該表中行中輸出所有的行。功能是在用戶插入、修改或刪除該表中行中輸出所有的行。 CREATE TRIGGER CREATE TRIGGER tr1tr1 ON student AFTER INSERT,DELETE,UPDATEON student AFTER INSERT,DELETE,UPDATE AS AS BEGINBEGIN SET NOCOUNT ONSET NOCOUNT ON SELECT SELECT * * FROM student FROM student ENDEND GOGO 在觸發(fā)器在觸發(fā)器tr1tr1創(chuàng)建創(chuàng)建完畢，當(dāng)對(duì)完畢，當(dāng)對(duì)studentstudent表進(jìn)行記

46、錄插入、修改表進(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)插入到所有記錄，輸出結(jié)果如圖所示，從中看到新記錄已經(jīng)插入到 studentstude

47、nt表中了。表中了。 例：創(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 student FOR DELETEFOR DELETE ASAS DELETE FROM score WHERE

48、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)刪除。可通過(guò)查詢studentstudent表和表和score

49、score表刪除前后的該表刪除前后的該 生記錄進(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觸發(fā)器，系觸發(fā)器，系 統(tǒng)執(zhí)行統(tǒng)執(zhí)行AF

50、TERAFTER觸發(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表中插入、修改和刪除記表中插入、修改和刪除記 錄時(shí)，

51、自動(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 test GOGO CREATE TABLE table10CREATE TABLE table10 -創(chuàng)建表創(chuàng)建表table10table10 ( (c1 c1 intint, , c2 char(30)c2 char(30) GOGO CREATE TRIGGER CREATE TRIGGER trigtesttrigtest -創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器trigtesttrigtest ON table10 A

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

53、test UPDATE Table10 SET c2=Name2 WHERE c1=1UPDATE Table10 SET c2=Name2 WHERE c1=1 GOGO 結(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

54、 table10 AFTER INSERT,UPDATE,DELETE ASAS PRINT inserted PRINT inserted表表: SELECT SELECT * * FROM inserted FROM inserted PRINT deleted PRINT deleted表表: SELECT SELECT * * FROM deleted FROM deleted GOGO 如果此時(shí)執(zhí)行下面的如果此時(shí)執(zhí)行下面的INSERTINSERT語(yǔ)句：語(yǔ)句： INSERT table10 VALUES(2,Name3INSERT table10 VALUES(2,Name3) 其執(zhí)行

55、結(jié)果如圖所示：其執(zhí)行結(jié)果如圖所示： 如果此時(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)向，

56、當(dāng)向studentstudent表中插入數(shù)據(jù)時(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 ASAS BEGINBEGIN DECLARE name char(10)DECLARE name char(10) SELECT name=inserted.SELECT name=inserted.姓

57、名姓名 FROM insertedFROM inserted IF EXISTS(SELECT IF EXISTS(SELECT 姓名姓名 FROM student FROM student WHERE WHERE 姓名姓名=name)=name) BEGINBEGIN RAISERROR(RAISERROR(姓名重復(fù)姓名重復(fù), ,不能插入不能插入,16,1),16,1) ROLLBACKROLLBACK ENDEND ENDEND 執(zhí)行以下程序：執(zhí)行以下程序： INSERT INTO student(INSERT INTO student(學(xué)號(hào)學(xué)號(hào), ,姓名姓名, ,性別性別) ) VALU

58、ES(102, VALUES(102,王麗王麗,女女) 出現(xiàn)如圖所示的消息，提示插入的記錄出錯(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ò)誤消息

59、。 解：創(chuàng)建觸發(fā)器解：創(chuàng)建觸發(fā)器trignsextrignsex的程序如下：的程序如下： CREATE TRIGGER CREATE TRIGGER tsts ON student AFTER INSERTON student AFTER INSERT ASAS DECLARE s1 char(1)DECLARE s1 char(1) SELECT s1=SELECT s1=性別性別 FROM INSERTEDFROM INSERTED IF s1IF s1男男 OR s1 OR s1女女 RAISERROR(RAISERROR(性別只能取男或女性別只能取男或女,16,1),16,1) GOG

60、O 當(dāng)執(zhí)行以下程序：當(dāng)執(zhí)行以下程序： INSERT student VALUES(2,INSERT 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ā)器trignotrig