《SQL Server數(shù)據(jù)庫(kù)系統(tǒng)基礎(chǔ)》課件009

第9章存儲(chǔ)過程和觸發(fā)器9.1存儲(chǔ)過程概述9.2創(chuàng)建存儲(chǔ)過程9.3執(zhí)行存儲(chǔ)過程9.4查看和修改存儲(chǔ)過程9.5重命名和刪除存儲(chǔ)過程9.6觸發(fā)器9.7創(chuàng)建觸發(fā)器9.8查看、修改和刪除觸發(fā)器9.9觸發(fā)器的應(yīng)用9.10實(shí)戰(zhàn)訓(xùn)練小結(jié)

思考題

在前面的章節(jié)中，我們已經(jīng)學(xué)習(xí)了數(shù)據(jù)庫(kù)的編程，但是，如何來(lái)保存和方便地運(yùn)行用戶存儲(chǔ)過程呢？這是我們進(jìn)一步要關(guān)心的問題。本章任務(wù)：學(xué)會(huì)查找和使用系統(tǒng)存儲(chǔ)過程，編寫和運(yùn)行用戶存儲(chǔ)過程，學(xué)會(huì)觸發(fā)器的設(shè)計(jì)和應(yīng)用。9.1存儲(chǔ)過程概述9.1.1存儲(chǔ)過程的概念

SQLServer的存儲(chǔ)過程類似于編程語(yǔ)言中的過程。使用Transact-SQL語(yǔ)言編程可以把某些需要多次調(diào)用以實(shí)現(xiàn)某個(gè)特定任務(wù)的代碼段編寫成一個(gè)程序塊，將其保存在數(shù)據(jù)庫(kù)中，在需要使用時(shí)由SQLServer服務(wù)器通過它們的名稱來(lái)調(diào)用，這樣的程序塊就叫做存儲(chǔ)過程。存儲(chǔ)過程在創(chuàng)建時(shí)經(jīng)過語(yǔ)法檢查被編譯和優(yōu)化，調(diào)用一次以后，相關(guān)信息就保存在內(nèi)存中，下次調(diào)用時(shí)可以直接執(zhí)行。存儲(chǔ)過程有以下特點(diǎn)：

(1)存儲(chǔ)過程可以包含一條或多條Transact-SQL語(yǔ)句。(2)存儲(chǔ)過程可以接受輸入?yún)?shù)并返回輸出值。

(3)一個(gè)存儲(chǔ)過程可以調(diào)用另一個(gè)存儲(chǔ)過程。

(4)存儲(chǔ)過程會(huì)返回執(zhí)行情況的狀態(tài)代碼給調(diào)用它的程序。9.1.2存儲(chǔ)過程的優(yōu)點(diǎn)使用存儲(chǔ)過程有很多優(yōu)點(diǎn)，具體如下：

(1)執(zhí)行速度快。存儲(chǔ)過程在創(chuàng)建時(shí)已經(jīng)通過語(yǔ)法檢查和編譯，調(diào)用時(shí)可直接執(zhí)行，程序的運(yùn)行效率高，其執(zhí)行速度要比標(biāo)準(zhǔn)SQL語(yǔ)句快得多。當(dāng)含有大量SQL語(yǔ)句的批處理需要重復(fù)多次執(zhí)行時(shí)，定義為存儲(chǔ)過程可大大提高運(yùn)行效率。

(2)有利于模塊化程序設(shè)計(jì)。存儲(chǔ)過程創(chuàng)建后可多次調(diào)用?？筛鶕?jù)不同的功能模式設(shè)計(jì)不同的存儲(chǔ)過程以供調(diào)用。(3)便于程序的維護(hù)和管理。當(dāng)用戶對(duì)數(shù)據(jù)庫(kù)的使用功能改變時(shí)，只需對(duì)相應(yīng)的存儲(chǔ)過程進(jìn)行修改而不用修改應(yīng)用程序。

(4)減少網(wǎng)絡(luò)通信量。存儲(chǔ)過程可包含大量對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作的SQL語(yǔ)句，它的存儲(chǔ)執(zhí)行都在SQLServer服務(wù)器(數(shù)據(jù)庫(kù))端，網(wǎng)絡(luò)用戶使用時(shí)只需發(fā)送一個(gè)調(diào)用語(yǔ)句就可以實(shí)現(xiàn)，大大減少了網(wǎng)絡(luò)上SQL語(yǔ)句的傳輸。

(5)保證系統(tǒng)的安全性。可以在存儲(chǔ)過程中設(shè)置用戶對(duì)數(shù)據(jù)的訪問權(quán)限，只允許用戶調(diào)用存儲(chǔ)過程而不允許直接對(duì)數(shù)據(jù)進(jìn)行訪問，以充分發(fā)揮安全機(jī)制的作用。

(6)具有業(yè)務(wù)邏輯的保密性。對(duì)存儲(chǔ)過程中代表業(yè)務(wù)邏輯的程序語(yǔ)句可以加密，存儲(chǔ)過程一旦加密，即使是系統(tǒng)管理員也難以解密。9.1.3存儲(chǔ)過程的分類

SQLServer中的存儲(chǔ)過程可分為兩類，即系統(tǒng)存儲(chǔ)過程和用戶自定義存儲(chǔ)過程。

(1)系統(tǒng)存儲(chǔ)過程：安裝數(shù)據(jù)庫(kù)系統(tǒng)時(shí)由系統(tǒng)自動(dòng)創(chuàng)建，主要存儲(chǔ)在master數(shù)據(jù)庫(kù)中，以sp_或xp_為前綴。系統(tǒng)存儲(chǔ)過程的功能主要是從系統(tǒng)表中獲取信息，通過系統(tǒng)存儲(chǔ)過程，SQLServer中的許多管理性或信息性的活動(dòng)都可以被順利而有效地完成?？梢栽谄渌麛?shù)據(jù)庫(kù)中調(diào)用系統(tǒng)存儲(chǔ)過程，在調(diào)用時(shí)不必在存儲(chǔ)過程名前加上數(shù)據(jù)庫(kù)名。

(2)用戶自定義存儲(chǔ)過程：由用戶創(chuàng)建并完成某一特定功能的存儲(chǔ)過程。下面介紹用戶自定義存儲(chǔ)過程的創(chuàng)建和使用方法。9.2創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程應(yīng)遵守的規(guī)則如下：

(1)作為存儲(chǔ)過程名稱的標(biāo)識(shí)符其長(zhǎng)度最大為128個(gè)字符，且必須唯一。

(2)每個(gè)存儲(chǔ)過程最多可以使用1024個(gè)參數(shù)。

(3)存儲(chǔ)過程的最大容量有一定的限制。

(4)存儲(chǔ)過程支持多達(dá)32層嵌套。

(5)在對(duì)存儲(chǔ)過程命名時(shí)，最好與系統(tǒng)存儲(chǔ)過程名加以區(qū)分。在SQLServer中，可以使用以下三種方法創(chuàng)建存儲(chǔ)過程。

(1)使用SQLServer企業(yè)管理器創(chuàng)建存儲(chǔ)過程。

(2)使用創(chuàng)建存儲(chǔ)過程向?qū)?chuàng)建存儲(chǔ)過程。(3)使用Transact-SQL語(yǔ)句中的CREATEPROCEDURE命令創(chuàng)建存儲(chǔ)過程。默認(rèn)情況下，創(chuàng)建存儲(chǔ)過程的許可權(quán)歸數(shù)據(jù)庫(kù)的所有者，數(shù)據(jù)庫(kù)的所有者可以把許可權(quán)授給其他用戶。當(dāng)創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的以下三個(gè)組成部分：

(1)所有輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。

(2)被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，包括調(diào)用其它存儲(chǔ)過程的語(yǔ)句。

(3)返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。9.2.1使用企業(yè)管理器創(chuàng)建存儲(chǔ)過程使用SQLServer企業(yè)管理器創(chuàng)建存儲(chǔ)過程的步驟如下：

(1)在SQLServer企業(yè)管理器中，選擇指定的服務(wù)器和數(shù)據(jù)庫(kù)，右擊要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫(kù)，在彈出的快捷菜單中依次選擇“新建”→“存儲(chǔ)過程”選項(xiàng)，如圖9-1所示，或者右擊數(shù)據(jù)庫(kù)中的存儲(chǔ)過程圖標(biāo)，從彈出的快捷菜單中選擇“新建存儲(chǔ)過程”選項(xiàng)，如圖9-2所示，均會(huì)出現(xiàn)“新建存儲(chǔ)過程”對(duì)話框，如圖9-3所示。圖9-1選擇新建存儲(chǔ)過程窗口(1)圖9-2選擇新建存儲(chǔ)過程窗口(2)圖9-3“新建存儲(chǔ)過程”對(duì)話框(2)在文本框中可以輸入創(chuàng)建存儲(chǔ)過程的Transact-SQL語(yǔ)句，這里創(chuàng)建一個(gè)名稱為“顯示學(xué)生信息”的存儲(chǔ)過程，輸入的代碼如下：

CREATEPROCEDURE顯示學(xué)生信息ASSELECT班級(jí)表.班級(jí)名稱,學(xué)生表.學(xué)號(hào),學(xué)生表.姓名,學(xué)生表.性別,

學(xué)生表.出生年月

FROM學(xué)生表INNERJOIN班級(jí)表ON班級(jí)表.班級(jí)編號(hào)=學(xué)生表.

班級(jí)編號(hào)存儲(chǔ)過程“顯示學(xué)生信息”完成的功能是：在學(xué)生表中查詢學(xué)生的姓名、性別、出生年月、地址身份證、學(xué)號(hào)和班級(jí)名稱等字段的內(nèi)容。

(3)輸入完畢單擊“檢查語(yǔ)法”按鈕進(jìn)行語(yǔ)法檢查，檢查成功后系統(tǒng)會(huì)彈出如圖9-4所示的提示信息框。圖9-4語(yǔ)法檢查提示信息框(4)在提示信息框中單擊“確定”按鈕，再在存儲(chǔ)過程屬性框中單擊“確定”按鈕保存該存儲(chǔ)過程，并關(guān)閉該對(duì)話框。9.2.2使用Transact-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過程可以使用Transact-SQL語(yǔ)句中的CREATEPROCEDURE命令創(chuàng)建存儲(chǔ)過程。創(chuàng)建存儲(chǔ)過程前，應(yīng)該注意下列事項(xiàng)：

(1)不能將CREATEPROCEDURE語(yǔ)句與其他SQL語(yǔ)句組合到單個(gè)批處理中。

(2)創(chuàng)建存儲(chǔ)過程的權(quán)限默認(rèn)屬于數(shù)據(jù)庫(kù)所有者，該所有者可將此權(quán)限授予其他用戶。

(3)存儲(chǔ)過程是數(shù)據(jù)庫(kù)對(duì)象，其名稱必須遵守標(biāo)識(shí)符規(guī)則。

(4)只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過程。

創(chuàng)建存儲(chǔ)過程的Transact-SQL語(yǔ)句其語(yǔ)法形式如下：

CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n]；

WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}[FORREPLICATION]ASsql_statement[,…n]其中，各參數(shù)的說明如下：●?procedure_name：用于指定所要?jiǎng)?chuàng)建存儲(chǔ)過程的名稱。存儲(chǔ)過程的命名必須符合標(biāo)識(shí)符命名規(guī)則。在一個(gè)數(shù)據(jù)庫(kù)中或者對(duì)其所有者而言，存儲(chǔ)過程的名稱必須唯一?！?@parameter：過程中的參數(shù)。在CREATEPROCEDURE語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。用戶必須在執(zhí)行過程時(shí)提供每個(gè)聲明參數(shù)的值(除非定義了該參數(shù)的默認(rèn)值)。存儲(chǔ)過程最多可以有2100個(gè)參數(shù)。●?data_type：用于指定參數(shù)的數(shù)據(jù)類型。在存儲(chǔ)過程中，所有的數(shù)據(jù)類型(包括text、ntext和image)均可以用作存儲(chǔ)過程的參數(shù)?！?VARYING：用于指定作為輸出OUTPUT參數(shù)支持的結(jié)果集(由存儲(chǔ)過程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化)。該參數(shù)僅適用于游標(biāo)參數(shù)?！?Default：用于指定參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，則不必指定該參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或空值。如果過程對(duì)該參數(shù)使用LIKE關(guān)鍵字，那么默認(rèn)值中可以包含通配符(%、_、[]和[^])?！?OUTPUT：表明該參數(shù)是一個(gè)返回參數(shù)。該選項(xiàng)的值可以返回給EXEC[UTE]。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。text、ntext和image參數(shù)可用作OUTPUT參數(shù)。使用OUTPUT關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符?！?RECOMPILE：表明SQLServer不會(huì)保存該存儲(chǔ)過程的執(zhí)行計(jì)劃，該存儲(chǔ)過程每執(zhí)行一次都要重新編譯?！?ENCRYPTION：表示對(duì)存儲(chǔ)過程文本進(jìn)行加密。在系統(tǒng)表syscomments中的text字段是包含CREATEPROCEDURE語(yǔ)句的存儲(chǔ)過程文本。使用ENCRYPTION關(guān)鍵字無(wú)法通過查看syscomments表來(lái)查看存儲(chǔ)過程的內(nèi)容。●?FORREPLICATION：用于指定該存儲(chǔ)過程只能在數(shù)據(jù)復(fù)制時(shí)使用。本選項(xiàng)不能和WITHRECOMPILE選項(xiàng)一起使用?！?AS：用于指定該存儲(chǔ)過程要執(zhí)行的操作?！?sql_statement：是存儲(chǔ)過程中包含的任意數(shù)目和類型的Transact-SQL語(yǔ)句。

【例9-1】創(chuàng)建一個(gè)存儲(chǔ)過程“學(xué)生成績(jī)信息”，完成的功能是在班組表、學(xué)生表、課程表和成績(jī)表中查詢以下字段：班級(jí)名稱、學(xué)號(hào)、姓名、性別、課程名稱、考試成績(jī)。

下列程序清單如圖9-5上半部分所示，下半部分是選中存儲(chǔ)過程中SELECT語(yǔ)句的運(yùn)行結(jié)果。

--打開college數(shù)據(jù)庫(kù)

USEcollege--創(chuàng)建存儲(chǔ)過程

CREATEPROCEDURE學(xué)生成績(jī)信息

ASSELECT班級(jí)表.班級(jí)名稱,學(xué)生表.學(xué)號(hào),學(xué)生表.姓名,學(xué)生表.性別,

課程表.課程名稱,成績(jī)表.考試成績(jī)

FROM班級(jí)表INNERJOIN

學(xué)生表ON班級(jí)表.班級(jí)編號(hào)=學(xué)生表.班級(jí)編號(hào)INNERJOIN

成績(jī)表ON學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)INNERJOIN

課程表ON成績(jī)表.課程號(hào)=課程表.課程號(hào)

GO圖9-5創(chuàng)建與運(yùn)行存儲(chǔ)過程

【例9-2】創(chuàng)建一個(gè)帶有參數(shù)的存儲(chǔ)過程“學(xué)生個(gè)人成績(jī)”，該存儲(chǔ)過程根據(jù)傳入的學(xué)生編號(hào)，在學(xué)生表中查詢此學(xué)生的成績(jī)信息。程序清單如下：

--創(chuàng)建存儲(chǔ)過程

USEcollegeGOCREATEPROCEDURE學(xué)生個(gè)人成績(jī)

@學(xué)號(hào)char(8)ASSELECTdbo.學(xué)生表.姓名,dbo.學(xué)生表.學(xué)號(hào),dbo.課程表.課程名稱,dbo.成績(jī)表.考試成績(jī)FROMdbo.成績(jī)表INNERJOINdbo.學(xué)生表ONdbo.成績(jī)表.學(xué)號(hào)=dbo.學(xué)生表.學(xué)號(hào)INNERJOINdbo.課程表ONdbo.成績(jī)表.課程號(hào)=dbo.課程表.課程號(hào)

WHERE學(xué)生表.學(xué)號(hào)=@學(xué)號(hào)

GO--運(yùn)行存儲(chǔ)過程，查詢學(xué)號(hào)為05209006的成績(jī)。學(xué)生個(gè)人成績(jī)'05209006'

輸入上述語(yǔ)句并執(zhí)行，將得到如圖9-6所示的結(jié)果。圖9-6創(chuàng)建與運(yùn)行帶輸入?yún)?shù)存儲(chǔ)過程【例9-3】如圖9-7所示，創(chuàng)建一個(gè)帶有參數(shù)的存儲(chǔ)過程“計(jì)算學(xué)生年齡”，該存儲(chǔ)過程根據(jù)傳入的學(xué)生編號(hào)，在學(xué)生表中計(jì)算此學(xué)生的年齡，并根據(jù)程序的執(zhí)行結(jié)果返回不同的值。程序執(zhí)行成功，返回整數(shù)0；如果執(zhí)行出錯(cuò)，則返回錯(cuò)誤號(hào)。

--創(chuàng)建存儲(chǔ)過程

USEcollegeGOalterPROCEDURE計(jì)算學(xué)生年齡

@學(xué)號(hào)char(8),@年齡tinyintOUTPUTAS--定義并初始化局部變量，用于保存返回值

DECLARE@Errint--,@年齡tinyintSET@Err=0--求此學(xué)生的年齡SELECT@年齡=YEAR(GETDATE())-YEAR(出生年月)FROM學(xué)生表WHERE學(xué)生表.學(xué)號(hào)=@學(xué)號(hào)--return@年齡--根據(jù)程序的執(zhí)行結(jié)果返回不同的值,IF(@@ERROR<>0)beginSET@Err=@@ERRORRETURN@Errendgodeclare@年齡tinyintexec計(jì)算學(xué)生年齡'05209003',@年齡outputselect@年齡圖9-7創(chuàng)建與運(yùn)行帶輸出參數(shù)存儲(chǔ)過程9.2.3使用向?qū)?chuàng)建存儲(chǔ)過程使用創(chuàng)建存儲(chǔ)過程向?qū)?chuàng)建存儲(chǔ)過程的步驟如下：

(1)在企業(yè)管理器中選中某個(gè)SQLServer服務(wù)器，這里選中l(wèi)ocal(代表本地機(jī))，選擇要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫(kù)，這里選中college。選擇工具菜單中的“向?qū)А辈藛雾?xiàng)，系統(tǒng)會(huì)彈出“選擇向?qū)А睂?duì)話框，單擊向?qū)е小皵?shù)據(jù)庫(kù)”選項(xiàng)左邊的加號(hào)，選中“創(chuàng)建存儲(chǔ)過程向?qū)А边x項(xiàng)，如圖9-8所示。

(2)單擊“確定”按鈕，出現(xiàn)“歡迎使用創(chuàng)建存儲(chǔ)過程向?qū)А睂?duì)話框，如圖9-9所示。

(3)單擊“下一步”按鈕，出現(xiàn)“選擇數(shù)據(jù)庫(kù)”對(duì)話框，如圖9-10所示。該對(duì)話框用于選擇創(chuàng)建存儲(chǔ)過程中使用的數(shù)據(jù)庫(kù)。圖9-8“選擇向?qū)А睂?duì)話框圖9-9“歡迎使用創(chuàng)建存儲(chǔ)過程向?qū)А睂?duì)話框圖9-10“選擇數(shù)據(jù)庫(kù)”對(duì)話框(4)在圖9-10中，選擇默認(rèn)的數(shù)據(jù)庫(kù)college，單擊“下一步”按鈕，出現(xiàn)“選擇存儲(chǔ)過程”對(duì)話框，如圖9-11所示。在該對(duì)話框中，列出了所有可選擇的表，以及可以對(duì)表進(jìn)行的數(shù)據(jù)庫(kù)操作，即插入、刪除和更新。這里要對(duì)班級(jí)表進(jìn)行操作，因此選中班級(jí)表對(duì)應(yīng)的插入、刪除并更新下面的復(fù)選框。

(5)單擊“下一步”按鈕，出現(xiàn)確認(rèn)存儲(chǔ)過程信息對(duì)話框，如圖9-12所示。如果需要修改前面的設(shè)置，則可在該對(duì)話框中單擊“上一步”按鈕。圖9-11“選擇存儲(chǔ)過程”對(duì)話框圖9-12“正在完成創(chuàng)建存儲(chǔ)過程向?qū)А睂?duì)話框(6)選中名稱為“insert_班級(jí)表_1”的存儲(chǔ)過程，單擊“編輯”按鈕，出現(xiàn)“編輯存儲(chǔ)過程屬性”對(duì)話框，如圖9-13所示。在該對(duì)話框中，可以在“名稱”文本框中修改存儲(chǔ)過程的名稱，在下面的列表框中列出了所選表的所有字段，包括字段名稱、數(shù)據(jù)類型、長(zhǎng)度和是否在存儲(chǔ)過程中使用。單擊“編輯SQL”按鈕，將會(huì)出現(xiàn)創(chuàng)建存儲(chǔ)過程的Transact-SQL語(yǔ)句的編輯界面，如圖9-14所示。用戶可以在已有Transact-SQL語(yǔ)句的基礎(chǔ)上進(jìn)行編輯修改，然后單擊“分析”按鈕，執(zhí)行語(yǔ)法正確性檢驗(yàn)。

(7)在圖9-14中，選中存儲(chǔ)過程insert_班級(jí)表_1，查看它們對(duì)應(yīng)的SQL語(yǔ)句。

insert_班級(jí)表_1存儲(chǔ)過程對(duì)應(yīng)的SQL語(yǔ)句如圖9-14所示。圖9-13“編輯存儲(chǔ)過程屬性”對(duì)話框圖9-14“編輯存儲(chǔ)過程SQL”對(duì)話框(8)編輯完各個(gè)存儲(chǔ)過程的屬性后，在圖9-14中單擊“確定”按鈕，即可完成存儲(chǔ)過程的創(chuàng)建任務(wù)。這里同時(shí)創(chuàng)建了三個(gè)存儲(chǔ)過程：insert_班級(jí)表_1、update_班級(jí)表_1和delete_班級(jí)表_1，分別完成對(duì)班級(jí)表中數(shù)據(jù)的插入、修改和刪除操作。創(chuàng)建成功后，系統(tǒng)會(huì)給出如圖9-15所示的存儲(chǔ)過程創(chuàng)建成功提示信息框。

(9)存儲(chǔ)過程創(chuàng)建完成后，選中企業(yè)管理器中的college數(shù)據(jù)庫(kù)，單擊其中的“存儲(chǔ)過程”，就可以看到新創(chuàng)建的這三個(gè)存儲(chǔ)過程。圖9-15存儲(chǔ)過程創(chuàng)建成功提示信息框9.3執(zhí)行存儲(chǔ)過程存儲(chǔ)過程創(chuàng)建成功后，將保存在數(shù)據(jù)庫(kù)中。在SQLServer中，可以使用EXECUTE命令直接執(zhí)行存儲(chǔ)過程，其語(yǔ)法形式如下：

[EXEC[UTE]][@return_status=]{procedure_name|@procedure_name_var}[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n][WITHRECOMPILE]

其中，各選項(xiàng)的說明如下：●?EXECUTE：執(zhí)行存儲(chǔ)過程的命令關(guān)鍵字。如果此語(yǔ)句是批處理中的第一條語(yǔ)句，則可以省略此關(guān)鍵字。●?@return_status：是一個(gè)可選的整型變量，保存存儲(chǔ)過程的返回狀態(tài)。這個(gè)變量在使用前必須先在批處理、存儲(chǔ)過程或函數(shù)中聲明。●?procedure__name：指定執(zhí)行的存儲(chǔ)過程的名稱?！?@procedure_name_var：局部定義變量名，代表存儲(chǔ)過程名稱?！?@parameter：在創(chuàng)建存儲(chǔ)過程時(shí)定義的過程參數(shù)。調(diào)用時(shí)向存儲(chǔ)過程所傳遞的參數(shù)值由value參數(shù)或@variable變量提供，或者使用DEFAULT關(guān)鍵字指定該參數(shù)的默認(rèn)值，OUTPUT參數(shù)說明指定參數(shù)為返回參數(shù)?！?WITHRECOMPILE：指定在執(zhí)行存儲(chǔ)過程時(shí)重新編譯執(zhí)行計(jì)劃。

執(zhí)行存儲(chǔ)過程時(shí)，需要指定要執(zhí)行的存儲(chǔ)過程的名稱和參數(shù)，使用一個(gè)存儲(chǔ)過程去執(zhí)行一組Transact-SQL語(yǔ)句，可以在首次運(yùn)行時(shí)被編譯，在編譯過程中把Transact-SQL語(yǔ)句從字符形式轉(zhuǎn)化為可執(zhí)行形式。

【例9-4】執(zhí)行前面創(chuàng)建的“顯示學(xué)生信息”存儲(chǔ)過程，它是一個(gè)無(wú)參的存儲(chǔ)過程。程序清單如下：

USEcollegeEXEC顯示學(xué)生信息或直接寫存儲(chǔ)過程的名稱：

USEcollegeGO

顯示學(xué)生信息

程序的執(zhí)行結(jié)果如下：姓名性別出生年月電話 地址張三男1987-02-0355502103 松花江路892弄43號(hào)李四女1986-12-0656565821 共和新路423弄456號(hào)唐寶家男1987-05-0155021456 中山路332弄55號(hào)顧葉男1985-08-2656897142 滬太路555弄973號(hào)李佳誠(chéng)女1988-04-0454869218 老滬太路8529弄1號(hào)王安女1986-09-1855057694 國(guó)順路81弄120號(hào)李靜女1987-12-1956421308 福州路3489弄68號(hào)李朋男1988-10-0357610246 大華新村356弄99號(hào)劉興男1987-07-0154239187 江灣鎮(zhèn)4087弄167號(hào)

(所影響的行數(shù)為9行)

注意：如果省略EXECUTE關(guān)鍵字，則存儲(chǔ)過程必須是批處理中的第一條語(yǔ)句，否則會(huì)出錯(cuò)?！纠?-5】執(zhí)行存儲(chǔ)過程“學(xué)生成績(jī)信息”。程序清單如下：

USEcollegeEXEC學(xué)生成績(jī)信息程序的執(zhí)行結(jié)果如下：班級(jí)名稱 學(xué)號(hào)姓名性別課程名稱考試成績(jī)

--------------------------------------------------------------------------------05網(wǎng)絡(luò)1班 05209001張三男C語(yǔ)言 9005網(wǎng)絡(luò)1班 05209001張三男高等數(shù)學(xué) 8805網(wǎng)絡(luò)1班 05209001張三男鄧小平理論6005網(wǎng)絡(luò)1班 05209002李四女C語(yǔ)言 7005網(wǎng)絡(luò)1班 05209002李四女高等數(shù)學(xué) 7805網(wǎng)絡(luò)1班 05209002李四女鄧小平理論8005網(wǎng)絡(luò)1班 05209003唐寶家男C語(yǔ)言 8505網(wǎng)絡(luò)1班 05209003唐寶家男高等數(shù)學(xué) 9005網(wǎng)絡(luò)1班 05209003唐寶家男鄧小平理論9005軟件班 05209004顧葉男C語(yǔ)言 7505軟件班 05209004顧葉男高等數(shù)學(xué) 7805軟件班 05209004顧葉男鄧小平理論 8505軟件班 05209005李佳誠(chéng)女C語(yǔ)言 9005軟件班 05209005李佳誠(chéng)女高等數(shù)學(xué) 8705軟件班 05209005李佳誠(chéng)女鄧小平理論 6005軟件班 05209006王安女C語(yǔ)言 6005軟件班 05209006王安女高等數(shù)學(xué) 5905軟件班 05209006王安女鄧小平理論 8305網(wǎng)絡(luò)2班 05209007李靜女C語(yǔ)言 7205網(wǎng)絡(luò)2班 05209007李靜女高等數(shù)學(xué) 8005網(wǎng)絡(luò)2班 05209007李靜女鄧小平理論 75(所影響的行數(shù)為21行)

【例9-6】執(zhí)行存儲(chǔ)過程“學(xué)生個(gè)人成績(jī)”，該存儲(chǔ)過程有一個(gè)輸入?yún)?shù)“學(xué)號(hào)”，在執(zhí)行時(shí)需要傳入一個(gè)學(xué)號(hào)值。程序清單如下：

USEcollegeGOEXECUTE學(xué)生個(gè)人成績(jī)'05209006'

或

USEcollegeGOEXECUTE學(xué)生個(gè)人成績(jī)@學(xué)號(hào)='05209006'

程序的執(zhí)行結(jié)果如下：姓名學(xué)號(hào)課程名稱考試成績(jī)

-----------------------------------------------

王安05209006C語(yǔ)言 60

王安05209006高等數(shù)學(xué) 59

王安05209006鄧小平理論 83(所影響的行數(shù)為3行)【例9-7】執(zhí)行例9-3存儲(chǔ)過程“計(jì)算學(xué)生年齡”，該存儲(chǔ)過程有一個(gè)輸入?yún)?shù)“學(xué)號(hào)”，另外，還有一個(gè)輸出參數(shù)“年齡”。程序清單如下：

declare@年齡tinyintexec計(jì)算學(xué)生年齡'05209003',@年齡outputselect@年齡程序的執(zhí)行結(jié)果如下：本程序的執(zhí)行結(jié)果：

----209.4查看和修改存儲(chǔ)過程9.4.1查看存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程之后，它的名字就存儲(chǔ)在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表svscornments中。可以使用企業(yè)管理器或系統(tǒng)存儲(chǔ)過程查看用戶創(chuàng)建的存儲(chǔ)過程。

1.使用企業(yè)管理器查看用戶創(chuàng)建的存儲(chǔ)過程

(1)在企業(yè)管理器中，打開指定的服務(wù)器和數(shù)據(jù)庫(kù)項(xiàng)，指定服務(wù)器下的college數(shù)據(jù)庫(kù)，并單擊college中的“存儲(chǔ)過程”文件夾，此時(shí)在右窗格中就會(huì)顯示出college數(shù)據(jù)庫(kù)中的所有存儲(chǔ)過程，如圖9-16所示。圖9-16存儲(chǔ)過程顯示窗口(2)右擊要查看的存儲(chǔ)過程，這里右擊存儲(chǔ)過程“顯示學(xué)生信息”，從彈出的快捷菜單中選擇“屬性”選項(xiàng)，彈出“存儲(chǔ)過程屬性”對(duì)話框。在此對(duì)話框中能夠看到存儲(chǔ)過程的源代碼，如圖9-17所示。

(3)如果從彈出的快捷菜單中依次選擇“所有任務(wù)”→“顯示相關(guān)性”選項(xiàng)，則會(huì)彈出“相關(guān)性”對(duì)話框，顯示與所選擇的存儲(chǔ)過程有依賴關(guān)系的其他數(shù)據(jù)庫(kù)對(duì)象的名稱，如圖9-18所示。圖9-17“存儲(chǔ)過程屬性”對(duì)話框圖9-18“相關(guān)性”對(duì)話框2.使用系統(tǒng)存儲(chǔ)過程查看用戶創(chuàng)建的存儲(chǔ)過程除了使用企業(yè)管理器查看用戶創(chuàng)建的存儲(chǔ)過程外，也可以使用系統(tǒng)存儲(chǔ)過程查看。可供使用的系統(tǒng)存儲(chǔ)過程及其語(yǔ)法形式如下：●sp_help：用于顯示存儲(chǔ)過程的參數(shù)及其數(shù)據(jù)類型。

sp_help[[@obj_name=]name]

參數(shù)name為要查看的存儲(chǔ)過程的名稱?！駍p_helptext：用于顯示存儲(chǔ)過程的源代碼。

sp_helptext[[@obj_name=]name]

參數(shù)name為要查看的存儲(chǔ)過程的名稱。●sp_depends：用于顯示和存儲(chǔ)過程相關(guān)的數(shù)據(jù)庫(kù)對(duì)象。

sp_depende[@obj_name=]'object'

參數(shù)object為要查看依賴關(guān)系的存儲(chǔ)過程的名稱?！駍p_stored_procedures：用于返回當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過程列表。

sp_stored_procedures[[@sp_name=]'name'][，[@sp_owner=]'owner'][，[@qualifier=]'qualifier']

其中，[@sp_name=]'name'用于指定返回目錄信息的過程名；[@sp_owner=]'owner'用于指定過程所有者的名稱；[@qualifier=]'qualifier'用于指定過程限定符的名稱?！纠?-8】使用系統(tǒng)存儲(chǔ)過程查看“計(jì)算學(xué)生年齡”存儲(chǔ)過程的參數(shù)及其數(shù)據(jù)類型。程序清單如下：

--查看參數(shù)及其數(shù)據(jù)類型

USEcollegeGOsp_help計(jì)算學(xué)生年齡

GO

程序的執(zhí)行結(jié)果如圖9-19所示。圖9-19程序執(zhí)行結(jié)果顯示窗口【例9-9】使用系統(tǒng)存儲(chǔ)過程查看“學(xué)生成績(jī)信息”存儲(chǔ)過程的相關(guān)數(shù)據(jù)庫(kù)對(duì)象。程序清單如下：

--查看相關(guān)的數(shù)據(jù)庫(kù)對(duì)象

USEcollegeGOsp_depends學(xué)生成績(jī)信息

GO

程序的執(zhí)行結(jié)果如圖9-20所示。圖9-20程序執(zhí)行結(jié)果顯示窗口9.4.2修改存儲(chǔ)過程

1.使用企業(yè)管理器修改存儲(chǔ)過程使用企業(yè)管理器可以很方便地修改存儲(chǔ)過程的定義。在企業(yè)管理器中，展開存儲(chǔ)過程，右擊要修改的存儲(chǔ)過程，從彈出的快捷菜單中選擇“屬性”選項(xiàng)，將會(huì)出現(xiàn)存儲(chǔ)過程的“屬性”對(duì)話框。在該對(duì)話框中，可以直接修改定義該存儲(chǔ)過程的Transact-SQL語(yǔ)句。單擊“檢查語(yǔ)法”按鈕，可以進(jìn)行語(yǔ)法檢查；單擊“權(quán)限”按鈕，可以修改用戶執(zhí)行該存儲(chǔ)過程的權(quán)限。2.使用Transact-SQL語(yǔ)句修改存儲(chǔ)過程使用Transact-SQL語(yǔ)言中的ALTERPROCEDURE命令可以更改先前通過執(zhí)行CREATEPROCEDURE語(yǔ)句創(chuàng)建的過程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過程。其語(yǔ)法形式如下：

ALTERPROC[EDURE]procedure_name[；number][{@parameterdata-type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}][FORREPLICATION]ASsql_statement[,...n]

修改存儲(chǔ)過程時(shí)，應(yīng)該注意以下幾點(diǎn)：

(1)如果在CREATEPROCEDURE語(yǔ)句中使用過參數(shù)，那么在ALTERPROCEDURE語(yǔ)句中也應(yīng)該使用這些參數(shù)。

(2)每次只能修改一個(gè)存儲(chǔ)過程。

(3)存儲(chǔ)過程的創(chuàng)建者、db_owner和db_ddladmin的成員擁有執(zhí)行ALTERPROCEDURE語(yǔ)句的許可，其他用戶不能使用。

(4)用ALTERPROCEDURE更改的存儲(chǔ)過程的權(quán)限和啟動(dòng)屬性保持不變。

【例9-10】修改前面創(chuàng)建的“顯示學(xué)生信息”存儲(chǔ)過程，使之完成以下功能：根據(jù)傳入的學(xué)號(hào)在學(xué)生表、課程表和成績(jī)表中查詢此學(xué)生的班級(jí)、姓名、性別、考試課程名稱和考試分?jǐn)?shù)。

程序清單如下：

--修改存儲(chǔ)過程

USEcollegeGOALTERPROCEDURE顯示學(xué)生信息

@學(xué)號(hào)varchar(10)ASSELECT班級(jí)編號(hào),姓名,性別,課程表.課程名稱,成績(jī)表.總評(píng)成績(jī)

FROM學(xué)生表,課程表,成績(jī)表

WHERE學(xué)生表.學(xué)號(hào)=@學(xué)號(hào)

AND學(xué)生表.學(xué)號(hào)=成績(jī)表.學(xué)號(hào)

AND課程表.課程號(hào)=成績(jī)表.課程號(hào)

GO

執(zhí)行修改后的顯示學(xué)生信息的存儲(chǔ)過程：

USEcollegeGO

顯示學(xué)生信息'05209006'GO

程序的執(zhí)行結(jié)果如下：班級(jí)編號(hào)姓名性別課程名稱總評(píng)成績(jī)

-----------------------------------------------------------052005王安女C語(yǔ)言 65052005王安女高等數(shù)學(xué) 69052005王安女鄧小平理論 84(所影響的行數(shù)為3行)9.5重命名和刪除存儲(chǔ)過程9.5.1重命名存儲(chǔ)過程

1.使用企業(yè)管理器修改存儲(chǔ)過程名稱方法是：在企業(yè)管理器中，右擊要操作的存儲(chǔ)過程名稱，從彈出的快捷菜單中選擇“重命名”選項(xiàng)，修改該存儲(chǔ)過程的名稱，最后在彈出的確認(rèn)重命名對(duì)話框中單擊“是”按鈕即可。

2.使用系統(tǒng)存儲(chǔ)過程修改存儲(chǔ)過程名稱修改存儲(chǔ)過程的名稱也可以使用系統(tǒng)存儲(chǔ)過程sp_rename，其語(yǔ)法形式如下：

sp_rename原存儲(chǔ)過程名稱，新存儲(chǔ)過程名稱

【例9-11】使用系統(tǒng)存儲(chǔ)過程將“顯示學(xué)生信息”存儲(chǔ)過程的名稱修改為“學(xué)生信息資料”。程序清單如下：

USEcollegeGOSp_rename顯示學(xué)生信息,學(xué)生信息資料

GO

程序的執(zhí)行結(jié)果如下：

object已重命名為'學(xué)生信息資料'。

注意:

更改對(duì)象名的任一部分都可能破壞腳本和存儲(chǔ)過程。9.5.2刪除存儲(chǔ)過程

1.使用企業(yè)管理器刪除存儲(chǔ)過程在企業(yè)管理器中，右擊要?jiǎng)h除的存儲(chǔ)過程，從彈出的快捷菜單中選擇“刪除”選項(xiàng)，彈出“除去對(duì)象”對(duì)話框，單擊“全部除去”按鈕，即可完成刪除操作。在刪除該對(duì)象之前，單擊“顯示相關(guān)性”按鈕，可以查看與該存儲(chǔ)過程有依賴關(guān)系的其他數(shù)據(jù)庫(kù)對(duì)象名稱。

2.使用Transact-SQL語(yǔ)句刪除存儲(chǔ)過程刪除存儲(chǔ)過程也可以使用Transact-SQL語(yǔ)言中的DROP命令，DROP命令可以將若干個(gè)存儲(chǔ)過程或者存儲(chǔ)過程組從當(dāng)前數(shù)據(jù)庫(kù)中刪除，其語(yǔ)法形式如下：

DROPprocedure{procedure}[，...n]【例9-12】使用DROP命令一次刪除update_班級(jí)表_1和delete_班級(jí)表_1兩個(gè)存儲(chǔ)過程。程序清單如下：

USEcollegeGODROPprocedureupdate_班級(jí)表_1，delete_班級(jí)表_1GO

程序執(zhí)行結(jié)果如下：命令已成功完成。如果程序返回了上面的執(zhí)行結(jié)果，則表示已成功刪除了update_班級(jí)表_1和delete_班級(jí)表_1這兩個(gè)存儲(chǔ)過程。9.6觸發(fā)器9.6.1觸發(fā)器的概念觸發(fā)器是一種特殊類型的存儲(chǔ)過程，它不同于前面介紹過的一般的存儲(chǔ)過程。一般的存儲(chǔ)過程通過存儲(chǔ)過程名稱被直接調(diào)用，而觸發(fā)器主要通過事件進(jìn)行觸發(fā)而被執(zhí)行。觸發(fā)器是一個(gè)功能強(qiáng)大的工具，它隨時(shí)監(jiān)視數(shù)據(jù)表，當(dāng)表中數(shù)據(jù)發(fā)生變化時(shí)自動(dòng)執(zhí)行。觸發(fā)器可以用于SQLServer約束、默認(rèn)值和規(guī)則的完整性檢查，還可以完成用普通約束難以實(shí)現(xiàn)的復(fù)雜功能。

當(dāng)在某一個(gè)數(shù)據(jù)表中插入、修改或者刪除記錄時(shí)，SQLServer就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語(yǔ)句所定義的規(guī)則。在觸發(fā)器中可以查詢其他數(shù)據(jù)表，甚至復(fù)雜的SQL語(yǔ)句。觸發(fā)器和引起觸發(fā)器執(zhí)行的SQL語(yǔ)句被當(dāng)作一次事務(wù)處理，如果這次事務(wù)未獲得成功，則SQLServer會(huì)自動(dòng)返回該事務(wù)執(zhí)行前的狀態(tài)。和CHECK約束相比較，觸發(fā)器可以強(qiáng)制實(shí)現(xiàn)更加復(fù)雜的數(shù)據(jù)完整性，而且可以引用其他表中的字段。9.6.2觸發(fā)器的優(yōu)點(diǎn)觸發(fā)器具有以下優(yōu)點(diǎn)：

(1)觸發(fā)器是自動(dòng)執(zhí)行的。在對(duì)表中的數(shù)據(jù)做了任何修改(比如手工輸入或者應(yīng)用程序采取的操作)之后立即被激活。

(2)觸發(fā)器可以通過數(shù)據(jù)庫(kù)中的相關(guān)表進(jìn)行級(jí)聯(lián)更改。例如，可以在學(xué)生表的學(xué)號(hào)列上寫入一個(gè)刪除觸發(fā)器，以使成績(jī)表中的各匹配行采取刪除操作。該觸發(fā)器用學(xué)號(hào)列作為唯一鍵，在成績(jī)表中對(duì)各匹配行進(jìn)行定位。

(3)觸發(fā)器可以強(qiáng)制限制。這些限制比用CHECK約束所定義的更復(fù)雜。與CHECK約束不同的是，觸發(fā)器可以引用其他表中的列。9.6.3觸發(fā)器的類型觸發(fā)器可以分為AFTER觸發(fā)器和INSTEADOF觸發(fā)器兩種。

(1)?AFTER觸發(fā)器：這種類型的觸發(fā)器將在數(shù)據(jù)變動(dòng)(INSERT、UPDATE和DELETE操作)完成以后才被觸發(fā)?？梢詫?duì)變動(dòng)的數(shù)據(jù)進(jìn)行檢查，如果發(fā)現(xiàn)錯(cuò)誤，則將拒絕接受或回滾變動(dòng)的數(shù)據(jù)。AFTER觸發(fā)器只能在表上定義。在同一個(gè)數(shù)據(jù)表中可以創(chuàng)建多個(gè)AFTER觸發(fā)器。

(2)?INSTEADOF觸發(fā)器：INSTEADOF觸發(fā)器是SQLServer2000中新增的功能。這種類型的觸發(fā)器將在數(shù)據(jù)變動(dòng)以前被觸發(fā)，即用執(zhí)行觸發(fā)器定義的操作取代變動(dòng)數(shù)據(jù)的操作(INSERT、UPDATE和DELETE操作)。INSTEADOF觸發(fā)器可以在表或視圖上定義。在表或視圖上，每個(gè)INSERT、UPDATE和DELETE語(yǔ)句最多可以定義一個(gè)INSTEADOF觸發(fā)器。9.7創(chuàng)?建?觸?發(fā)?器在SQLServer中，可以使用企業(yè)管理器或者Transact-SQL語(yǔ)句創(chuàng)建觸發(fā)器。在創(chuàng)建觸發(fā)器時(shí)應(yīng)該注意以下幾個(gè)問題：

(1)?CREATETRIGGER語(yǔ)句必須是批處理中的第一個(gè)語(yǔ)句。將該批處理中隨后的其他所有語(yǔ)句解釋為CREATETRIGGER語(yǔ)句定義的一部分。

(2)創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。

(3)觸發(fā)器為數(shù)據(jù)庫(kù)對(duì)象，其名稱必須遵循標(biāo)識(shí)符的命名規(guī)則。

(4)只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建觸發(fā)器，但觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫(kù)以外的對(duì)象。(5)?TRUNCATETABLE語(yǔ)句不會(huì)引發(fā)DELETE觸發(fā)器。

(6)?WRITETEXT語(yǔ)句不會(huì)引發(fā)INSERT或UPDATE觸發(fā)器。9.7.1使用企業(yè)管理器創(chuàng)建觸發(fā)器使用企業(yè)管理器創(chuàng)建觸發(fā)器的步驟如下：

(1)在企業(yè)管理器中展開指定的服務(wù)器和數(shù)據(jù)庫(kù)，這里展開LOCAL服務(wù)器下面的college數(shù)據(jù)庫(kù)，右擊學(xué)生表，從彈出的快捷菜單中依次選擇“所有任務(wù)”→“管理觸發(fā)器”選項(xiàng)，將出現(xiàn)“觸發(fā)器屬性”對(duì)話框，如圖9-21所示。圖9-21“觸發(fā)器屬性”對(duì)話框(2)在“觸發(fā)器屬性”對(duì)話框的“名稱”文本框中選擇“新建”，然后在文本框中輸入創(chuàng)建觸發(fā)器的文本。在創(chuàng)建一個(gè)觸發(fā)器時(shí)必須指定以下幾項(xiàng)內(nèi)容：●觸發(fā)器的名稱；●需要監(jiān)視數(shù)據(jù)操作的表；●觸發(fā)器將由數(shù)據(jù)的增、刪、改中的什么事件觸發(fā)；●執(zhí)行觸發(fā)操作的程序語(yǔ)句。在文本框中輸入以下文本：

CREATETRIGGERtr_學(xué)生_InsertON學(xué)生表

FORINSERTASDECLARE@標(biāo)示char(30)SET@標(biāo)示="你插入了一條新記錄!"PRINT@標(biāo)示上面這段程序的功能是創(chuàng)建一個(gè)由INSERT事件觸發(fā)的觸發(fā)器，當(dāng)在學(xué)生表中插入一條新記錄時(shí)，該觸發(fā)器給出“你插入了一條新記錄!”的提示信息。

(3)單擊“檢查語(yǔ)法”按鈕，可以檢查語(yǔ)法是否正確，顯示結(jié)果如圖9-22所示。

(4)單擊“應(yīng)用”按鈕，在名稱下拉列表中出現(xiàn)新創(chuàng)建的tr_學(xué)生_Insert觸發(fā)器的名稱，單擊“確定”按鈕，即可關(guān)閉該對(duì)話框，成功創(chuàng)建觸發(fā)器。圖9-22語(yǔ)法檢查結(jié)果對(duì)話框

【例9-13】創(chuàng)建tr_學(xué)生_Insert觸發(fā)器后，查看向?qū)W生表中插入數(shù)據(jù)時(shí)此觸發(fā)器所完成的功能。在查詢分析器中輸入以下SQL語(yǔ)句：

USEcollegeGOinsertinto學(xué)生表

(學(xué)號(hào),姓名,性別,出生年月,班級(jí)編號(hào),電話,地址,身份證)values(‘05209030’,‘程晨’,‘女’,‘1986-11-11’,‘052005’,‘65214213’,‘松花江路33弄5號(hào)603室',)GO

程序的執(zhí)行結(jié)果如下：你插入了一條新記錄!(所影響的行數(shù)為1行)9.7.2使用Transact-SQL語(yǔ)句創(chuàng)建觸發(fā)器使用Transact-SQL語(yǔ)言中的CREATETRIGGER命令也可以創(chuàng)建觸發(fā)器，其中需要指定觸發(fā)器所監(jiān)視的表、觸發(fā)器執(zhí)行的事件和觸發(fā)器的所有指令。創(chuàng)建觸發(fā)器的過程類似于創(chuàng)建存儲(chǔ)過程，其語(yǔ)法形式如下：

CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{(FOR|AFTER|INSTEADOF){[DELETE][，][INSERT][，][UPDATE]}[WITHAPPEND][NOTFORREPLICATION]AS[{IFUPDATE(column)[(AND|OR)UPDATE(column)][,...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[,...n]}]sql_statement[,...n]}}

其中，各參數(shù)的說明如下：●?trigger_name：用于指定觸發(fā)器的名稱。觸發(fā)器的名稱必須符合SQLServer標(biāo)識(shí)符規(guī)則，并且其名稱在當(dāng)前數(shù)據(jù)庫(kù)中必須是唯一的。另外，還可以選擇是否指定觸發(fā)器所有者的名稱?！?table|view：用于指定在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱為觸發(fā)器表或觸發(fā)器視圖。另外，還可以選擇是否指定表或視圖的所有者名稱?！?WITHENCRYPTION：用于加密syscomments表中包含CREATETRIGGER語(yǔ)句文本的條目。使用WITHENCRYPTION可防止將觸發(fā)器作為SQLServer復(fù)制的一部分發(fā)布?！?AFTER：用于規(guī)定此觸發(fā)器只有在觸發(fā)SQL語(yǔ)句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級(jí)聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。如果僅指定FOR關(guān)鍵字，則AFTER是默認(rèn)設(shè)置。注意，該類型觸發(fā)器僅能在表上創(chuàng)建，而不能在視圖上定義該觸發(fā)器?！?INSTEADOF：用于規(guī)定執(zhí)行的是觸發(fā)器而不是執(zhí)行觸發(fā)SQL語(yǔ)句，從而用觸發(fā)器替代觸發(fā)語(yǔ)句的操作。在表或視圖上，每個(gè)INSERT、UPDATE或DELETE語(yǔ)句最多可以定義一個(gè)INSTEADOF觸發(fā)器。INSTEADOF觸發(fā)器不能在WITHCHECKOPTION的可更新視圖上定義。如果向指定的WITHCHECKOPTION選項(xiàng)的可更新視圖上添加INSTEADOF觸發(fā)器，則SQLServer將產(chǎn)生一個(gè)錯(cuò)誤。用戶必須用ALTERVIEW刪除該選項(xiàng)后才能定義INSTEADOF觸發(fā)器?！?{[DELETE][,][INSERT][,][UPDATE]}：用于指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語(yǔ)句時(shí)將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個(gè)選項(xiàng)。在觸發(fā)器定義中允許以任意順序組合這些關(guān)鍵字。如果指定的選項(xiàng)多于一個(gè)，則需用逗號(hào)分隔這些選項(xiàng)?！?WITHAPPEND：用于指定應(yīng)該添加現(xiàn)有類型的其他觸發(fā)器。只有當(dāng)兼容級(jí)別(指某一數(shù)據(jù)庫(kù)行為與以前版本的SQLServer的兼容程度)是65或更低時(shí)，才需要使用該可選子句。如果兼容級(jí)別是70或更高，則不必使用該子句?！?NOTFORREPLICATION：表示當(dāng)復(fù)制進(jìn)程更改觸發(fā)器所涉及的表時(shí)，不應(yīng)執(zhí)行該觸發(fā)器?！?AS：觸發(fā)器要執(zhí)行的操作?！?sql_statement：觸發(fā)器的條件和操作。觸發(fā)器條件指定其他準(zhǔn)則，以確定DELETE、INSERT或UPDATE語(yǔ)句是否導(dǎo)致執(zhí)行觸發(fā)器操作?！?IFUPDATE(column)：用于測(cè)試在指定的列上進(jìn)行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。因?yàn)樵贠N子句中指定了表名，所以在IFUPDATE子句的列名前不要包含表名。若要測(cè)試在多個(gè)列上進(jìn)行的INSERT或UPDATE操作，則應(yīng)在第一個(gè)操作后指定單獨(dú)的UPDATE(column)子句。在INSERT操作中，IFUPDATE將返回TRUE值，因?yàn)檫@些列插入了顯式值或隱性值(NULL)。●?IF(COLUMNS_UPDATED()：用于測(cè)試是否插入或更新了所涉及的列，僅用于INSERT或者UPDATE觸發(fā)器?！?bitwise_operator：用于比較運(yùn)算的位邏輯運(yùn)算符。●?updated_bitmask：整型位掩碼，表示實(shí)際更新或插入的列。例如，表t1包含列C1、C2、C3、C4和C5。假定表t1上有UPDATE觸發(fā)器，若要檢查列C2、C3和C4是否都已更新，則指定值14；若要檢查是否只有列C2已更新，則指定值2?！?comparison_operator：比較運(yùn)算符。使用等號(hào)(=)檢查updated_bitmask中指定的所有列是否都實(shí)際進(jìn)行了更新。使用大于號(hào)(>)檢查updated_bitmask中指定的任一列或某些列是否已更新?！?column_bitmask：檢查列的整型位掩碼，用來(lái)檢查是否已更新或插入了這些列。注意：當(dāng)創(chuàng)建觸發(fā)器時(shí)，如果使用了相同名稱的觸發(fā)器，則后面建立的觸發(fā)器將會(huì)覆蓋前面建立的觸發(fā)器。用戶不能在系統(tǒng)表上創(chuàng)建用戶自定義的觸發(fā)器。在創(chuàng)建觸發(fā)器時(shí)，可以使用兩個(gè)特殊的臨時(shí)表，它們分別是inserted表和deleted表，這兩個(gè)表都存在于內(nèi)存中。

inserted表中存儲(chǔ)著被INSERT和UPDATE語(yǔ)句影響的新的數(shù)據(jù)行。執(zhí)行INSERT或UPDATE語(yǔ)句時(shí)，新的數(shù)據(jù)行被添加到基本表中，同時(shí)這些數(shù)據(jù)行的備份被復(fù)制到inserted臨時(shí)表中。deleted表中存儲(chǔ)著被DELETE和UPDATE語(yǔ)句影響的舊的數(shù)據(jù)行。執(zhí)行DELETE或UPDATE語(yǔ)句時(shí)，指定的數(shù)據(jù)行從基本表中刪除，然后被轉(zhuǎn)移到deleted表中。在基本表和deleted表中一般不存在相同的數(shù)據(jù)行。一個(gè)UPDATE操作實(shí)際上是由一個(gè)DELETE操作和一個(gè)INSERT操作組成的。在執(zhí)行UPDATE操作時(shí)，舊的數(shù)據(jù)行從基本表中轉(zhuǎn)移到deleted表中，然后將新的數(shù)據(jù)行同時(shí)插入基本表和inserted表中。下面舉例說明如何使用Transact-SQL語(yǔ)句創(chuàng)建觸發(fā)器，并在觸發(fā)器中使用inserted和deleted臨時(shí)表?！纠?-14】創(chuàng)建一個(gè)AFTER觸發(fā)器，要求實(shí)現(xiàn)以下功能：在成績(jī)表上創(chuàng)建一個(gè)插入、更新類型的觸發(fā)器“檢查分?jǐn)?shù)觸發(fā)器”，當(dāng)在考試成績(jī)字段中插入或修改考試分?jǐn)?shù)后，觸發(fā)該觸發(fā)器，檢查分?jǐn)?shù)是否在0～100之間。．程序清單如下：

USEcollegeGOCREATETRIGGER檢查分?jǐn)?shù)觸發(fā)器

ON成績(jī)表

FORINSERT,UPDATEASIFUPDATE(考試成績(jī))PRINT'AFTER觸發(fā)器開始執(zhí)行……'BEGINDECLARE@分?jǐn)?shù)intSELECT@分?jǐn)?shù)=(SELECT考試成績(jī)

FROMinserted)IF(@分?jǐn)?shù)NOTbetween0and100)PRINT'輸入的分?jǐn)?shù)超出范圍，請(qǐng)重新輸入的考試分?jǐn)?shù)!'ENDGO

創(chuàng)建了“檢查分?jǐn)?shù)觸發(fā)器”之后，在查詢分析器中輸入以下SQL語(yǔ)句：

USEcollegeGOPRINT'在成績(jī)表中插入記錄時(shí)觸發(fā)器執(zhí)行結(jié)果：'PRINT''INSERTINTO成績(jī)表VALUES('05209030','059001',35,-25,5)INSERTINTO成績(jī)表VALUES('05209030','059002',35,225,100)GOPRINT'在成績(jī)表中修改記錄時(shí)觸發(fā)器執(zhí)行結(jié)果：'PRINT''UPDATE成績(jī)表SET考試成績(jī)=115WHERE學(xué)號(hào)='05209030'and課程號(hào)='059001'UPDATE成績(jī)表SET考試成績(jī)=-65WHERE學(xué)號(hào)='05209030'and課程號(hào)='059002'

執(zhí)行上面的SQL語(yǔ)句，結(jié)果如下：在成績(jī)表中插入記錄時(shí)觸發(fā)器執(zhí)行結(jié)果：

AFTER觸發(fā)器開始執(zhí)行……

輸入的分?jǐn)?shù)超出范圍，請(qǐng)重新輸入的考試分?jǐn)?shù)!(所影響的行數(shù)為1行)

在成績(jī)表中修改記錄時(shí)觸發(fā)器執(zhí)行結(jié)果如下：

AFTER觸發(fā)器開始執(zhí)行……

輸入的分?jǐn)?shù)超出范圍，請(qǐng)重新輸入的考試分?jǐn)?shù)!(所影響的行數(shù)為1行)【例9-15】創(chuàng)建一個(gè)AFTER觸發(fā)器，要求實(shí)現(xiàn)以下功能：在學(xué)生表上創(chuàng)建一個(gè)刪除類型的觸發(fā)器tr_學(xué)生刪除，當(dāng)在學(xué)生表中刪除某一條記錄后，觸發(fā)該觸發(fā)器，在成績(jī)表中刪除與此學(xué)號(hào)對(duì)應(yīng)的記錄。

USEcollegeGOCREATETRIGGERtr_學(xué)生刪除

ON學(xué)生表

FORDELETEASPRINT'刪除觸發(fā)器開始執(zhí)行……'DECLARE@學(xué)號(hào)char(10)PRINT'把在學(xué)生表中刪除的記錄的學(xué)號(hào)賦值給局部變量@學(xué)號(hào)。'SELECT@學(xué)號(hào)=學(xué)號(hào)

FROMdeletedPRINT'開始查找并刪除成績(jī)表中的相關(guān)記錄……'DELETEFROM成績(jī)表

WHERE學(xué)號(hào)=@學(xué)號(hào)

PRINT'刪除了成績(jī)表中的學(xué)號(hào)為'+RTRIM(@學(xué)號(hào))+'的記錄。'GO

創(chuàng)建了tr_學(xué)生刪除觸發(fā)器之后，在查詢分析器中輸入以下SQL語(yǔ)句：

USEcollegeGODELETEFROM學(xué)生表WHERE學(xué)號(hào)='05209006'DELETEFROM成績(jī)表WHERE學(xué)號(hào)='05209006'GO

程序的執(zhí)行結(jié)果如下：刪除觸發(fā)器開始執(zhí)行……

把在學(xué)生表中刪除的記錄的學(xué)號(hào)賦值給局部變量@學(xué)號(hào)。開始查找并刪除成績(jī)表中的相關(guān)記錄……(所影響的行數(shù)為3行)(所影響的行數(shù)為1行)

刪除了成績(jī)表中的學(xué)號(hào)為05209006的記錄。

(所影響的行數(shù)為0行)

【例9-16】創(chuàng)建一個(gè)INSTEADOF觸發(fā)器，要求實(shí)現(xiàn)以下功能：在教師表上創(chuàng)建一個(gè)刪除類型的觸發(fā)器“不可刪除”，當(dāng)在教師表中刪除