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

(1)存儲(chǔ)過程可以包含一條或多條Transact-SQL語句。(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)通過語法檢查和編譯，調(diào)用時(shí)可直接執(zhí)行，程序的運(yùn)行效率高，其執(zhí)行速度要比標(biāo)準(zhǔn)SQL語句快得多。當(dāng)含有大量SQL語句的批處理需要重復(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ù)庫的使用功能改變時(shí)，只需對(duì)相應(yīng)的存儲(chǔ)過程進(jìn)行修改而不用修改應(yīng)用程序。

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

(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ù)邏輯的程序語句可以加密，存儲(chǔ)過程一旦加密，即使是系統(tǒng)管理員也難以解密。9.1.3存儲(chǔ)過程的分類

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

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

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

(1)作為存儲(chǔ)過程名稱的標(biāo)識(shí)符其長度最大為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語句中的CREATEPROCEDURE命令創(chuàng)建存儲(chǔ)過程。默認(rèn)情況下，創(chuàng)建存儲(chǔ)過程的許可權(quán)歸數(shù)據(jù)庫的所有者，數(shù)據(jù)庫的所有者可以把許可權(quán)授給其他用戶。當(dāng)創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的以下三個(gè)組成部分：

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

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

(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ù)庫，右擊要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫，在彈出的快捷菜單中依次選擇“新建”→“存儲(chǔ)過程”選項(xiàng)，如圖9-1所示，或者右擊數(shù)據(jù)庫中的存儲(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語句，這里創(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)輸入完畢單擊“檢查語法”按鈕進(jìn)行語法檢查，檢查成功后系統(tǒng)會(huì)彈出如圖9-4所示的提示信息框。圖9-4語法檢查提示信息框(4)在提示信息框中單擊“確定”按鈕，再在存儲(chǔ)過程屬性框中單擊“確定”按鈕保存該存儲(chǔ)過程，并關(guān)閉該對(duì)話框。9.2.2使用Transact-SQL語句創(chuàng)建存儲(chǔ)過程可以使用Transact-SQL語句中的CREATEPROCEDURE命令創(chuàng)建存儲(chǔ)過程。創(chuàng)建存儲(chǔ)過程前，應(yīng)該注意下列事項(xiàng)：

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

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

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

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

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

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ù)庫中或者對(duì)其所有者而言，存儲(chǔ)過程的名稱必須唯一?！?@parameter：過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個(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語句的存儲(chǔ)過程文本。使用ENCRYPTION關(guān)鍵字無法通過查看syscomments表來查看存儲(chǔ)過程的內(nèi)容?！?FORREPLICATION：用于指定該存儲(chǔ)過程只能在數(shù)據(jù)復(fù)制時(shí)使用。本選項(xiàng)不能和WITHRECOMPILE選項(xiàng)一起使用?！?AS：用于指定該存儲(chǔ)過程要執(zhí)行的操作?！?sql_statement：是存儲(chǔ)過程中包含的任意數(shù)目和類型的Transact-SQL語句。

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

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

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

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'

輸入上述語句并執(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ù)庫，這里選中college。選擇工具菜單中的“向?qū)А辈藛雾?xiàng)，系統(tǒng)會(huì)彈出“選擇向?qū)А睂?duì)話框，單擊向?qū)е小皵?shù)據(jù)庫”選項(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ù)庫”對(duì)話框，如圖9-10所示。該對(duì)話框用于選擇創(chuàng)建存儲(chǔ)過程中使用的數(shù)據(jù)庫。圖9-8“選擇向?qū)А睂?duì)話框圖9-9“歡迎使用創(chuàng)建存儲(chǔ)過程向?qū)А睂?duì)話框圖9-10“選擇數(shù)據(jù)庫”對(duì)話框(4)在圖9-10中，選擇默認(rèn)的數(shù)據(jù)庫college，單擊“下一步”按鈕，出現(xiàn)“選擇存儲(chǔ)過程”對(duì)話框，如圖9-11所示。在該對(duì)話框中，列出了所有可選擇的表，以及可以對(duì)表進(jìn)行的數(shù)據(jù)庫操作，即插入、刪除和更新。這里要對(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ù)類型、長度和是否在存儲(chǔ)過程中使用。單擊“編輯SQL”按鈕，將會(huì)出現(xiàn)創(chuàng)建存儲(chǔ)過程的Transact-SQL語句的編輯界面，如圖9-14所示。用戶可以在已有Transact-SQL語句的基礎(chǔ)上進(jìn)行編輯修改，然后單擊“分析”按鈕，執(zhí)行語法正確性檢驗(yàn)。

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

insert_班級(jí)表_1存儲(chǔ)過程對(duì)應(yīng)的SQL語句如圖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ù)庫，單擊其中的“存儲(chǔ)過程”，就可以看到新創(chuàng)建的這三個(gè)存儲(chǔ)過程。圖9-15存儲(chǔ)過程創(chuàng)建成功提示信息框9.3執(zhí)行存儲(chǔ)過程存儲(chǔ)過程創(chuàng)建成功后，將保存在數(shù)據(jù)庫中。在SQLServer中，可以使用EXECUTE命令直接執(zhí)行存儲(chǔ)過程，其語法形式如下：

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

其中，各選項(xiàng)的說明如下：●?EXECUTE：執(zhí)行存儲(chǔ)過程的命令關(guān)鍵字。如果此語句是批處理中的第一條語句，則可以省略此關(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ùn)行時(shí)被編譯，在編譯過程中把Transact-SQL語句從字符形式轉(zhuǎn)化為可執(zhí)行形式。

【例9-4】執(zhí)行前面創(chuàng)建的“顯示學(xué)生信息”存儲(chǔ)過程，它是一個(gè)無參的存儲(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)李佳誠女1988-04-0454869218 老滬太路8529弄1號(hào)王安女1986-09-1855057694 國順路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ǔ)過程必須是批處理中的第一條語句，否則會(huì)出錯(cuò)?！纠?-5】執(zhí)行存儲(chǔ)過程“學(xué)生成績(jī)信息”。程序清單如下：

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

--------------------------------------------------------------------------------05網(wǎng)絡(luò)1班 05209001張三男C語言 9005網(wǎng)絡(luò)1班 05209001張三男高等數(shù)學(xué) 8805網(wǎng)絡(luò)1班 05209001張三男鄧小平理論6005網(wǎng)絡(luò)1班 05209002李四女C語言 7005網(wǎng)絡(luò)1班 05209002李四女高等數(shù)學(xué) 7805網(wǎng)絡(luò)1班 05209002李四女鄧小平理論8005網(wǎng)絡(luò)1班 05209003唐寶家男C語言 8505網(wǎng)絡(luò)1班 05209003唐寶家男高等數(shù)學(xué) 9005網(wǎng)絡(luò)1班 05209003唐寶家男鄧小平理論9005軟件班 05209004顧葉男C語言 7505軟件班 05209004顧葉男高等數(shù)學(xué) 7805軟件班 05209004顧葉男鄧小平理論 8505軟件班 05209005李佳誠女C語言 9005軟件班 05209005李佳誠女高等數(shù)學(xué) 8705軟件班 05209005李佳誠女鄧小平理論 6005軟件班 05209006王安女C語言 6005軟件班 05209006王安女高等數(shù)學(xué) 5905軟件班 05209006王安女鄧小平理論 8305網(wǎng)絡(luò)2班 05209007李靜女C語言 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語言 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中?？梢允褂闷髽I(yè)管理器或系統(tǒng)存儲(chǔ)過程查看用戶創(chuàng)建的存儲(chǔ)過程。

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

(1)在企業(yè)管理器中，打開指定的服務(wù)器和數(shù)據(jù)庫項(xiàng)，指定服務(wù)器下的college數(shù)據(jù)庫，并單擊college中的“存儲(chǔ)過程”文件夾，此時(shí)在右窗格中就會(huì)顯示出college數(shù)據(jù)庫中的所有存儲(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ù)庫對(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ǔ)過程查看?？晒┦褂玫南到y(tǒng)存儲(chǔ)過程及其語法形式如下：●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ǔ)過程的名稱?！駍p_depends：用于顯示和存儲(chǔ)過程相關(guān)的數(shù)據(jù)庫對(duì)象。

sp_depende[@obj_name=]'object'

參數(shù)object為要查看依賴關(guān)系的存儲(chǔ)過程的名稱?！駍p_stored_procedures：用于返回當(dāng)前數(shù)據(jù)庫中的存儲(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ù)庫對(duì)象。程序清單如下：

--查看相關(guān)的數(shù)據(jù)庫對(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語句。單擊“檢查語法”按鈕，可以進(jìn)行語法檢查；單擊“權(quán)限”按鈕，可以修改用戶執(zhí)行該存儲(chǔ)過程的權(quán)限。2.使用Transact-SQL語句修改存儲(chǔ)過程使用Transact-SQL語言中的ALTERPROCEDURE命令可以更改先前通過執(zhí)行CREATEPROCEDURE語句創(chuàng)建的過程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過程。其語法形式如下：

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語句中使用過參數(shù)，那么在ALTERPROCEDURE語句中也應(yīng)該使用這些參數(shù)。

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

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

(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語言 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，其語法形式如下：

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ù)庫對(duì)象名稱。

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

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語句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。在觸發(fā)器中可以查詢其他數(shù)據(jù)表，甚至復(fù)雜的SQL語句。觸發(fā)器和引起觸發(fā)器執(zhí)行的SQL語句被當(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ù)庫中的相關(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語句最多可以定義一個(gè)INSTEADOF觸發(fā)器。9.7創(chuàng)?建?觸?發(fā)?器在SQLServer中，可以使用企業(yè)管理器或者Transact-SQL語句創(chuàng)建觸發(fā)器。在創(chuàng)建觸發(fā)器時(shí)應(yīng)該注意以下幾個(gè)問題：

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

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

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

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

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

(1)在企業(yè)管理器中展開指定的服務(wù)器和數(shù)據(jù)庫，這里展開LOCAL服務(wù)器下面的college數(shù)據(jù)庫，右擊學(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ā)操作的程序語句。在文本框中輸入以下文本：

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)單擊“檢查語法”按鈕，可以檢查語法是否正確，顯示結(jié)果如圖9-22所示。

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

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

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語句創(chuàng)建觸發(fā)器使用Transact-SQL語言中的CREATETRIGGER命令也可以創(chuàng)建觸發(fā)器，其中需要指定觸發(fā)器所監(jiān)視的表、觸發(fā)器執(zhí)行的事件和觸發(fā)器的所有指令。創(chuàng)建觸發(fā)器的過程類似于創(chuàng)建存儲(chǔ)過程，其語法形式如下：

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ù)庫中必須是唯一的。另外，還可以選擇是否指定觸發(fā)器所有者的名稱?！?table|view：用于指定在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱為觸發(fā)器表或觸發(fā)器視圖。另外，還可以選擇是否指定表或視圖的所有者名稱?！?WITHENCRYPTION：用于加密syscomments表中包含CREATETRIGGER語句文本的條目。使用WITHENCRYPTION可防止將觸發(fā)器作為SQLServer復(fù)制的一部分發(fā)布?！?AFTER：用于規(guī)定此觸發(fā)器只有在觸發(fā)SQL語句中指定的所有操作都已成功執(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語句，從而用觸發(fā)器替代觸發(fā)語句的操作。在表或視圖上，每個(gè)INSERT、UPDATE或DELETE語句最多可以定義一個(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ù)修改語句時(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ù)庫行為與以前版本的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語句是否導(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：檢查列的整型位掩碼，用來檢查是否已更新或插入了這些列。注意：當(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語句影響的新的數(shù)據(jù)行。執(zhí)行INSERT或UPDATE語句時(shí)，新的數(shù)據(jù)行被添加到基本表中，同時(shí)這些數(shù)據(jù)行的備份被復(fù)制到inserted臨時(shí)表中。deleted表中存儲(chǔ)著被DELETE和UPDATE語句影響的舊的數(shù)據(jù)行。執(zhí)行DELETE或UPDATE語句時(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語句創(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語句：

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語句，結(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語句：

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)在教師表中刪除