存储过程和触发器课件1

存儲過程和觸發器

本章以本書的“圖書銷售系統”為例，主要介紹了如何在SQLServer2000資料庫中建立、修改存儲過程和觸發器。

10．1存儲過程

10.1.1存儲過程簡介存儲過程是一組預先編譯好的SQL代碼。它是SQLSever上的一個非常有用的特性，是一個獨立的資料庫對象，可以通過調用命令（EXECUTE）執行存儲過程，而不必發送大量Transact-SQL代碼，因此大大減少了網路流量，或用戶的應用程式調用。存儲過程可用於向用戶返回數據、向表中插入新數據、修改、執行系統和管理任務等。

存儲過程有如下優點：（1）行速度快存儲過程在創建時經過語法檢查和性能優化，在第一次被調用後就駐留在內存中，下次執行不再經過編譯。當某操作需大量使用或重複執行，存儲過程將比Transact-SQL代碼要快。（2）模組化的程式設計存儲過程在創建後，即存儲在資料庫中，可被應用程式無數次調用。用戶可按不同功能的程式模組，設計不同的存儲過程以供使用。（3）減少網路流量存儲過程是一組Transact-SQL語句，但其是作為一個獨立的單元來使用，在調用時，只需一個執行語句即可實現。（4）安全性可以授權無直接執行存儲過程語句許可權的用戶執行該存儲過程。（5）可以將複雜的任務劃分為多個簡單的商業邏輯（6）存儲過程對某些需要週期性處理的任務特別有用。（7）存儲過程在客戶端和服務器端提供了良好的併發性。在下例情況下，將使用到存儲過程。（1）不需要任何客戶端資訊而在伺服器端完成的操作（2）涉及到大量行的操作、運算（3）頻繁的、週期性的操作（4）那些被多個模組共同執行的操作。10.1.2創建存儲過程語法：

CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]

參數解釋：

procedure_name

新存儲過程的名稱。過程名必須符合識別字規則，且對於資料庫及其所有者必須唯一。

要創建局部臨時過程，可以在procedure_name前面加一個編號符(#procedure_name)，要創建全局臨時過程，可以在procedure_name前面加兩個編號符(##procedure_name)。完整的名稱（包括#或##）不能超過128個字元。指定過程所有者的名稱是可選的。

;number

是可選的整數，用來對同名的過程分組，以便用一條DROPPROCEDURE語句即可將同組的過程一起除去。例如，名為orders的應用程式使用的過程可以命名為orderproc;1、orderproc;2等。DROPPROCEDUREorderproc語句將除去整個組。如果名稱中包含定界標識符，則數字不應包含在識別字中，只應在procedure_name前後使用適當的定界符。@parameter

過程中的參數。在CREATEPROCEDURE語句中可以聲明一個或多個參數。用戶必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的默認值）。存儲過程最多可以有2100個參數。使用@符號作為第一個字元來指定參數名稱。參數名稱必須符合識別字的規則。每個過程的參數僅用於該過程本身；相同的參數名稱可以用在其他過程中。默認情況下，參數只能代替常量，而不能用於代替表名、列名或其他資料庫對象的名稱。【注意】調用存儲過程時，必須給出參數值，參數可為空格，但不能為空（NULL）

data_type

參數的數據類型。所有數據類型（包括text、ntext和image）均可以用作存儲過程的參數。不過，cursor數據類型只能用於OUTPUT參數。如果指定的數據類型為cursor，也必須同時指定VARYING和OUTPUT關鍵字。

【說明】對於可以是cursor數據類型的輸出參數，沒有最大數目的限制。

VARYING

指定作為輸出參數支持的結果集（由存儲過程動態構造，內容可以變化）。僅適用於游標參數。

default

參數的默認值。如果定義了默認值，不必指定該參數的值即可執行過程。默認值必須是常量或NULL。如果過程將對該參數使用LIKE關鍵字，那麼默認值中可以包含通配符（%、_、[]和[^]）。

OUTPUT

表明參數是返回參數。該選項的值可以返回給EXEC[UTE]。使用OUTPUT參數可將資訊返回給調用過程。Text、ntext和image參數可用作OUTPUT參數。使用OUTPUT關鍵字的輸出參數可以是游標占位符。

n表示最多可以指定2100個參數的占位符。

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}RECOMPILE表明SQLServer不會緩存該過程的計畫，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內存中的執行計畫時，請使用RECOMPILE選項。

ENCRYPTION表示SQLServer加密syscomments表中包含

CREATEPROCEDURE語句文本的條目。使用ENCRYPTION可防止將過程作為SQLServer複製的一部分發佈。

FORREPLICATION

指定不能在訂閱伺服器上執行為複製創建的存儲過程。.使用FORREPLICATION選項創建的存儲過程可用作存儲過程篩選，且只能在複製過程中執行。本選項不能和WITHRECOMPILE選項一起使用。

AS指定過程要執行的操作。

sql_statement

過程中要包含的任意數目和類型的Transact-SQL語句。但有一些限制。

n是表示此過程可以包含多條Transact-SQL語句的占位符。注釋存儲過程的最大值為128MB。用戶定義的存儲過程只能在當前資料庫中創建（臨時過程除外，臨時過程總是在tempdb中創建）。在單個批處理中，CREATEPROCEDURE語句不能與其它Transact-SQL語句組合使用。默認情況下，參數可為空。如果傳遞NULL參數值並且該參數在CREATE或ALTERTABLE語句中使用，而該語句中引用的列又不允許使用NULL，則SQLServer會產生一條錯誤資訊。為了防止向不允許使用NULL的列傳遞NULL參數值，應向過程中添加編程邏輯或為該列使用默認值（使用CREATE或ALTERTABLE的DEFAULT關鍵字）。1．下麵我們以本書的SQLServer資料庫“圖書銷售系統”為例，分別建立一個名為“cunchu”、“cunchu2”的存儲過程。【例10.1】本例創建一個名稱為“cunchu”的存儲過程，該存儲過程的作用是根據參數值，返回表BookRecord的相關記錄。執行的操作是查詢記錄。步驟：（1）打開SQL企業管理器，連接伺服器，展開資料庫列表，選擇“圖書銷售系統”資料庫。（2）右擊“圖書銷售系統”資料庫，選擇“新建”命令中的“存儲過程”選項，如圖10.1。

圖10.1新建存儲過程（3）在彈出的“存儲過程屬性—新建存儲過程”對話框中，輸入以下語句：

CREATEPROCEDUREcunchu@findBookNochar(30),@findBookNamechar(200)ASselectBookNO,BookName

fromBookRecord

whereBookNO=@findBookNOorBookName=@findBookName

該語句新建了一個名為cunchu的存儲過程，參數findBookNo對應字段BookNO，參數findBookName對應字段BookName。圖10.2存儲過程屬性（4）檢查語法。生成一個存儲過程前，應該執行檢查語法操作。方法是點擊“檢查語法”按鈕。我們點擊該按鈕後，系統將會提示，如成功，則顯示“語法檢查成功”，否則將顯示其錯誤。語法無誤，點擊“確定”後，系統將生成該存儲過程。【例10.2】本例創建一個名為“cunchu2”的存儲過程，該存儲過程定義了四個參數，對應表Operator的四個字段。執行的操作是向Operator表添加新記錄。步驟：步驟（1）、步驟（2）與例10.2的步驟（1）、步驟（2）相同，在此省略。（3）在彈出的“存儲過程屬性—新建存儲過程”對話框中，輸入以下語句：

CREATEPROCEDUREcunchu2@Accountchar(10),@Namechar(20),@Passwordchar(16),@IsdeletedBITASinsertOperatorvalues(@account,@name,@password,@isdeleted)

（4）檢查語法。點擊“檢查語法”按鈕後，如成功，則顯示“語法檢查成功”，否則將顯示其錯誤。語法無誤，點擊“確定”後，系統將生成該存儲過程。

2．執行存儲過程：要執行存儲過程，需在SQL查詢分析器中使用關鍵字“execute”，該關鍵字後面是存儲過程名，及相關參數。下麵我們介紹如何執行剛才新建的兩個存儲過程。【例10.3】本例介紹執行存儲過程“cunchu”的步驟。步驟：（1）打開SQL企業管理器，連接伺服器，選擇“圖書銷售系統”。（2）選擇“工具”菜單，在彈出的菜單中選擇“SQL查詢分析器”，彈出如圖10.3，輸入executecunchu"9787302000001",""，在工具欄中點擊“執行查詢”按鈕，即可見執行結果，如圖10.4：

圖10.3執行存儲過程圖10.4執行結果【例10.4】本例介紹執行存儲過程“cunchu2”的步驟，並查看其結果。步驟：（1）打開SQL企業管理器，連接伺服器，選擇“圖書銷售系統”。（2）點擊“工具”菜單，選擇“SQL查詢分析器”，在查詢分析器的文本區域內輸入如下語句。

executecunchu2yb,楊斌,yb,0（3）在工具欄中點擊“執行查詢”按鈕，第二個文本框出現提示“所影響的行數為一行”，表示執行存儲過程成功。如圖10.5。我們可以打開表Operator，查看其結果。如圖10.6所示，表中新添加了一條記錄。

圖10.5執行存儲過程成功圖10.6查看結果

3．修改存儲過程：修改存儲過程的方法很簡單，選擇要修改的存儲過程，雙擊即彈出“存儲過程屬性”對話框，在文本框中直接修改語句，檢查語法通過，點擊確定後即生效。

4．刪除存儲過程：刪除存儲過程最直接的方法是選擇相應資料庫，在該資料庫的存儲過程列表中選擇要刪除的存儲過程，右擊滑鼠，彈出快捷菜單，選擇“刪除“即可。如圖10.7：圖10.7刪除存儲過程10．2觸發器

10.2.1觸發器簡介觸發器是一種特殊的存儲過程，其作用是當表中數據被UPDATE、INSERT、DELETE語句修改，或直接在表中數據被直接更改後，觸發器就會生效，自動執行觸發器中的語句。對每個SQL語句，觸發器執行一次，在SQL語句執行完成後它立即啟動。如遇到錯誤，觸發器和啟動它的SQL語句自動取消。使用觸發器的優點：

1．觸發器是自動執行的，它在對表的數據作了任何修改之後立即被啟動。

2．觸發器可以通過資料庫中的相關表進行層疊和更改。

3．觸發器可以強制執行。

4．觸發器可以引用其他表中的列。

使用觸發器的限制：

1．一個表中最多可以有三個觸發器。一個是“INSERT”觸發器，一個是“UPDATE”觸發器，還有一個是“DELETE”觸發器。

2．一個觸發器只能運行一個表，但是可以運行三個用戶動作，包括INSERT、UPDATE和DELETE。在下列情況下，將使用到觸發器。

1．beforeinsert/update觸發器可以檢查操作是否完成以及數據的一致性，或者為那些需要唯一編號的列提供數值

2．beforedelete觸發器可以在刪除時檢查主要明細表之間的關係，也可以終止刪除。

3．所有的before觸發器均可以通過激發異常終止操作回退事務

4．所有的after觸發器均可以在更改完成後做一些審計或更改其他相關表的操作。10.2.2創建觸發器語法

CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{

{{FOR|AFTER|INSTEADOF}{[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]

}

參數

trigger_name

觸發器的名稱。觸發器名稱必須符合識別字規則，並且在資料庫中必須唯一。可以選擇是否指定觸發器所有者名稱。

Table|view

是在其上執行觸發器的表或視圖，有時稱為觸發器表或觸發器視圖。可以選擇是否指定表或視圖的所有者名稱。

WITHENCRYPTION

加密syscomments表中包含CREATETRIGGER語句文本的條目。使用WITHENCRYPTION可防止將觸發器作為SQLServer複製的一部分發佈。

AFTER

指定觸發器只有在觸發SQL語句中指定的所有操作都已成功執行後才激發。所有的引用級聯操作和約束檢查也必須成功完成後，才能執行此觸發器。

如果僅指定FOR關鍵字，則AFTER是默認設置。不能在視圖上定義AFTER觸發器。

INSTEADOF

指定執行觸發器而不是執行觸發SQL語句，從而替代觸發語句的操作。在表或視圖上，每個INSERT、UPDATE或DELETE語句最多可以定義一個INSTEADOF觸發器。然而，可以在每個具有INSTEADOF觸發器的視圖上定義視圖。

INSTEADOF觸發器不能在WITHCHECKOPTION的可更新視圖上定義。如果向指定了WITHCHECKOPTION選項的可更新視圖添加INSTEADOF觸發器，SQLServer將產生一個錯誤。用戶必須用ALTERVIEW刪除該選項後才能定義INSTEADOF觸發器。

{[DELETE][,][INSERT][,][UPDATE]}

是指定在表或視圖上執行哪些數據修改語句時將啟動觸發器的關鍵字。必須至少指定一個選項。在觸發器定義中允許使用以任意順序組合的這些關鍵字。如果指定的選項多於一個，需用逗號分隔這些選項。

對於INSTEADOF觸發器，不允許在具有ONDELETE級聯操作引用關係的表上使用DELETE選項。同樣，也不允許在具有ONUPDATE級聯操作引用關係的表上使用UPDATE選項。

WITHAPPEND

指定應該添加現有類型的其他觸發器。

WITHAPPEND不能與INSTEADOF觸發器一起使用，或者，如果顯式聲明AFTER觸發器，也不能使用該子句。只有當出於向後相容而指定FOR時（沒有INSTEADOF或AFTER），才能使用WITHAPPEND。

NOTFORREPLICATION

表示當複製進程更改觸發器所涉及的表時，不應執行該觸發器。

AS是觸發器要執行的操作。

Sql_statement

是觸發器的條件和操作。觸發器條件指定其他準則，以確定DELETE、INSERT或UPDATE語句是否導致執行觸發器操作。

當嘗試DELETE、INSERT或UPDATE操作時，Transact-SQL語句中指定的觸發器操作將生效。觸發器可以包含任意數量和種類的Transact-SQL語句。觸發器旨在根據數據修改語句檢查或更改數據；它不應將數據返回給用戶。觸發器中的Transact-SQL語句常常包含控制流語言。CREATETRIGGER語句中使用幾個特殊的表：

·

deleted和inserted是邏輯（概念）表。這些表在結構上類似於定義觸發器的表（也就是在其中嘗試用戶操作的表）；這些表用於保存用戶操作可能更改的行的舊值或新值。例如，若要檢索deleted表中的所有值，請使用：

·

SELECT*

·

FROMdeleted

n

是表示觸發器中可以包含多條Transact-SQL語句的占位符。對於IFUPDATE(column)語句，可以通過重複UPDATE(column)子句包含多列。

IFUPDATE(column)

測試在指定的列上進行的INSERT或UPDATE操作，不能用於DELETE操作。可以指定多列。因為在ON子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。若要測試在多個列上進行的INSERT或UPDATE操作，請在第一個操作後指定單獨的UPDATE(column)子句。在INSERT操作中IFUPDATE將返回TRUE值，因為這些列插入了顯式值或隱性(NULL)值。【說明】IFUPDATE(column)子句的功能等同於IF、IF...ELSE或WHILE語句，並且可以使用BEGIN...END語句塊。可以在觸發器主體中的任意位置使用UPDATE(column)。

column

是要測試INSERT或UPDATE操作的列名。該列可以是SQLServer支持的任何數據類型。但是，計算列不能用於該環境中。IF(COLUMNS_UPDATED())

測試是否插入或更新了提及的列，僅用於INSERT或UPDATE觸發器中。COLUMNS_UPDATED返回varbinary位模式，表示插入或更新了表中的哪些列。

COLUMNS_UPDATED函數以從左到右的順序返回位，最左邊的為最不重要的位。最左邊的位表示表中的第一列；向右的下一位表示第二列，依此類推。如果在表上創建的觸發器包含8列以上，則COLUMNS_UPDATED返回多個位元組，最左邊的為最不重要的位元組。在INSERT操作中COLUMNS_UPDATED將對所有列返回TRUE值，因為這些列插入了顯式值或隱性(NULL)值。可以在觸發器主體中的任意位置使用COLUMNS_UPDATED。

bitwise_operator

是用於比較運算的位運算符。updated_bitmask

是整型位掩碼，表示實際更新或插入的列。例如，表t1包含列C1、C2、C3、C4和C5。假定表t1上有UPDATE觸發器，若要檢查列C2、C3和C4是否都有更新，指定值14；若要檢查是否只有列C2有更新，指定值2。

comparison_operator

是比較運算符。使用等號(=)檢查updated_bitmask中指定的所有列是否都實際進行了更新。使用大於號(>)檢查updated_bitmask中指定的任一列或某些列是否已更新。

column_bitmask

是要檢查的列的整型位掩碼，用來檢查是否已更新或插入了這些列。我們以“圖書銷售系統”資料庫中的表InRecord為例創建三個觸發器：

1．INSERT觸發器【例10.5】本例創建了一個名為OrdDet_Insert的觸發器，該觸發器的作用是當有新書入庫的時候，則更新庫存中該書的庫存量。步驟：（1）打開SQL企業管理器，連接伺服器，雙擊“資料庫”，展開資料庫列表，選擇“圖書銷售系統”資料庫。（2）展開表，右擊“InRecord”，選擇“所有任務”下的子菜單“管理觸發器”，如圖10.8：圖10.8創建觸發器(3)在“觸發器屬性”對話框的文本區域輸入以下語句：

CREATETRIGGEROrdDet_Insert

ONInRecord

FORINSERTAS

UPDATEPSETQuantity=P.Quantity+I.Quantity

FROMBookRecordASPINNERJOINInRecordASIONP.BookNO=I.BookNO

（4）單擊“檢查語法”按鈕，無誤後單擊“確定”命令。2．UPDATE觸發器【例10.6】本觸發器的名稱為OrdDet_Update，當SalesDetail表發生更新的時候，更新BookRecord表的相關記錄。步驟：（1）打開SQLServer企業管理器，連接服務管理器，展開資料庫列表，選擇“圖書銷售系統”資料庫。（2）展開表，右擊“SalesDetail”表，選擇“所有任務”下子菜單的“管理觸發器”。（3）在“觸發器屬性”對話框的文本區