第7章存儲過程和觸發器7.1存儲過程7.2觸發器7.3本章小結

7.1存儲過程

7.1.1存儲過程概述

存儲過程中獨立存在于表之外的數據庫對象，由被編譯在一起的一組Transact-SQL語句組成。它可以被客服調用，也可以被另一個存儲過程或觸發器調用，它的參數可以被傳遞，它的出錯代碼也可以被檢驗。存儲過程可以使對數據庫的管理，以及顯示關于數據庫及其用戶信息的工作容易得多。可以在任何可使用SQL語句的場合來使用存儲過程，它具有以下優點：

(1)可以在單個存儲過程中執行一系列SQL語句。

(2)可以從自己的存儲過程內引用其他存儲過程，從而簡化一系列復雜語句。

(3)存儲過程在創建時即在服務器上進行編譯，所以執行起來比單個SQL語句快，且能減少網絡通信的負擔。7.1.2創建存儲過程

要使用存儲過程，首先要創建一個存儲過程。可以使用Transact-SQL語言的createprocedure語句，也可以使用企業管理器或者存儲過程創建向導來完成。

1．使用createprocedure語句創建存儲過程

createprocedure語句的語法格式為：

【例1】創建一個簡單的存儲過程procedure1，用于檢索所有學生的成績記錄。通過下述sql語句執行該存儲過程：

use學生成績管理

/*判斷procedure1存儲過程是否存在，若存在，則執行它*/

ifexists(selectnamefromsysobjects

wherename=‘procedure1’andtype=‘p’)

execprocedure1/*執行存儲過程procedure1*/

go

執行結果如圖7-1所示。圖7-1執行結果

2．使用企業管理器創建存儲過程

使用企業管理器創建存儲過程的操作步驟如下：圖7-2創建存儲過程

3．使用向導創建

SQLServer2000還提供了創建存儲過程的向導。

【例2】使用向導創建一個存儲過程procedure2，對應的操作步驟如下：圖7-3“選擇向導”對話框圖7-4選擇數據庫圖7-5選擇存儲過程圖7-6完成創建存儲過程圖7-7編輯存儲過程圖7-8“編輯存儲過程SQL”對話框7.1.3執行存儲過程

執行存儲過程使用execute語句，其完整語法格式如下：

[exec[ute]]

[@return_status=]

{procedure_name[;number]|@procedure_name_var}

[[@parameter=]{value|@variable[output]|[default]]

[,…n]

[withrecompile]7.1.4存儲過程的參數

在創建和使用存儲過程時，其參數是非常重要的。下面詳細討論存儲過程的參數傳遞和返回。

1．使用參數

在調用存儲過程時，有兩種傳遞參數的方法。第一種是在傳遞參數時，使傳遞的參數和定義時的參數順序一致，對于使用默認值的參數可以用default代替。

2．使用默認參數

創建存儲過程時，可以為參數提供一個默認值，默認值必須為常量或者null。

【例3】創建一個存儲過程insert_學生表2_2，該存儲過程中包含兩個參數，其默認值分別為'2013008'，'外語'和'北京'。圖7-9執行結果

3．使用返回參數

在創建存儲過程時，可以定義返回參數。在執行存儲過程時，可以將結果返回給返回參數。返回參數應用output進行說明。

【例4】創建一個存儲過程average，它返回兩個參數@st_name和@st_avg，分別代表了姓名和平均分。圖7-10執行結果

4．存儲過程的返回值

存儲過程在執行后都會返回一個整型值。如果執行成功，則返回0；否則返回-1～-99之間的數值。也可以使用return語句來指定一個返回值。

【例5】創建的存儲過程test_ret根據輸入的參數來判斷返回值。7.1.5存儲過程的查看、修改和刪除

可以使用sp_helptext存儲過程來查看存儲過程的定義信息。

【例6】要查看前面的test_ret存儲過程的定義信息，可以執行下面的SQL語句。

use學生成績管理

go

execsp_helptexttest_ret

go

執行結果如圖7-11所示。圖7-11執行結果圖7-12“存儲過程屬性”對話框

7.2觸發器

7.2.1觸發器概述

觸發器在insert、update或delete語句對表或視圖進行修改時會被自動執行。觸發器可以查詢其他表，并可以包含復雜的Transact-SQL語句。一個表可以有多個觸發器。7.2.2創建觸發器

在創建觸發器前，應該考慮到下列問題：

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

(2)創建觸發器的權限默認分配給表的所有者，且不能將該權限轉給其他用戶。

(3)觸發器為數據庫對象，其名稱必須遵循標識符的命名規則。

(4)雖然觸發器可以引用當前數據庫以外的對象，但只能在當前數據庫中創建觸發器。

(5)雖然不能在臨時表或系統表上創建觸發器，但是觸發器可以引用臨時表。觸發器不應引用系統表，而應使用信息架構視圖。

(6)在含有用delete或update操作定義的外鍵的表中，不能定義insteadof和insteadofupdate觸發器。

(7)雖然truncatetable語句沒有類似where子句(用于刪除行)的delete語句，但它并不會引發delete觸發器，因為truncatetable語句沒有記錄。

(8)?writetext語句不會引發insert或update觸發器。創建觸發器時需要指定下面的選項：

(1)觸發器的名稱，必須遵循標識符的命名規則。

(2)需定義觸發器的表。

(3)觸發器將何時激發。

(4)激活觸發器的數據修改語句。有效選項為insert，update或delete。多個數據修改語句可激活同一個觸發器。例如，觸發器可由insert或update語句激活。

(5)執行觸發操作的編程語句。

1．使用Transact-SQL語句創建觸發器

創建觸發器可以使用createtrigger語句，其語法格式如下：

【例7】創建一個觸發器，在插入、修改和刪除記錄時，都會自動顯示表中的內容。

2．使用企業管理器

使用企業管理器創建觸發器的操作步驟如下：圖7-13管理觸發器快捷菜單圖7-14“觸發器屬性”對話框7.2.3inserted表和deleted表

在觸發器執行的時候，會產生兩個臨時表：inserted表和deleted表。它們的結構和觸發器所在的表的結構相同，SQLServer2000自動創建和管理這些表。可以使用這兩個臨時的駐留內存的表測試某些數據修改的效果及設置觸發器操作的條件；然而，不能直接對表中的數據進行更改。

【例8】下面的例子說明了inserted表和deleted表的作用。7.2.4使用觸發器

1．insert和update觸發器

當向表中插入或者更新記錄時，insert或者update觸發器被執行。一般情況下，這兩種觸發器常用來檢查插入或者修改后的數據是否滿足要求。

【例9】下面創建的trig2觸發器可用來檢查插入的c1是否在1～108之間。

2．delete觸發器

delete觸發器通常用于下面的情況：

(1)防止那些確實要刪除，但是可能會引起數據一致性問題的情況，一般用在有外部鍵記錄時。

(2)用于級聯刪除操作。

【例10】學生成績管理數據庫中，學生表包含學生基本數據，而成績表包含學生的成績，當刪除學生表中的學生記錄時，應該同時刪除成績表中對應的成績記錄。實現該功能的觸發器如下。此時，要刪除學生表中的記錄：

delete學生表where學號='213006'

則學生表中對應的記錄也被刪除。如果使用select語句來查詢成績表，將看到其中學號為'213006'的兩個記錄已經被刪除。7.2.5修改觸發器

修改觸發器可以使用altertrigger語句,其語法格式如下：7.2.6刪除觸發器

除了使用企業管理器刪除觸發器外，也可以使用droptrigger語句來刪除觸發器。其語法格式如下：

droptrigger{trigger}[,…n]

其中，“trigger”是要刪除的觸發器名稱，而n是表示可以指定多個觸發器的占位符。

【例11】要刪除trig1觸發器，則可以執行下面的SQL語句。

droptriggertrig17.2.7嵌套觸發器

如果一個觸發器在執行操作時引發了另一個觸發器，而這個觸發器又接著引發下一個觸發器，如此等等。這些觸發器就是嵌套觸發器。觸發器可嵌套至32層，可