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

1、第 9 章 創(chuàng)建存儲(chǔ)過程和觸發(fā)器 n存儲(chǔ)過程概述存儲(chǔ)過程概述 n創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程 n執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程 n查看、重命名和刪除存儲(chǔ)過程查看、重命名和刪除存儲(chǔ)過程 n創(chuàng)建帶有參數(shù)的存儲(chǔ)過程創(chuàng)建帶有參數(shù)的存儲(chǔ)過程 n觸發(fā)器概述 nDML觸發(fā)器的創(chuàng)建和應(yīng)用 nDDL觸發(fā)器的創(chuàng)建和應(yīng)用 n嵌套觸發(fā)器和遞歸觸發(fā)器 n查看、修改和刪除觸發(fā)器 存儲(chǔ)過程概述存儲(chǔ)過程概述 任務(wù)演示：任務(wù)演示： 小張的學(xué)校為進(jìn)一步提高師資質(zhì)量，開展了網(wǎng)上評(píng)教活動(dòng)，通過學(xué)生小張的學(xué)校為進(jìn)一步提高師資質(zhì)量，開展了網(wǎng)上評(píng)教活動(dòng)，通過學(xué)生 反映老師的授課情況?；顒?dòng)結(jié)束后，教務(wù)部門將通過學(xué)生對(duì)老師的評(píng)教信反映老師的授課情況?；?/p>

2、動(dòng)結(jié)束后，教務(wù)部門將通過學(xué)生對(duì)老師的評(píng)教信 息進(jìn)行匯總，再對(duì)每位老師進(jìn)行綜合評(píng)定。息進(jìn)行匯總，再對(duì)每位老師進(jìn)行綜合評(píng)定。 這些信息將記錄在數(shù)據(jù)庫(kù)的這些信息將記錄在數(shù)據(jù)庫(kù)的 tblRemarks 表中，教務(wù)部門根據(jù)網(wǎng)上的表中，教務(wù)部門根據(jù)網(wǎng)上的 數(shù)據(jù)開展工作。他們開始匯總每位老師的評(píng)價(jià)信息，該數(shù)據(jù)開展工作。他們開始匯總每位老師的評(píng)價(jià)信息，該 tblRemarks 表是表是 以學(xué)生評(píng)教以學(xué)生評(píng)教ID為主鍵的，中間存儲(chǔ)的是同學(xué)對(duì)老師的不同信息。為主鍵的，中間存儲(chǔ)的是同學(xué)對(duì)老師的不同信息。 該表內(nèi)容如下：該表內(nèi)容如下： 評(píng)教評(píng)教ID教師教師ID備課認(rèn)真?zhèn)湔n認(rèn)真師生互動(dòng)師生互動(dòng) 1001 3 4 200

3、7 2 4 3009 5 3 4012 4 4 管理任務(wù)：管理任務(wù)： 從從 tblRemarks 表中可以看，教務(wù)處如果直接對(duì)其匯總工作量很大，表中可以看，教務(wù)處如果直接對(duì)其匯總工作量很大， 因?yàn)樗麄儽仨氈鹨唤y(tǒng)計(jì)教師的信息。為減輕負(fù)擔(dān)，小張以因?yàn)樗麄儽仨氈鹨唤y(tǒng)計(jì)教師的信息。為減輕負(fù)擔(dān)，小張以 tblRemarks表表 收集的數(shù)據(jù)為基礎(chǔ)，以每位老師作為一條記錄進(jìn)行存儲(chǔ)，以便匯總。收集的數(shù)據(jù)為基礎(chǔ)，以每位老師作為一條記錄進(jìn)行存儲(chǔ)，以便匯總。 你能想到什么方案呢？你能想到什么方案呢？ 小張想到了如下幾種方案：小張想到了如下幾種方案： 1、直接對(duì)、直接對(duì) tblRemarks 表進(jìn)行修改。（好與壞？）

4、表進(jìn)行修改。（好與壞？） 2、創(chuàng)建一個(gè)用戶定義函數(shù)，以實(shí)現(xiàn)參數(shù)化的視圖功能；（好與壞？）、創(chuàng)建一個(gè)用戶定義函數(shù)，以實(shí)現(xiàn)參數(shù)化的視圖功能；（好與壞？） 3、創(chuàng)建一個(gè)存儲(chǔ)過程，以實(shí)現(xiàn)對(duì)現(xiàn)有的評(píng)教信息進(jìn)行匯總。（好與壞？）、創(chuàng)建一個(gè)存儲(chǔ)過程，以實(shí)現(xiàn)對(duì)現(xiàn)有的評(píng)教信息進(jìn)行匯總。（好與壞？） 小張選擇了第三種方案，創(chuàng)建了小張選擇了第三種方案，創(chuàng)建了spStatRemarks存儲(chǔ)過程，在過程存儲(chǔ)過程，在過程 中創(chuàng)建了中創(chuàng)建了tblStatRemarks表，并將其插入統(tǒng)計(jì)后的評(píng)教信息。該表以教表，并將其插入統(tǒng)計(jì)后的評(píng)教信息。該表以教 師師ID為主鍵，以實(shí)現(xiàn)各位教師評(píng)分記錄的單一性。為主鍵，以實(shí)現(xiàn)各位教師評(píng)分記

5、錄的單一性。 存儲(chǔ)過程：存儲(chǔ)過程： 為完成特定的功能而匯集在一起的一組為完成特定的功能而匯集在一起的一組TSQL程序語(yǔ)句，經(jīng)編譯后程序語(yǔ)句，經(jīng)編譯后 存儲(chǔ)在數(shù)據(jù)庫(kù)中可重用的存儲(chǔ)在數(shù)據(jù)庫(kù)中可重用的TSQL模塊或例程。模塊或例程。 提問：提問： 在程序開發(fā)中，有時(shí)需要編寫數(shù)百行在程序開發(fā)中，有時(shí)需要編寫數(shù)百行T-SQL 語(yǔ)句來(lái)訪問數(shù)據(jù)庫(kù)中的語(yǔ)句來(lái)訪問數(shù)據(jù)庫(kù)中的 數(shù)據(jù)，這些代碼在程序中不僅破壞了程序的可讀性，而且為將來(lái)應(yīng)用程數(shù)據(jù)，這些代碼在程序中不僅破壞了程序的可讀性，而且為將來(lái)應(yīng)用程 序的修改和維護(hù)帶來(lái)很多不便。有沒有種方法能封裝這些語(yǔ)句，使其作序的修改和維護(hù)帶來(lái)很多不便。有沒有種方法能封裝這些語(yǔ)

6、句，使其作 為一個(gè)集合出現(xiàn)呢？為一個(gè)集合出現(xiàn)呢？ 存儲(chǔ)過程可以接受輸入?yún)?shù)、向客戶端返回表格或標(biāo)量結(jié)果和消息、存儲(chǔ)過程可以接受輸入?yún)?shù)、向客戶端返回表格或標(biāo)量結(jié)果和消息、 調(diào)用數(shù)據(jù)定義語(yǔ)言調(diào)用數(shù)據(jù)定義語(yǔ)言 （DDL） 和數(shù)據(jù)操作語(yǔ)言和數(shù)據(jù)操作語(yǔ)言 （DML） 語(yǔ)句，然后返回語(yǔ)句，然后返回 輸出參數(shù)。輸出參數(shù)。 存儲(chǔ)過程的類型：存儲(chǔ)過程的類型： 在在 Microsoft SQL Server 2008中有多種可用的存儲(chǔ)過程。本節(jié)簡(jiǎn)中有多種可用的存儲(chǔ)過程。本節(jié)簡(jiǎn) 要介紹每種存儲(chǔ)過程。要介紹每種存儲(chǔ)過程。 1、用戶定義的存儲(chǔ)過程、用戶定義的存儲(chǔ)過程 2、系統(tǒng)存儲(chǔ)過程：、系統(tǒng)存儲(chǔ)過程： SQL Ser

7、ver 中的許多管理活動(dòng)都是通過一種特殊的存儲(chǔ)過程執(zhí)行的，中的許多管理活動(dòng)都是通過一種特殊的存儲(chǔ)過程執(zhí)行的， 這種存儲(chǔ)過程被稱為系統(tǒng)存儲(chǔ)過程。例如，這種存儲(chǔ)過程被稱為系統(tǒng)存儲(chǔ)過程。例如，sys.sp_changedbowner 就是就是 一個(gè)系統(tǒng)存儲(chǔ)過程。一個(gè)系統(tǒng)存儲(chǔ)過程。 3、擴(kuò)展存儲(chǔ)過程：、擴(kuò)展存儲(chǔ)過程： SQL Server 支持在支持在 SQL Server 和外部程序之間提供一個(gè)接口以實(shí)和外部程序之間提供一個(gè)接口以實(shí) 現(xiàn)各種維護(hù)活動(dòng)的系統(tǒng)存儲(chǔ)過程。這些擴(kuò)展存儲(chǔ)程序使用現(xiàn)各種維護(hù)活動(dòng)的系統(tǒng)存儲(chǔ)過程。這些擴(kuò)展存儲(chǔ)程序使用 xp_ 前綴。前綴。 存儲(chǔ)過程的優(yōu)點(diǎn)：存儲(chǔ)過程的優(yōu)點(diǎn)： 在在 SQ

8、L Server 中使用存儲(chǔ)過程而不使用存儲(chǔ)在客戶端計(jì)算機(jī)本地的中使用存儲(chǔ)過程而不使用存儲(chǔ)在客戶端計(jì)算機(jī)本地的 Transact-SQL 程序的優(yōu)點(diǎn)包括：程序的優(yōu)點(diǎn)包括： （1）存儲(chǔ)過程已在服務(wù)器注冊(cè)。）存儲(chǔ)過程已在服務(wù)器注冊(cè)。 （2）存儲(chǔ)過程具有安全特性（例如權(quán)限）和所有權(quán)鏈接，以及可以附加）存儲(chǔ)過程具有安全特性（例如權(quán)限）和所有權(quán)鏈接，以及可以附加 到它們的證書。到它們的證書。 （3）存儲(chǔ)過程可以強(qiáng)制應(yīng)用程序的安全性。）存儲(chǔ)過程可以強(qiáng)制應(yīng)用程序的安全性。 （4）存儲(chǔ)過程允許模塊化程序設(shè)計(jì)。）存儲(chǔ)過程允許模塊化程序設(shè)計(jì)。 （5）存儲(chǔ)過程是命名代碼，允許延遲綁定。）存儲(chǔ)過程是命名代碼，允許延遲

9、綁定。 （6）存儲(chǔ)過程可以減少網(wǎng)絡(luò)通信流量。）存儲(chǔ)過程可以減少網(wǎng)絡(luò)通信流量。 創(chuàng)建存儲(chǔ)過程：創(chuàng)建存儲(chǔ)過程： 在在SQL Server中，可以使用三種方法創(chuàng)建存儲(chǔ)過程：中，可以使用三種方法創(chuàng)建存儲(chǔ)過程： （1）使用）使用 T-SQL 語(yǔ)句創(chuàng)建存儲(chǔ)過程；語(yǔ)句創(chuàng)建存儲(chǔ)過程； （2）使用創(chuàng)建存儲(chǔ)過程模板創(chuàng)建存儲(chǔ)過程）使用創(chuàng)建存儲(chǔ)過程模板創(chuàng)建存儲(chǔ)過程； （3）利用）利用SQL Server 管理平臺(tái)創(chuàng)建存儲(chǔ)過程。管理平臺(tái)創(chuàng)建存儲(chǔ)過程。 使用使用 T-SQL 語(yǔ)句創(chuàng)建存儲(chǔ)過程：語(yǔ)句創(chuàng)建存儲(chǔ)過程： 存儲(chǔ)過程是使用存儲(chǔ)過程是使用 CREATE PROCEDURE 語(yǔ)句創(chuàng)建的。它們只能創(chuàng)語(yǔ)句創(chuàng)建的。它們只能創(chuàng)

10、建在當(dāng)前的數(shù)據(jù)庫(kù)中，但臨時(shí)的存儲(chǔ)過程除外，它們創(chuàng)建在建在當(dāng)前的數(shù)據(jù)庫(kù)中，但臨時(shí)的存儲(chǔ)過程除外，它們創(chuàng)建在 tempdb 數(shù)數(shù) 據(jù)庫(kù)中。據(jù)庫(kù)中。 使用使用 T-SQL 語(yǔ)句創(chuàng)建存儲(chǔ)過程的語(yǔ)法：語(yǔ)句創(chuàng)建存儲(chǔ)過程的語(yǔ)法： CREATE PROC|PROCEDURE schema_cedure_name;number parameter type_schema_name. data_type VARYING =default OUTPUT,.n WITH ,.n FOR REPLICATION AS ;.n|; := ENCRYPTION RECOMPILE EXECUTE_AS_Cl

11、ause - 創(chuàng)建名為創(chuàng)建名為Production.LongLeadProducts 的存儲(chǔ)過程，的存儲(chǔ)過程， - 實(shí)現(xiàn)在實(shí)現(xiàn)在Production.Product 表中查詢制造時(shí)間在一天以上的所表中查詢制造時(shí)間在一天以上的所 有產(chǎn)品的名稱及產(chǎn)品號(hào)的功能有產(chǎn)品的名稱及產(chǎn)品號(hào)的功能 CREATE PROCEDURE Production.LongLeadProducts AS SELECTName, ProductNumber FROM Production.Product WHEREDaysToManufacture = 1 GO 舉例舉例9： 在在 Production 架構(gòu)中創(chuàng)建名為架構(gòu)中

12、創(chuàng)建名為 LongLeadProducts 的的存儲(chǔ)過程，該過存儲(chǔ)過程，該過 程返回制造時(shí)間在一天以上的所有產(chǎn)品程返回制造時(shí)間在一天以上的所有產(chǎn)品。 使用創(chuàng)建存儲(chǔ)過程模板創(chuàng)建存儲(chǔ)過程：使用創(chuàng)建存儲(chǔ)過程模板創(chuàng)建存儲(chǔ)過程： 在在SQL Server 管理平臺(tái)中，選擇工具欄中的管理平臺(tái)中，選擇工具欄中的模板資源資源管理模板資源資源管理 器器，出現(xiàn)，出現(xiàn)模板資源管理器模板資源管理器窗口，選擇窗口，選擇存儲(chǔ)過程（存儲(chǔ)過程（stored procedure）中的中的創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程選項(xiàng)，如圖所示。選項(xiàng)，如圖所示。 在文本框中可以輸入創(chuàng)建存儲(chǔ)過程的在文本框中可以輸入創(chuàng)建存儲(chǔ)過程的Transact_S

13、QL語(yǔ)句，單擊語(yǔ)句，單擊 【執(zhí)行執(zhí)行】按鈕，即可創(chuàng)建該存儲(chǔ)過程。按鈕，即可創(chuàng)建該存儲(chǔ)過程。 利用利用SQL Server 管理平臺(tái)創(chuàng)建存儲(chǔ)過程：管理平臺(tái)創(chuàng)建存儲(chǔ)過程： 在在SQL Server管理平臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫(kù)，然后展開管理平臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫(kù)，然后展開 可編程性可編程性，右單擊，右單擊存儲(chǔ)過程存儲(chǔ)過程選項(xiàng)，在彈出的快捷菜單中選擇選項(xiàng)，在彈出的快捷菜單中選擇 新建存儲(chǔ)過程新建存儲(chǔ)過程選項(xiàng)，如圖所示，出現(xiàn)創(chuàng)建存儲(chǔ)過程窗口。選項(xiàng)，如圖所示，出現(xiàn)創(chuàng)建存儲(chǔ)過程窗口。 在文本框中輸入創(chuàng)建存儲(chǔ)過程的在文本框中輸入創(chuàng)建存儲(chǔ)過程的 Transact_SQL 語(yǔ)句，單擊語(yǔ)句，單擊【

14、執(zhí)行執(zhí)行】 按鈕，即可創(chuàng)建該存儲(chǔ)過程。按鈕，即可創(chuàng)建該存儲(chǔ)過程。 舉例舉例10：在在 adventureworks 數(shù)據(jù)庫(kù)中數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)帶有創(chuàng)建一個(gè)帶有SELECT語(yǔ)句的簡(jiǎn)語(yǔ)句的簡(jiǎn) 單過程（單過程（ au_infor_all ），該存儲(chǔ)過程返回所有員工姓名，），該存儲(chǔ)過程返回所有員工姓名，Email地址，地址， 電話。該存儲(chǔ)過程不使用任何參數(shù)。電話。該存儲(chǔ)過程不使用任何參數(shù)。 程序清單如下：程序清單如下： USE adventureworks GO CREATE PROCEDURE au_infor_all AS SELECT lastname, firstname, emailaddre

15、ss, phone FROM person.contact GO 創(chuàng)建存儲(chǔ)過程的準(zhǔn)則：創(chuàng)建存儲(chǔ)過程的準(zhǔn)則： 限定存儲(chǔ)過程所引用的對(duì)象名稱限定存儲(chǔ)過程所引用的對(duì)象名稱 每個(gè)任務(wù)創(chuàng)建一個(gè)存儲(chǔ)過程每個(gè)任務(wù)創(chuàng)建一個(gè)存儲(chǔ)過程 創(chuàng)建，測(cè)試存儲(chǔ)過程，并對(duì)其進(jìn)行故障診斷創(chuàng)建，測(cè)試存儲(chǔ)過程，并對(duì)其進(jìn)行故障診斷 存儲(chǔ)過程名稱避免使用存儲(chǔ)過程名稱避免使用 sp_ 前綴前綴 對(duì)所有存儲(chǔ)過程使用相同的連接設(shè)置對(duì)所有存儲(chǔ)過程使用相同的連接設(shè)置 盡可能減少臨時(shí)存儲(chǔ)過程的使用盡可能減少臨時(shí)存儲(chǔ)過程的使用 執(zhí)行存儲(chǔ)過程：執(zhí)行存儲(chǔ)過程： 可以使用可以使用 Transact-SQL EXECUTE 語(yǔ)句來(lái)運(yùn)行存儲(chǔ)過程。語(yǔ)句來(lái)運(yùn)行存

16、儲(chǔ)過程。 執(zhí)行存儲(chǔ)過程必須具有執(zhí)行存儲(chǔ)過程的權(quán)限許可，才可以直接執(zhí)行存執(zhí)行存儲(chǔ)過程必須具有執(zhí)行存儲(chǔ)過程的權(quán)限許可，才可以直接執(zhí)行存 儲(chǔ)過程，執(zhí)行存儲(chǔ)過程可使用儲(chǔ)過程，執(zhí)行存儲(chǔ)過程可使用EXECUTE命令來(lái)執(zhí)行，語(yǔ)法形式如下：命令來(lái)執(zhí)行，語(yǔ)法形式如下： EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE 執(zhí)行存儲(chǔ)過程：執(zhí)行存儲(chǔ)過程： 針對(duì)例針對(duì)例9：調(diào)用執(zhí)行存儲(chǔ)過程的示例：調(diào)用執(zhí)行存儲(chǔ)過程的示例： 調(diào)用調(diào)用

17、 LongLeadProducts 存儲(chǔ)過程。存儲(chǔ)過程。 EXECUTE Production.LongLeadProducts 針對(duì)例針對(duì)例10：執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程au_infor_all。 EXECUTE（EXEC） au_infor_all 查看查看、修改、重命名和刪除存儲(chǔ)過程：、修改、重命名和刪除存儲(chǔ)過程： 使用使用SQL Server管理平臺(tái)查看用戶創(chuàng)建的存儲(chǔ)過程；管理平臺(tái)查看用戶創(chuàng)建的存儲(chǔ)過程； 使用系統(tǒng)存儲(chǔ)過程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過程使用系統(tǒng)存儲(chǔ)過程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過程 。 使用使用SQL Server管理平臺(tái)查看用戶創(chuàng)建的存儲(chǔ)過程：管理平臺(tái)查看用戶創(chuàng)建的存儲(chǔ)過程： 在

18、在SQL 平臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫(kù)，選擇并依次展開平臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫(kù)，選擇并依次展開 可編程性可編程性存儲(chǔ)過程存儲(chǔ)過程，然后右擊要查看的存儲(chǔ)過程名稱，如，然后右擊要查看的存儲(chǔ)過程名稱，如 圖所示，從彈出的快捷菜單中，選擇圖所示，從彈出的快捷菜單中，選擇編寫存儲(chǔ)過程腳本為編寫存儲(chǔ)過程腳本為 CREATE到到新查詢編輯器窗口新查詢編輯器窗口，則可以看到存儲(chǔ)過程的源，則可以看到存儲(chǔ)過程的源 代碼。代碼。 使用系統(tǒng)存儲(chǔ)過程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過程使用系統(tǒng)存儲(chǔ)過程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過程 ： 可供使用的系統(tǒng)存儲(chǔ)過程及其語(yǔ)法形式如下：可供使用的系統(tǒng)存儲(chǔ)過程及其語(yǔ)法形式如下： sp_h

19、elp，用于顯示存儲(chǔ)過程的參數(shù)及其數(shù)據(jù)類型。，用于顯示存儲(chǔ)過程的參數(shù)及其數(shù)據(jù)類型。 其語(yǔ)法為：其語(yǔ)法為： sp_help objname= name sp_helptext，用于顯示存儲(chǔ)過程的源代碼。，用于顯示存儲(chǔ)過程的源代碼。 其語(yǔ)法為：其語(yǔ)法為： sp_helptext objname= name sp_depends，用于顯示和存儲(chǔ)過程相關(guān)的數(shù)據(jù)庫(kù)對(duì)象。，用于顯示和存儲(chǔ)過程相關(guān)的數(shù)據(jù)庫(kù)對(duì)象。 其語(yǔ)法為：其語(yǔ)法為： sp_depends objname=object sp_stored_procedures，用于返回當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過程列表。，用于返回當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過程列表。 其語(yǔ)法

20、為：其語(yǔ)法為： sp_stored_proceduressp_name=name ,sp_owner=owner ,sp_qualifier = qualifier 修改存儲(chǔ)過程語(yǔ)法形式如下：修改存儲(chǔ)過程語(yǔ)法形式如下： ALTER PROCEDURE procedure_name;number parameter data_type VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n 修改存儲(chǔ)過程：修改存儲(chǔ)過程： 存儲(chǔ)過程可以根據(jù)用

21、戶的要求或者基表定義的改變而改變。使用存儲(chǔ)過程可以根據(jù)用戶的要求或者基表定義的改變而改變。使用ALTER PROCEDURE 語(yǔ)句可以更改先前通過執(zhí)行語(yǔ)句可以更改先前通過執(zhí)行 CREATE PROCEDURE 語(yǔ)句創(chuàng)建的語(yǔ)句創(chuàng)建的 過程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過程或觸發(fā)器。過程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過程或觸發(fā)器。 舉例舉例11：創(chuàng)建了一個(gè)名為創(chuàng)建了一個(gè)名為 proc_person 的存儲(chǔ)過程，該存儲(chǔ)過程包含的存儲(chǔ)過程，該存儲(chǔ)過程包含 姓名和姓名和 Email 地址信息。然后，用地址信息。然后，用 ALTER PROCEDURE 重新定義了重新定義了 該存儲(chǔ)過程，使之只

22、包含姓名信息，并使用該存儲(chǔ)過程，使之只包含姓名信息，并使用 ENCRYPTION 關(guān)鍵字使之關(guān)鍵字使之 無(wú)法通過查看無(wú)法通過查看syscomments表來(lái)查看存儲(chǔ)過程的內(nèi)容。表來(lái)查看存儲(chǔ)過程的內(nèi)容。 程序清單如下：程序清單如下： USE adventureworks GO /*創(chuàng)建一個(gè)存儲(chǔ)過程，該存儲(chǔ)過程包含姓名和Email地址信息*/ CREATE PROCEDURE proc_person AS SELECT firstname, lastname, emailaddress FROM person.contact ORDER BY lastname, firstname GO 下面對(duì)該存

23、儲(chǔ)過程進(jìn)行重新定義。使之只包含姓名信息，并使用下面對(duì)該存儲(chǔ)過程進(jìn)行重新定義。使之只包含姓名信息，并使用 ENCRYPTION 關(guān)鍵字使之無(wú)法通過查看關(guān)鍵字使之無(wú)法通過查看 syscomments 表來(lái)查看存表來(lái)查看存 儲(chǔ)過程的內(nèi)容。儲(chǔ)過程的內(nèi)容。 程序清單如下：程序清單如下： ALTER PROCEDURE proc_person WITH ENCRYPTION AS SELECT firstname, lastname FROM person.contact ORDER BY lastname, firstname GO 重命名存儲(chǔ)過程：重命名存儲(chǔ)過程： 1、修改存儲(chǔ)過程的名稱可以使用系統(tǒng)存

24、儲(chǔ)過程修改存儲(chǔ)過程的名稱可以使用系統(tǒng)存儲(chǔ)過程 sp_rename，其語(yǔ)法為：，其語(yǔ)法為： sp_rename 原存儲(chǔ)過程名稱，新存儲(chǔ)過程名稱原存儲(chǔ)過程名稱，新存儲(chǔ)過程名稱 2、通過、通過SQL Server管理平臺(tái)也可以修改存儲(chǔ)過程的名稱。在管理平臺(tái)也可以修改存儲(chǔ)過程的名稱。在SQL 管理管理 平臺(tái)中，右擊要操作的存儲(chǔ)過程名稱，從彈出的快捷菜單中選擇平臺(tái)中，右擊要操作的存儲(chǔ)過程名稱，從彈出的快捷菜單中選擇重命重命 名名選項(xiàng)，當(dāng)存儲(chǔ)過程名稱變成可輸入狀態(tài)時(shí)，就可以直接修改該存儲(chǔ)選項(xiàng)，當(dāng)存儲(chǔ)過程名稱變成可輸入狀態(tài)時(shí)，就可以直接修改該存儲(chǔ) 過程的名稱。過程的名稱。 刪除存儲(chǔ)過程：刪除存儲(chǔ)過程： 1、

25、刪除存儲(chǔ)過程可以使用刪除存儲(chǔ)過程可以使用DROP命令，命令，DROP命令可以將一個(gè)或者多個(gè)命令可以將一個(gè)或者多個(gè) 存儲(chǔ)過程或者存儲(chǔ)過程組從當(dāng)前數(shù)據(jù)庫(kù)中刪除，其語(yǔ)法形式如下：存儲(chǔ)過程或者存儲(chǔ)過程組從當(dāng)前數(shù)據(jù)庫(kù)中刪除，其語(yǔ)法形式如下： drop procedure procedure,n 2、利用、利用SQL 管理平臺(tái)刪除存儲(chǔ)過程。管理平臺(tái)刪除存儲(chǔ)過程。 在在SQL Server管理平臺(tái)中，右擊要?jiǎng)h除的存儲(chǔ)過程，從彈出的快管理平臺(tái)中，右擊要?jiǎng)h除的存儲(chǔ)過程，從彈出的快 捷菜單中選擇捷菜單中選擇刪除刪除選項(xiàng)，則會(huì)彈出選項(xiàng)，則會(huì)彈出刪除刪除對(duì)象對(duì)話框，在該對(duì)話對(duì)象對(duì)話框，在該對(duì)話 框中，單擊框中，單擊“

26、確定確定”按鈕，即可完成刪除操作。按鈕，即可完成刪除操作。 創(chuàng)建帶有參數(shù)的存儲(chǔ)過程：創(chuàng)建帶有參數(shù)的存儲(chǔ)過程： 提問：提問： 要進(jìn)入一個(gè)網(wǎng)站，經(jīng)常需要先進(jìn)行身份驗(yàn)證。我們是否可以通過存要進(jìn)入一個(gè)網(wǎng)站，經(jīng)常需要先進(jìn)行身份驗(yàn)證。我們是否可以通過存 儲(chǔ)過程和應(yīng)用程序進(jìn)行交互，實(shí)現(xiàn)所需的身份驗(yàn)證登錄模塊呢？?jī)?chǔ)過程和應(yīng)用程序進(jìn)行交互，實(shí)現(xiàn)所需的身份驗(yàn)證登錄模塊呢？ 如果參數(shù)作為過程定義的一部分包含在存儲(chǔ)過程內(nèi)，則存儲(chǔ)過程如果參數(shù)作為過程定義的一部分包含在存儲(chǔ)過程內(nèi)，則存儲(chǔ)過程 更為靈活，因此可創(chuàng)建更通用的應(yīng)用程序的邏輯。上面的問題也會(huì)迎刃更為靈活，因此可創(chuàng)建更通用的應(yīng)用程序的邏輯。上面的問題也會(huì)迎刃 而解

27、了。而解了。 場(chǎng)景：場(chǎng)景： 在上個(gè)場(chǎng)景中，由于在上個(gè)場(chǎng)景中，由于tblRemarks表做了很大的改動(dòng)，數(shù)據(jù)管理員表做了很大的改動(dòng)，數(shù)據(jù)管理員 刪除現(xiàn)有的存儲(chǔ)過程并重新進(jìn)行設(shè)計(jì)。刪除現(xiàn)有的存儲(chǔ)過程并重新進(jìn)行設(shè)計(jì)。 新建的存儲(chǔ)過程要求根據(jù)新的表設(shè)置相應(yīng)的參數(shù)。根據(jù)應(yīng)用程序新建的存儲(chǔ)過程要求根據(jù)新的表設(shè)置相應(yīng)的參數(shù)。根據(jù)應(yīng)用程序 的功能需求進(jìn)行設(shè)計(jì)。的功能需求進(jìn)行設(shè)計(jì)。 設(shè)計(jì)的邏輯是：根據(jù)參數(shù)設(shè)計(jì)的邏輯是：根據(jù)參數(shù)教師教師ID和和評(píng)選年份查找評(píng)選年份查找 tblRemarks 表中是否存在教師評(píng)選記錄。若有，則將新評(píng)分同表中的相應(yīng)記錄均更表中是否存在教師評(píng)選記錄。若有，則將新評(píng)分同表中的相應(yīng)記錄均更

28、 新至表中；若沒有，則將教師的評(píng)分記錄添加到表中。新至表中；若沒有，則將教師的評(píng)分記錄添加到表中。 表 dbo.tblremarks 列名數(shù)據(jù)類型允許空 教師IDint 評(píng)選年份int 備課認(rèn)真int 師生互動(dòng)int 存儲(chǔ)過程最多支持存儲(chǔ)過程最多支持2100個(gè)參數(shù)，通過由這些參數(shù)組成的列表與調(diào)用個(gè)參數(shù)，通過由這些參數(shù)組成的列表與調(diào)用 該過程的程序進(jìn)行通信。輸入?yún)?shù)允許信息傳入存儲(chǔ)過程，這些值可用該過程的程序進(jìn)行通信。輸入?yún)?shù)允許信息傳入存儲(chǔ)過程，這些值可用 作過程中的局部變量。作過程中的局部變量。 使用輸入?yún)?shù)的準(zhǔn)則：使用輸入?yún)?shù)的準(zhǔn)則： 若要定義接受輸入?yún)?shù)的存儲(chǔ)過程，應(yīng)在若要定義接受輸入?yún)?/p>

29、數(shù)的存儲(chǔ)過程，應(yīng)在 CREATE PROCEDURE 語(yǔ)語(yǔ) 句中聲明一個(gè)或多個(gè)變量作為參數(shù)。使用輸入?yún)?shù)時(shí)，應(yīng)考慮以下準(zhǔn)則：句中聲明一個(gè)或多個(gè)變量作為參數(shù)。使用輸入?yún)?shù)時(shí)，應(yīng)考慮以下準(zhǔn)則： 根據(jù)情況相應(yīng)地為參數(shù)提供默認(rèn)值。根據(jù)情況相應(yīng)地為參數(shù)提供默認(rèn)值。 在存儲(chǔ)過程的開頭驗(yàn)證所有傳入的參數(shù)值，以盡早查出缺少的值和無(wú)在存儲(chǔ)過程的開頭驗(yàn)證所有傳入的參數(shù)值，以盡早查出缺少的值和無(wú) 效值，包括檢查參數(shù)是否為空。效值，包括檢查參數(shù)是否為空。 注意：注意： 默認(rèn)的參數(shù)必須是常量或默認(rèn)的參數(shù)必須是常量或 NULL，在指定的，在指定的 NULL 作為參數(shù)的默認(rèn)作為參數(shù)的默認(rèn) 值時(shí)，必須使用值時(shí)，必須使用“NU

30、LL”，不能使用，不能使用“ IS NULL ”。 輸出參數(shù)：輸出參數(shù)： 輸出參數(shù)允許保留因存儲(chǔ)過程的執(zhí)行而產(chǎn)生的對(duì)該參數(shù)的任何修改，輸出參數(shù)允許保留因存儲(chǔ)過程的執(zhí)行而產(chǎn)生的對(duì)該參數(shù)的任何修改， 即使是在存儲(chǔ)過程執(zhí)行完畢之后。在即使是在存儲(chǔ)過程執(zhí)行完畢之后。在 T-SQL 中使用輸出參數(shù)，必須在中使用輸出參數(shù)，必須在 CREATE PROCEDURE 和和 EXECUTE 語(yǔ)句中同時(shí)指定語(yǔ)句中同時(shí)指定 OUTPUT 關(guān)關(guān) 鍵字。鍵字。 若省略了若省略了OUTPUT關(guān)鍵字，存儲(chǔ)過程仍會(huì)執(zhí)行，但不會(huì)返回修改的關(guān)鍵字，存儲(chǔ)過程仍會(huì)執(zhí)行，但不會(huì)返回修改的 值。在大多數(shù)客戶的編程語(yǔ)言中值。在大多數(shù)客戶的

31、編程語(yǔ)言中，參數(shù)方向默認(rèn)為輸入，因此必須在客參數(shù)方向默認(rèn)為輸入，因此必須在客 戶端指定參數(shù)的方向。戶端指定參數(shù)的方向。 舉例舉例12：創(chuàng)建一個(gè)存儲(chǔ)過程，以簡(jiǎn)化對(duì)創(chuàng)建一個(gè)存儲(chǔ)過程，以簡(jiǎn)化對(duì)sc表的數(shù)據(jù)添加工作，使得在執(zhí)行表的數(shù)據(jù)添加工作，使得在執(zhí)行 該存儲(chǔ)過程時(shí)，其參數(shù)值（該存儲(chǔ)過程時(shí)，其參數(shù)值（Param1,Param2,Param3）作為數(shù)據(jù)添）作為數(shù)據(jù)添 加到表中。（其數(shù)據(jù)類型為加到表中。（其數(shù)據(jù)類型為char(10) ,char(2) ,real ） 程序清單如下：程序清單如下： CREATE PROCEDURE dbo. pr1_sc_ins Param1 char(10) , Par

32、am2 char(2) , Param3 real AS BEGIN insert into sc(sno,cno,score) values(Param1,Param2,Param3) END 舉例舉例13：在在AdventureWorks創(chuàng)建一個(gè)帶有參數(shù)的存儲(chǔ)過程創(chuàng)建一個(gè)帶有參數(shù)的存儲(chǔ)過程GetEmployees ，從，從 視圖中返回指定的雇員（提供名和姓視圖中返回指定的雇員（提供名和姓l(shuí)astname firstname ）及其職務(wù)和部門）及其職務(wù)和部門 名稱，該存儲(chǔ)過程接受與傳遞的參數(shù)精確匹配的值。（其數(shù)據(jù)類型為名稱，該存儲(chǔ)過程接受與傳遞的參數(shù)精確匹配的值。（其數(shù)據(jù)類型為varchar

33、(30) varchar(10) ） 程序清單如下：程序清單如下： USE AdventureWorks; GO CREATE PROCEDURE GetEmployees lastname varchar(30), firstname varchar(10) AS SELECT LastName, FirstName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName = firstname AND LastName = lastname; GO 舉例：舉例：使用使用 EXEC命令傳遞參數(shù)

34、，執(zhí)行命令傳遞參數(shù)，執(zhí)行（例（例12）定義的存儲(chǔ)過程定義的存儲(chǔ)過程 pr1_sc_ins。 sc_ins 存儲(chǔ)過程可以通過以下方法執(zhí)行：存儲(chǔ)過程可以通過以下方法執(zhí)行： EXECUTE（EXEC） pr1_sc_ins 3130040101,c1,85 當(dāng)然，在執(zhí)行過程中變量可以顯式命名：當(dāng)然，在執(zhí)行過程中變量可以顯式命名： EXEC pr1_sc_ins Param1=3130040101,Param2=c1, Param3=85 舉例：舉例： 執(zhí)行執(zhí)行（例（例13）定義的存儲(chǔ)過程定義的存儲(chǔ)過程 GetEmployees 。 GetEmployees 存儲(chǔ)過程可以通過以下方法執(zhí)行：存儲(chǔ)過程可以

35、通過以下方法執(zhí)行： EXEC GetEmployees Dull, Ann 或者或者 EXEC GetEmployees lastname = Dull, firstname = Ann 或者或者 EXEC GetEmployees firstname = Ann, lastname = Dull n存儲(chǔ)過程概述 n創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程 n執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程 n查看、重命名和刪除存儲(chǔ)過程查看、重命名和刪除存儲(chǔ)過程 n創(chuàng)建帶有參數(shù)的存儲(chǔ)過程創(chuàng)建帶有參數(shù)的存儲(chǔ)過程 n觸發(fā)器概述觸發(fā)器概述 nDML觸發(fā)器的創(chuàng)建和應(yīng)用 nDDL觸發(fā)器的創(chuàng)建和應(yīng)用 n嵌套觸發(fā)器和遞歸觸發(fā)器 n查看、修改和刪除

36、觸發(fā)器 觸發(fā)器概述：觸發(fā)器概述： 觸發(fā)器是一種特殊的存儲(chǔ)過程，它在執(zhí)行語(yǔ)言事件時(shí)自動(dòng)生效。觸發(fā)器是一種特殊的存儲(chǔ)過程，它在執(zhí)行語(yǔ)言事件時(shí)自動(dòng)生效。 SQL Server2005 包括兩大類觸發(fā)器：包括兩大類觸發(fā)器：DML 觸發(fā)器和觸發(fā)器和 DDL 觸發(fā)器。觸發(fā)器。 （1）DML 觸發(fā)器在數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言觸發(fā)器在數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言 （DML） 事件時(shí)將啟事件時(shí)將啟 用。用。DML 事件包括在指定表或視圖中修改數(shù)據(jù)的事件包括在指定表或視圖中修改數(shù)據(jù)的 INSERT 語(yǔ)句、語(yǔ)句、 UPDATE 語(yǔ)句或語(yǔ)句或 DELETE 語(yǔ)句。語(yǔ)句。 DML 觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的觸發(fā)

37、器可以查詢其他表，還可以包含復(fù)雜的 Transact-SQL 語(yǔ)句。將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。語(yǔ)句。將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。 如果檢測(cè)到錯(cuò)誤，則整個(gè)事務(wù)即自動(dòng)回滾。如果檢測(cè)到錯(cuò)誤，則整個(gè)事務(wù)即自動(dòng)回滾。 （2）DDL 觸發(fā)器是觸發(fā)器是 SQL Server 2005 的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)的新增功能。當(dāng)服務(wù)器或數(shù)據(jù) 庫(kù)中發(fā)生數(shù)據(jù)定義語(yǔ)言庫(kù)中發(fā)生數(shù)據(jù)定義語(yǔ)言 （DDL） 事件時(shí)將調(diào)用這些觸發(fā)器。事件時(shí)將調(diào)用這些觸發(fā)器。 DML觸發(fā)器的創(chuàng)建和應(yīng)用：觸發(fā)器的創(chuàng)建和應(yīng)用： 當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言 （DM

38、L） 事件時(shí)將調(diào)用事件時(shí)將調(diào)用 DML 觸發(fā)器。觸發(fā)器。 從而確保對(duì)數(shù)據(jù)的處理必須符合由這些從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語(yǔ)句所定義的規(guī)則。語(yǔ)句所定義的規(guī)則。 DML 觸發(fā)器的主要優(yōu)點(diǎn)如下：觸發(fā)器的主要優(yōu)點(diǎn)如下： （1）DML 觸發(fā)器可通過數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。觸發(fā)器可通過數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。 （2）DML 觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采 取措施。取措施。 （3）DML 觸發(fā)器可以防止惡意或錯(cuò)誤的觸發(fā)器可以防止惡意或錯(cuò)誤的 INSERT、UPDATE 以及以及 DELETE 操作，并強(qiáng)制執(zhí)行比操作

39、，并強(qiáng)制執(zhí)行比 CHECK 約束定義的限制更為復(fù)雜的其他約束定義的限制更為復(fù)雜的其他 限制。限制。 與與 CHECK 約束不同，約束不同，DML 觸發(fā)器可以引用其他表中的列。觸發(fā)器可以引用其他表中的列。 提問：提問： 很多時(shí)候改動(dòng)一個(gè)數(shù)據(jù)往往會(huì)對(duì)其它數(shù)據(jù)產(chǎn)生影響。當(dāng)用戶提交數(shù)很多時(shí)候改動(dòng)一個(gè)數(shù)據(jù)往往會(huì)對(duì)其它數(shù)據(jù)產(chǎn)生影響。當(dāng)用戶提交數(shù) 據(jù)時(shí)，能否根據(jù)數(shù)據(jù)內(nèi)容立刻對(duì)數(shù)據(jù)庫(kù)中的其他數(shù)據(jù)進(jìn)行操作？據(jù)時(shí)，能否根據(jù)數(shù)據(jù)內(nèi)容立刻對(duì)數(shù)據(jù)庫(kù)中的其他數(shù)據(jù)進(jìn)行操作？ 當(dāng)創(chuàng)建一個(gè)觸發(fā)器時(shí)必須指定如下選項(xiàng)：當(dāng)創(chuàng)建一個(gè)觸發(fā)器時(shí)必須指定如下選項(xiàng)： （1）名稱；）名稱； （2）在其上定義觸發(fā)器的表；）在其上定義觸發(fā)器的表；

40、（3）觸發(fā)器將何時(shí)激發(fā)；）觸發(fā)器將何時(shí)激發(fā)； （4）激活觸發(fā)器的數(shù)據(jù)修改語(yǔ)句，有效選項(xiàng)為）激活觸發(fā)器的數(shù)據(jù)修改語(yǔ)句，有效選項(xiàng)為 INSERT、UPDATE 或或 DELETE，多個(gè)數(shù)據(jù)修改語(yǔ)句可激活同一個(gè)觸發(fā)器；，多個(gè)數(shù)據(jù)修改語(yǔ)句可激活同一個(gè)觸發(fā)器； （5）執(zhí)行觸發(fā)操作的編程語(yǔ)句。）執(zhí)行觸發(fā)操作的編程語(yǔ)句。 DML 觸發(fā)器使用觸發(fā)器使用 deleted 和和 inserted 邏輯表。它們?cè)诮Y(jié)構(gòu)上和觸邏輯表。它們?cè)诮Y(jié)構(gòu)上和觸 發(fā)器所在的表的結(jié)構(gòu)相同，發(fā)器所在的表的結(jié)構(gòu)相同，SQL Server 會(huì)自動(dòng)創(chuàng)建和管理這些表。會(huì)自動(dòng)創(chuàng)建和管理這些表。 可以使用這兩個(gè)臨時(shí)的駐留內(nèi)存的表測(cè)試某些數(shù)據(jù)修改的

41、效果及設(shè)可以使用這兩個(gè)臨時(shí)的駐留內(nèi)存的表測(cè)試某些數(shù)據(jù)修改的效果及設(shè) 置觸發(fā)器操作的條件。置觸發(fā)器操作的條件。 Deleted表用于存儲(chǔ)表用于存儲(chǔ)delete，update語(yǔ)句所影響的行的副本。在執(zhí)語(yǔ)句所影響的行的副本。在執(zhí) 行行delete或或 update 語(yǔ)句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)秸Z(yǔ)句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表表 中。中。 Inserted 表用于存儲(chǔ)表用于存儲(chǔ) Insert 或或 update 語(yǔ)句所影響的行的副本，在語(yǔ)句所影響的行的副本，在 一個(gè)插入或更新事務(wù)處理中，新建的行被同時(shí)添加到一個(gè)插入或更新事務(wù)處理中，新建的行被同時(shí)添加到 Inserted 表

42、和表和 觸發(fā)器表中。觸發(fā)器表中。Inserted 表中的行是觸發(fā)器表中新行的副本。表中的行是觸發(fā)器表中新行的副本。 使用使用SQL Server管理平臺(tái)創(chuàng)建觸發(fā)器的過程如下：管理平臺(tái)創(chuàng)建觸發(fā)器的過程如下： 在在SQL Server管理平臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫(kù)項(xiàng)，然后展開管理平臺(tái)中，展開指定的服務(wù)器和數(shù)據(jù)庫(kù)項(xiàng)，然后展開 表，選擇并展開要在其上創(chuàng)建觸發(fā)器的表，如下圖所示，右擊觸發(fā)器選項(xiàng)。表，選擇并展開要在其上創(chuàng)建觸發(fā)器的表，如下圖所示，右擊觸發(fā)器選項(xiàng)。 從彈出的快捷菜單中選擇從彈出的快捷菜單中選擇新建觸發(fā)器新建觸發(fā)器選項(xiàng)，則會(huì)出現(xiàn)觸發(fā)器創(chuàng)選項(xiàng)，則會(huì)出現(xiàn)觸發(fā)器創(chuàng) 建窗口，如上圖所示。建窗口，

43、如上圖所示。 最后，單擊最后，單擊執(zhí)行執(zhí)行按鈕，即可成功創(chuàng)建觸發(fā)器。按鈕，即可成功創(chuàng)建觸發(fā)器。 使用使用 CREATE TRIGGER 命令創(chuàng)建命令創(chuàng)建 DML 觸發(fā)器的語(yǔ)法形式如下：觸發(fā)器的語(yǔ)法形式如下： CREATE TRIGGER schema_name. trigger_name ON table|view WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT , UPDATE , DELETE AS sql_statement ; .n 使用使用INSERT觸發(fā)器：觸發(fā)器： 場(chǎng)景場(chǎng)景： 你為銷售部門創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，用來(lái)存儲(chǔ)所有的訂貨信息。銷售經(jīng)你為銷

44、售部門創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，用來(lái)存儲(chǔ)所有的訂貨信息。銷售經(jīng) 理要實(shí)時(shí)了解庫(kù)存情況。因此用戶提交訂單時(shí)，庫(kù)存數(shù)量將會(huì)自動(dòng)減去理要實(shí)時(shí)了解庫(kù)存情況。因此用戶提交訂單時(shí)，庫(kù)存數(shù)量將會(huì)自動(dòng)減去 訂單中的數(shù)量。你覺得用什么方法比較方便呢？訂單中的數(shù)量。你覺得用什么方法比較方便呢？ INSERT觸發(fā)器通常被用來(lái)更新時(shí)間標(biāo)記字段，或者驗(yàn)證被觸發(fā)器監(jiān)控觸發(fā)器通常被用來(lái)更新時(shí)間標(biāo)記字段，或者驗(yàn)證被觸發(fā)器監(jiān)控 的字段中數(shù)據(jù)滿足要求的標(biāo)準(zhǔn)，以確保數(shù)據(jù)的完整性。的字段中數(shù)據(jù)滿足要求的標(biāo)準(zhǔn)，以確保數(shù)據(jù)的完整性。 舉例：舉例：建立一個(gè)觸發(fā)器（建立一個(gè)觸發(fā)器（sc_ins） ，當(dāng)向，當(dāng)向sc表中添加數(shù)據(jù)時(shí)，如果添加表中添加數(shù)據(jù)時(shí)

45、，如果添加 的數(shù)據(jù)與的數(shù)據(jù)與student表中的數(shù)據(jù)不匹配（沒有對(duì)應(yīng)的學(xué)號(hào)），則將此數(shù)據(jù)表中的數(shù)據(jù)不匹配（沒有對(duì)應(yīng)的學(xué)號(hào)），則將此數(shù)據(jù) 刪除。（可設(shè)變量刪除。（可設(shè)變量bh ） 程序清單如下：程序清單如下： CREATE TRIGGER sc_ins ON sc AFTER INSERT AS BEGIN DECLARE bh char(5) Select bh=Inserted.sno from Inserted If not exists(select sno from s where s.sno=bh) Delete sc where sno=bh END 使用使用UPDATE觸發(fā)器：觸

46、發(fā)器： 場(chǎng)景場(chǎng)景： 你為銷售部門創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，包含產(chǎn)品的信息。為了防止新的銷你為銷售部門創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，包含產(chǎn)品的信息。為了防止新的銷 售人員銷售庫(kù)存為零的產(chǎn)品。銷售部門希望你通過技術(shù)預(yù)防出現(xiàn)此類問售人員銷售庫(kù)存為零的產(chǎn)品。銷售部門希望你通過技術(shù)預(yù)防出現(xiàn)此類問 題。題。 你決定使用觸發(fā)器來(lái)加以預(yù)防。一旦銷售人員的操作使得庫(kù)存列中你決定使用觸發(fā)器來(lái)加以預(yù)防。一旦銷售人員的操作使得庫(kù)存列中 的數(shù)值為負(fù)值時(shí)，將執(zhí)行的數(shù)值為負(fù)值時(shí)，將執(zhí)行RAISERROR（）命令進(jìn)行報(bào)警，并將事件寫（）命令進(jìn)行報(bào)警，并將事件寫 入操作系統(tǒng)的事件日志中。入操作系統(tǒng)的事件日志中。 當(dāng)在一個(gè)有當(dāng)在一個(gè)有 UPDATE 觸

47、發(fā)器的表中修改記錄時(shí)，表中原來(lái)的記錄被觸發(fā)器的表中修改記錄時(shí)，表中原來(lái)的記錄被 移動(dòng)到刪除表中，修改過的記錄插入到了插入表中，觸發(fā)器可以參考刪除移動(dòng)到刪除表中，修改過的記錄插入到了插入表中，觸發(fā)器可以參考刪除 表和插入表以及被修改的表，以確定如何完成數(shù)據(jù)庫(kù)操作。表和插入表以及被修改的表，以確定如何完成數(shù)據(jù)庫(kù)操作。 舉例：舉例：創(chuàng)建一個(gè)修改觸發(fā)器（tri_s_upd） ，該觸發(fā)器防止用戶修改表 sc的 入學(xué)成績(jī)。 程序清單如下：程序清單如下： 使用使用UPDATE觸發(fā)器：觸發(fā)器： CREATE TRIGGER tri_s_upd ON sc AFTER update AS IF UPDATE（g

48、rade） BEGIN RAISERROR（不能修改成績(jī),16,10） ROLLBACK TRANSACTION END GO 可使用可使用IF UPDATE 語(yǔ)句來(lái)定義用來(lái)監(jiān)視特定列的數(shù)據(jù)更新的觸發(fā)語(yǔ)句來(lái)定義用來(lái)監(jiān)視特定列的數(shù)據(jù)更新的觸發(fā) 器，這將允許觸發(fā)器輕松分離出針對(duì)特定列的活動(dòng)。器，這將允許觸發(fā)器輕松分離出針對(duì)特定列的活動(dòng)。 當(dāng)觸發(fā)器檢測(cè)到特定列發(fā)生更新時(shí)，它會(huì)采取相應(yīng)的操作，如：當(dāng)觸發(fā)器檢測(cè)到特定列發(fā)生更新時(shí)，它會(huì)采取相應(yīng)的操作，如： 引發(fā)聲明該列不可更新的錯(cuò)誤信息，或者基于新更新的列值一系列的引發(fā)聲明該列不可更新的錯(cuò)誤信息，或者基于新更新的列值一系列的 語(yǔ)句。語(yǔ)句。 DELETE觸

49、發(fā)器通常用于兩種情況， 第一種情況是為了防止那些確實(shí)需要?jiǎng)h除但會(huì)引起數(shù)據(jù)一致性問題的記 錄的刪除， 第二種情況是執(zhí)行可刪除主記錄的子記錄的級(jí)聯(lián)刪除操作。 使用使用DELETE觸發(fā)器：觸發(fā)器： 場(chǎng)景場(chǎng)景： 你為銷售部門創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，專門存儲(chǔ)用戶訂單的信息。你的幾你為銷售部門創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，專門存儲(chǔ)用戶訂單的信息。你的幾 個(gè)重要客戶因?yàn)橐呀?jīng)有一段時(shí)間沒有下新訂單，因此他們的信息不在最個(gè)重要客戶因?yàn)橐呀?jīng)有一段時(shí)間沒有下新訂單，因此他們的信息不在最 近客戶之列。近客戶之列。 你們那的銷售經(jīng)理發(fā)覺在歷史的客戶記錄中，有人錯(cuò)誤的刪除了這你們那的銷售經(jīng)理發(fā)覺在歷史的客戶記錄中，有人錯(cuò)誤的刪除了這 幾位重要

50、客戶的相關(guān)信息，因此她要求你防止類似情況的發(fā)生。幾位重要客戶的相關(guān)信息，因此她要求你防止類似情況的發(fā)生。 你決定應(yīng)該怎么做？你決定應(yīng)該怎么做？ 舉例：舉例： 建立一個(gè)與建立一個(gè)與sc表結(jié)構(gòu)一樣的表表結(jié)構(gòu)一樣的表s1，并建立一個(gè)觸發(fā)器，并建立一個(gè)觸發(fā)器 （ tr_del ）。當(dāng)刪除表）。當(dāng)刪除表sc中的記錄時(shí)，自動(dòng)將刪除掉的記錄存放到中的記錄時(shí)，自動(dòng)將刪除掉的記錄存放到s1 表中。表中。 程序清單如下：程序清單如下： CREATE TRIGGER tr_del ON sc /*建立觸發(fā)器建立觸發(fā)器 AFTER DELETE/*對(duì)表刪除操作對(duì)表刪除操作 AS insert into s1 (SEL

51、ECT * FROM deleted) /*將刪除掉的數(shù)據(jù)送入表將刪除掉的數(shù)據(jù)送入表s1中中*/ GO 實(shí)例實(shí)例1：用觸發(fā)器實(shí)現(xiàn)引用完整性。：用觸發(fā)器實(shí)現(xiàn)引用完整性。 建立觸發(fā)器（建立觸發(fā)器（ tr_del_s ），當(dāng)刪除表），當(dāng)刪除表 學(xué)生記錄時(shí)，自動(dòng)刪除表學(xué)生記錄時(shí)，自動(dòng)刪除表sc 中對(duì)應(yīng)中對(duì)應(yīng) 學(xué)號(hào)的記錄。（可設(shè)變量學(xué)號(hào)的記錄。（可設(shè)變量bh ） 程序清單如下：程序清單如下： CREATE TRIGGER tr_del_s ON student AFTER DELETE AS BEGIN DECLARE bh char(10) SELECT bh=deleted.sno from del

52、eted DELETE sc where sno=bh END 實(shí)例實(shí)例1：用觸發(fā)器實(shí)現(xiàn)引用完整性：用觸發(fā)器實(shí)現(xiàn)引用完整性:若班級(jí)中存在學(xué)生，則不若班級(jí)中存在學(xué)生，則不 能刪除班級(jí)。能刪除班級(jí)。 USE student GO IF EXISTS (SELECT name FROM sysobjects WHERE name = del_banji AND type = TR) DROP TRIGGER del_banji GO CREATE TRIGGER del_banji ON 班級(jí) FOR DELETE AS DECLARE banjidaima char(9) SELECT banji

53、daima=班級(jí)代碼 FROM Deleted IF EXISTS (SELECT * FROM 學(xué)生 WHERE 班級(jí)代碼=banjidaima) BEGIN PRINT 班級(jí)正在使用，不能被刪除！ ROLLBACK TRANSACTION END END GO 使用使用INSTEAD OF觸發(fā)器：觸發(fā)器： INSTEAD OF觸發(fā)器使SQL SERVER 執(zhí)行觸發(fā)器中的代碼。而不是 執(zhí)行導(dǎo)致觸發(fā)器激活的操作。 INSTEAD OF觸發(fā)器代替了原始觸發(fā)器的操作執(zhí)行。它可以基于一個(gè) 或多個(gè)基表的視圖上的定義，它還增加了可對(duì)視圖執(zhí)行的更新類型的種類。 每個(gè)表或視圖限制為每個(gè)觸發(fā)操作（ INSER

54、T、UPDATE 和DELETE）一 個(gè)INSTEAD OF觸發(fā)器。 注意：注意： 不能在定義了不能在定義了WITH CHECK OPTION 的視圖上創(chuàng)建的視圖上創(chuàng)建INSTEAD OF觸 發(fā)器。 使用嵌套觸發(fā)器：使用嵌套觸發(fā)器： 由于任何觸發(fā)器都可包含影響另一個(gè)表的 UPDATE 、INSERT 和 DELETE語(yǔ)句，當(dāng)一個(gè)觸發(fā)器啟動(dòng)另一個(gè)觸發(fā)器的操作時(shí)，稱為嵌套觸 發(fā)器。 可使用嵌套觸發(fā)器服務(wù)器配置選項(xiàng)控制是否嵌套觸發(fā)器。嵌套在安可使用嵌套觸發(fā)器服務(wù)器配置選項(xiàng)控制是否嵌套觸發(fā)器。嵌套在安 裝時(shí)已默認(rèn)啟用，并且設(shè)置在服務(wù)器中的級(jí)別，但是可以使用裝時(shí)已默認(rèn)啟用，并且設(shè)置在服務(wù)器中的級(jí)別，但是

55、可以使用 sp_configure 系統(tǒng)存儲(chǔ)過程禁用并重新啟用嵌套。系統(tǒng)存儲(chǔ)過程禁用并重新啟用嵌套。 使用遞歸觸發(fā)器：使用遞歸觸發(fā)器： 遞歸觸發(fā)器所執(zhí)行的操作將直接或間接引起同一個(gè)觸發(fā)器再次激發(fā)遞歸觸發(fā)器所執(zhí)行的操作將直接或間接引起同一個(gè)觸發(fā)器再次激發(fā) 的觸發(fā)器。任何觸發(fā)器都包含影響同一個(gè)表或另一個(gè)表的的觸發(fā)器。任何觸發(fā)器都包含影響同一個(gè)表或另一個(gè)表的UPDATE、 INSERT 或或 DELETE 語(yǔ)句。在它啟用的情況下，更改表中數(shù)據(jù)的觸發(fā)器語(yǔ)句。在它啟用的情況下，更改表中數(shù)據(jù)的觸發(fā)器 可能再次激活自身，從而引起遞歸執(zhí)行。可能再次激活自身，從而引起遞歸執(zhí)行。 有兩種類型的遞歸：有兩種類型的遞

56、歸： 直接遞歸：直接遞歸： 如果一個(gè)觸發(fā)器在同一個(gè)表上激發(fā)并執(zhí)行某個(gè)操作，而該操作又引如果一個(gè)觸發(fā)器在同一個(gè)表上激發(fā)并執(zhí)行某個(gè)操作，而該操作又引 起同一個(gè)觸發(fā)器再次激發(fā)，則此時(shí)為直接遞歸。起同一個(gè)觸發(fā)器再次激發(fā)，則此時(shí)為直接遞歸。 間接遞歸：間接遞歸： 如果一個(gè)觸發(fā)器激發(fā)并執(zhí)行某個(gè)操作，而該操作又引起另一個(gè)觸發(fā)如果一個(gè)觸發(fā)器激發(fā)并執(zhí)行某個(gè)操作，而該操作又引起另一個(gè)觸發(fā) 器（器（同一個(gè)表或另一個(gè)表中同一個(gè)表或另一個(gè)表中）激發(fā)，結(jié)果又造成原來(lái)的表上的又發(fā)生更新，）激發(fā)，結(jié)果又造成原來(lái)的表上的又發(fā)生更新， 則此時(shí)為間接遞歸，隨后又使最初的觸發(fā)器再次激發(fā)。則此時(shí)為間接遞歸，隨后又使最初的觸發(fā)器再次激發(fā)

57、。 默認(rèn)情況下，遞歸觸發(fā)器選項(xiàng)在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)是禁用的?？墒褂媚J(rèn)情況下，遞歸觸發(fā)器選項(xiàng)在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)是禁用的?？墒褂?ALTER DATABASE 語(yǔ)句將其啟用。語(yǔ)句將其啟用。 可用以下語(yǔ)句啟用遞歸觸發(fā)器： ALTER DATABASE AdventureWorks - 設(shè)置 RECURSIVE_TRIGGERS 數(shù)據(jù)庫(kù)選項(xiàng)以啟用遞歸觸發(fā)器 SET RECURSIVE_TRIGGERS ON Sp_dboption database,recursive triggers,Ture 在這個(gè)交互式多媒體中，你將了解 如何通過服務(wù)器選項(xiàng)，控制數(shù)據(jù)庫(kù)中嵌 套觸發(fā)器的行為。 DDL觸發(fā)器的創(chuàng)建和應(yīng)用：觸

58、發(fā)器的創(chuàng)建和應(yīng)用： DDL 觸發(fā)器會(huì)為響應(yīng)多種數(shù)據(jù)定義語(yǔ)言觸發(fā)器會(huì)為響應(yīng)多種數(shù)據(jù)定義語(yǔ)言 （DDL） 語(yǔ)句而激發(fā)。這語(yǔ)句而激發(fā)。這 些語(yǔ)句主要是以些語(yǔ)句主要是以 CREATE、ALTER 和和 DROP 開頭的語(yǔ)句。開頭的語(yǔ)句。DDL 觸發(fā)器觸發(fā)器 可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫(kù)操作。可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫(kù)操作。 DDL 觸發(fā)器一般用于以下目的：觸發(fā)器一般用于以下目的： （1）防止對(duì)數(shù)據(jù)庫(kù)架構(gòu)進(jìn)行某些更改；）防止對(duì)數(shù)據(jù)庫(kù)架構(gòu)進(jìn)行某些更改； （2）希望數(shù)據(jù)庫(kù)中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫(kù)架構(gòu)中的更改；）希望數(shù)據(jù)庫(kù)中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫(kù)架構(gòu)中的更改； （3）要記錄數(shù)據(jù)庫(kù)架構(gòu)中的

59、更改或事件。）要記錄數(shù)據(jù)庫(kù)架構(gòu)中的更改或事件。 DDL觸發(fā)器的創(chuàng)建和應(yīng)用：觸發(fā)器的創(chuàng)建和應(yīng)用： 使用使用 CREATE TRIGGER 命令創(chuàng)建命令創(chuàng)建 DDL 觸發(fā)器的語(yǔ)法形式如下：觸發(fā)器的語(yǔ)法形式如下： CREATE TRIGGER trigger_name ON ALL SERVER|DATABASE WITH ENCRYPTION FOR|AFTER event_type|event_group,.n AS sql_statement; .n 注意：注意： 僅在運(yùn)行觸發(fā)僅在運(yùn)行觸發(fā) DDL 觸發(fā)器的觸發(fā)器的 DDL 語(yǔ)句后，語(yǔ)句后，DDL 觸發(fā)器才會(huì)激發(fā)。觸發(fā)器才會(huì)激發(fā)。 在響應(yīng)當(dāng)前數(shù)

60、據(jù)庫(kù)或服務(wù)器中處理的在響應(yīng)當(dāng)前數(shù)據(jù)庫(kù)或服務(wù)器中處理的 Transact-SQL 事件時(shí)，可事件時(shí)，可 以激發(fā)以激發(fā) DDL 觸發(fā)器。觸發(fā)器的作用域取決于事件。觸發(fā)器。觸發(fā)器的作用域取決于事件。 舉例：舉例： 使用使用 DDL 觸發(fā)器觸發(fā)器safety來(lái)防止數(shù)據(jù)庫(kù)中的任一表被修改或刪除。來(lái)防止數(shù)據(jù)庫(kù)中的任一表被修改或刪除。 程序清單如下：程序清單如下： CREATE TRIGGER safety ON DATABASE AFTER DROP_TABLE, ALTER_TABLE AS PRINT You must disable Trigger safety to drop or alter t