1、附錄二SQL Server存儲過程客戶/服務器數據庫與傳統的數據庫結構的一個很重要的區別是，在傳統的數據庫中只存放數據，所有的應用程序都在用戶端，都與用戶實際運行的應用程序捆綁在一起；而在客戶/服務器結構的數據庫中，在數據庫中還可以存放程序，即存儲過程。 （一）基本概念 存儲過程是經過預編譯T-SQL語句的集合，大多數系統存儲過程以sp_作為過程名稱的前綴。系統存儲過程存放在master數據庫中，歸系統管理員所有，但其中很多過程可以運行在任意數據庫中。存儲過程是對數據庫管理系統功能的有力擴充。利用存儲過程我們可以避免在網絡上傳輸大量無用的信息或原始數據，只需要傳輸調用存儲過程的指令和數據庫服務

2、器返回的處理結果。不使用存儲過程時，所有的數據處理都在客戶端完成；而使用存儲過程時，可以使數據處理在服務器端完成。 客戶端應用 （不使用存儲過程）Start transactionINSERT dataUPDATE dataDELETE dataEnd transaction·DBMSServerStart transactionCallStored procedureEnd transactionDBMSServerprocedureINSERT dataUPDATE dataDELETE data（使用存儲過程）客戶端應用 （二）創建和執行存儲過程 CREATE PROCedur

3、e <過程名> (<參數, >) AS <SQL語句>比如，我們創建一個最簡單的存儲過程 CREATE PROCedure sp_getemp ASSELECT * FROM 職工 若我們只選擇工資值大于某個值的職工記錄，可以使用帶參數的存儲過程CREATE PROCedure sp_getemp( salary) ASSELECT * FROM職工 WHERE 工資> salary 創建存儲過程中的<SQL語句>可以是一組T-SQL語句，并可以含有流程控制等語句。存儲過程可以嵌套，即在一個存儲過程中可以調用另外一個存儲過程。存儲過程一般用

4、來完成數據查詢和數據處理操作，所以在存儲過程中不可以使用創建數據庫對象的語句。 執行存儲過程的語句 EXECute <返回狀態碼>= <存儲過程名> <參數>=<值>|<變量> （三）存儲過程的返回值和狀態信息 無論什么時候執行存儲過程，總要返回一個結果碼，用以指示存儲過程的執行狀態。如果存儲過程執行成功，返回的結果碼是0；如果存儲過程執行失敗，返回的結果碼一般是一個負數，它和失敗的類型有關。我們在創建存儲過程時，也可以定義自己的狀態碼和錯誤信息。比如： CREATE PROCedure sp_getemp(salary) ASIF

5、salary=NULLBEGINPRINT “必須提供一個數值作參數！”ENDRETURN 13IF NOT EXISTS(SELECT*FROM職工 WHERE 工資>salary)BEGINPRINT“沒有滿足條件的記錄！”ENDRETURN-103SELECT*FROM職工WHERE工資>salary 作為一般的原則，在編寫存儲過程時，應該能夠預測用戶在執行存儲過程時可能出現的錯誤，并在存儲過程中納入有意義的錯誤信息或提示信息。 （四）要用好存儲過程 存儲過程是客戶/服務器機制的一個重要組成部分，如果使用客戶/服務器機制的數據庫管理系統，但是不理解存儲過程或沒有充分利用存儲過

6、程，那將使客戶/服務器機制的功能大打折扣，使系統的整體性能可能降低很多。存儲過程的管理還可以納入安全控制的范疇，也就是說存儲過程的使用可以授權。利用存儲過程可以使困難的或復雜的數據庫活動自動化，提高安全性和限制對數據庫的存取。另外，根據客戶對查詢和特定環境資源的要求，存儲過程可以改善應用程序的響應時間。 附錄三 觸發器及其用途（一）基本概念 觸發器可以看作是一類特殊的存儲過程，它在滿足某個特定條件時自動觸發執行，存儲過程和觸發器同是提高數據庫服務器性能的有力工具。 觸發器是為表上的更新、插入、刪除操作定義的，也就是說當表上發生更新、插入或刪除操作時觸發器將執行。在一個表上，一種類型的觸發器只能

7、定義一個。比如，當刪除操作發生時，只能有一個觸發器執行。觸發器的主要作用是實現由主鍵和外鍵所不能保證的復雜的參照完整性和數據的一致性。除此之外，觸發器還有其他許多不同的功能。（1）強化約束（Enforce Restriction）觸發器可以偵測數據庫內的操作，從而不允許數據庫中未經許可的更新和變化。（2）級聯運行（Cascaded Operation）觸發器可以偵測數據庫內的操作，并自動地級聯影響整個數據庫的各項內容。例如，某個表上的觸發器包含對另外一個表的數據操作（如刪除、更新、插入），而該操作又導致該表上的觸發器被觸發。（3）存儲過程的調用(Stored Procedure Invocat

8、ion)為了響應數據庫更新，觸發器可以調用一個或多個存儲過程，甚至可以通過外部過程的調用而在DBMS之外進行操作。由此可見，觸發器可以解決高級形式的業務規則或復雜行為限制以及實現定制記錄等問題。例如，觸發器能夠找出某一表在數據修改前后狀態發生的差異，并根據這種差異執行一定的操作。此外一個表的同一類型（INSERT、UPDATE、DELETE）的多個觸發器能夠對同一種數據操作采取多種不同的操作。觸發器可以用于數據參照完整性和以下一些場合： 1觸發器可以通過級聯的方式對相關的表進行修改。比如，對父表的修改，可以引起對子孫表的一系列修改，從而保證數據的一致性和完整性。 2觸發器可以禁止或撤消違反參照

9、完整性的修改。 3觸發器可以強制用比CHECK約束定義更加復雜的限制。觸發器也是一個數據庫對象。一個觸發器和三部分內容有關：激活觸發器的表、激活觸發器的數據修改語句和觸發器要采取的動作。 （二）觸發器的種類SQL Server 2000 支持兩種類型的觸發器：AFTER觸發器和INSTEAD OF觸發器。其中，AFTER觸發器即為SQL Server 2000版本以前所介紹的觸發器；該類型觸發器要求只有執行某一操作（INSERT、UPDATE、DELETE）之后，觸發器才被觸發，且只能在表上定義。可以為針對表的同一操作定義多個觸發器。AFTER觸發器可以定義哪一個觸發器被最先觸發，哪一個被最后

10、觸發，通常使用系統過程sp_settriggerorder來完成此任務。INSTEAD OF觸發器表示并不執行其所定義的操作（INSERT、UPDATE、DELETE），而僅是執行觸發器本身。既可在表上定義INSTEAD OF觸發器，也可以在視圖上定義INSTEAD OF觸發器，但對同一操作只能定義一個INSTEAD OF觸發器。（三）創建觸發器上面介紹了有關觸發器的概念、作用和一些基本問題，下面我們將分別介紹在SQL Server中如何用SQL Server管理工具Enterprise Manager和Transaction-SQL來創建觸發器。在創建觸發器以前必須考慮到以下幾個方面： （1

11、）CREATE TRIGGER語句必須是批處理的第一個語句。 （2）表的所有者具有創建觸發器的缺省權限，表的所有者不能把該權限傳給其他用戶。 （3）觸發器的數據庫對象，其命名必須符合命名規則。 （4）盡管在觸發器的SQL語句中可以參照其他數據庫中的對象，但是 觸發器只能創建在當前數據庫中。 （5）雖然觸發器可以參照視圖或臨時表，但不能在視圖或臨時表上創建觸發器，只能在基表或在創建視圖的表上創建觸發器。 （6）一個觸發器只能對應一個表，這是由觸發器的機制決定的。 （7）盡管TRUNCATE TABLE語句如同沒有WHERE從句的DELETE語句，但是由于TRUNCATE TABLE語句沒有被記入

12、日志，所以該語句不能觸發DELETE型觸發器。 （8）WRITETEXT語句不能觸發INSERT或UPDATE型的觸發器。創建一個觸發器時，必須指定觸發器的名字、在哪一個表上定義觸發器、激活觸發器的修改語句，如INSERT、DELETE、UPDATE，當然兩個或三個不同的修改語句也可以觸發同一個觸發器，如INSERT和UPDATE語句能激活同一個觸發器。1用CREATE TRIGGER命令創建觸發器 可用CREATE TRIGGER命令創建觸發器，其語法規則如下： CREATE TRIGGER trigger_name ONtable|view SITH ENCRYPTION FOR|AFTE

13、R|INSTEAD OF DELETE INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE(column) AND|ORUPDATE(column) n | IF (COLUMNS_UPDATED() bitwise_operator updated_bitmask) comparison_operatorcolumn_bitmask n sql_statementn （1）trigger_name是用戶要創建的觸發器的名字。觸發器的名字必須符合SQL Server的命名規則，且其名字在當前數據庫中必須是唯一的。 （2）ta

14、blelview是與用戶創建的觸發器相關聯的表的名字或視圖的名稱，并且此表或視圖必須已經存在。 （3）WITH ENCRYPTION表示對包含有CREATE TRIGGER 文本的syscomments表進行加密。 （4）AFTER表示只有在執行了指定的操作（INSERT、DELETE、UPDATE）之后觸發器才被激活（執行觸發器中的SQL語句）。若使用關鍵字FOR，則表示觸發器為AFTER觸發器，且該類型觸發器只能在表上創建。 （5）INSTEAD OF請參看INSTEAD OF觸發器。 （6）DELETE , INSERT , UPDATE關鍵字用來指明哪種數據操作將激活觸發器。至少要指明

15、其中的一個選項，在觸發器的定義中，三者的順序不受限制，各選項要用逗號隔開。（7）WITH APPEND表明增加另外一個已存在的觸發器。只有在兼容性水平（指某一數據庫行為與以前版本的SQL Server兼容程度）不大于65時才使用該選項。 （8）NOT FOR REPLICATION表明當復制、處理和修改與觸發器相關聯的表時，觸發器不能被執行。（9）AS是觸發器將要執行的動作。 （10）sql_statement是包含在觸發器中的條件語句或處理語句。觸發器的條件語句定義了另外的標準來決定將被執行的INSERT、DELETE、UPDATE語句是否激活觸發器。（11）IF UPDATE（column

16、）用來測定對某一確定列是插入操作還是更新操作，但不與刪除操作用在一起。 （12）IF（COLUMNS_UPDATED()）僅在INSERT和UPDATE類型的觸發器中使用，用其來檢查所涉及的列是被更新還是被插入。 （13）bitwise_operatorj是在比較中使用的位邏輯運算符。 （14）updated_itmask是那些被更新或插入的列的整形位掩碼。例如，如果表T包括C1，C2，C3，C4，C5五列。為了確定是否只有C2列被修改可用2來做位掩碼；如果想確定C1，C2，C3，C4是否被修改，可用14來做位掩碼。 （15）comparison_operator是一個比較操作符。用“=”表示

17、檢查在updated_bitmask中定義的所有列是否都被更新，用“>”表示檢查在updated_bitmask中定義的某些列是否被更新。 （16）column_bitmask指被更新的列的位掩碼。從以上語句可以看出，一個表最多可以有三個觸發器：一個插入（INSERT）觸發器、一個更新（UPDATE）觸發器、一個刪除（DELETE）觸發器。一個觸發器只能應用到一個表上，但一個觸發器可以包含很多動作，可以執行很多功能。觸發器只能建立在基本表上，不可以建立在視圖或臨時表上。例 創建一個觸發器，當向s表中插入一條記錄時，自動顯示s表中的記錄。 CREATE TRIGGER Change_Dis

18、play On s FOR INSERT,UPDATE,DELETE AS SELECT * FROM s該觸發器建立完畢后，當執行如下操作時，將會顯示s數據表中的全部記錄。EXECUTE InsertRecordDefa sno=S11，sn=張建峰，age=17,sex=男2用Enterprise Manger創建觸發器的步驟如下（1）啟動Enterprise Manager,登錄到要使用的服務器。（2）在Enterprise Manager的左窗格中，展開要創建觸發器的數據庫文件夾，單擊“表”文件夾，此時在右窗格中顯示該數據庫的所有表。（3）在右窗格中，右擊要創建觸發器的數據表，在彈出的

19、快捷菜單中，將鼠標指向“所有任務”，在出現的下一級子菜單中選擇“管理觸發器”菜單項，此時會出現“觸發器屬性”對話框。（4）在“名稱”下拉框中選擇“<新建>”，“文本”編輯框中輸入觸發器的文本命令。（5）單擊“檢查語法”按鈕，檢查語句是否正確。（6）單擊“應用”按鈕，在“名稱”下拉框列表中會顯示新創建的觸發器名字。（四）觸發器的原理從以上的介紹中我們可以看出觸發器具有強大的功能，那么SQL Server是如何用觸發器來完成這些任務的呢？下面我們將對其工作原理及實現做詳細介紹。 每個觸發器有兩個特殊的表：插入表和刪除表，分別為inserted和deleted。這兩個表是邏輯表，并且這兩

20、個表都是由系統管理的，存儲在內存中，因此不允許用戶直接對其修改。這兩個表的結構總是與被該觸發器作用的表有相同的表結構。這兩個表是動態駐留在內存中的，當觸發器工作完成，這兩個表也被刪除。這兩個表主要保存因用戶操作而被影響到的原數據值或新數據值。另外，這兩個表是只讀的，即用戶不能向這兩個表寫入內容，但可以引用表中的數據。例如可用如下語句查看DELETED表中的信息：select * from deleted 1插入表的功能對一個定義了插入類型觸發器的表來講，一旦對該表執行了插入（INSERT）操作，那么對該插入的所有行來說，都有一個相應的副本級存放到插入表（inserted）中，即插入表就是用來存

21、儲原插入的內容。2刪除表的功能對一個定義了刪除類型觸發器的表來講，一旦對該表執行了刪除（DELETE）操作，則將所有的刪除行存放至刪除表（deleted）中。這樣做的目的是，一旦強迫觸發器中止的語句被執行時，刪除的那些行可以從刪除表中得以還原。需要強調的是，更新（UPDATE）操作包括兩個部分，即先將更新的內容去掉，然后將新值插入。因此，對一個定義了更新類型觸發器的表來講，當執行更新操作時，在刪除表中存放了舊值，然后在插入表中存放新值。由于觸發器僅當被定義的操作被執行時才被激活，即觸發器僅在執行插入、刪除和更新操作時才被執行。每條SQL語句僅能激活觸發器一次，可能存在一條語句影響多條記錄的情況

22、。在這種情況下就需要變量rowcount的值，該變量存儲了一條SQL語句執行后所影響的記錄數。一般來說，首先要用IF語句測試rowcount的值，以確定后面的語句是否執行。 3插入視圖和刪除視圖當在定義了觸發器的表上發生修改操作時會自動派生出兩個視圖，一個是插入視圖，一個是刪除視圖。當在表上發生插入操作時，新插入的行將出現在插入視圖中；當在表上發生刪除操作時，被刪除的舊行將出現在刪除視圖中。而更新的實現過程是先刪除舊行，然后再插入新行。這里的插入視圖和刪除視圖只是在觸發器內可用，一旦觸發器完成任務，這兩個視圖將不再存在。這兩個視圖的名稱是 inserted和 deleted，它們和原表具有完全

23、相同的結構。（五）管理觸發器如果要顯示作用于表上的觸發器究竟對表有哪些操作，必須查看觸發器信息。在SQL Server中，有多種方法查看觸發器信息。接下來，我們將介紹兩種常用的方法，即通過SQL Server 的管理工具Enterprise Manager 以及系統存儲過程sp_help、sp_helptext和sp_depends來查看觸發器信息。 1使用Enterprise Manager顯示觸發器信息使用Enterprise Manager顯示觸發器信息，步驟如下。 （1）啟動Enterprise Manager,登錄到要使用的服務器。 （2）在Enterprise Manager的左窗

24、格中，展開要創建觸發器的數據庫文件夾， 單擊“表”文件夾，此時在右窗格中顯示該數據庫的所有表。 （3）在右窗格中，右擊要創建觸發器的數據表，在彈出的快捷菜單中，將鼠標指向“所有任務”，在出現的下一級子菜單中選擇“管理觸發器”菜單項，出現“觸發器屬性”對話框。（4）在“名稱”下拉列表中選擇所要查看的觸發器的名稱，在“文本”編中顯示出該觸發器的文本命令。 2.使用系統存儲過程查看觸發器存儲過程sp_help、sp_help text 和 sp_depe nds分別 提供有關觸發器的不同信息。下面我們將分別對其進行介紹。（1）sp_help通過該系統過程，可以了解觸發器的一般信息，如觸發器的名字、屬

25、性、類型、創建時間。使用sp_help系統過程的命令格式為：sp_help觸發器名字例 查看我們已經建立的change_display觸發器。sp_helpchange_display (2) sp_helptext通過sp_helptext能夠查看觸發器的正文信息，其語法格式為： sp_helptext觸發器名例 查看我們已經建立的chang_display觸發器的命令文本。sp_helptextchange_display (3) sp_depends通過sp_depends能夠查看指定觸發器所引用的表或指定的表涉及到的所有觸發器。其語法形式如下：sp_depends 觸發器名字sp_de

26、pends表名例 查看我們已經建立的change-display觸發器所涉及的表。sp_dependschange_display注意：用戶必須在當前數據庫中查看觸發器的信息，而且被查看的觸發器必須已經被創建。3.修改、刪除觸發器（1）修改觸發器通過Enterprise Manager Alert trigger命令和sp_rename命令，可以修改觸發器的名字和正文。 使用sp_rename命令修改觸發器的名字。sp_rename命令的語法格式為：sp_rename oldname, newname，oldname為觸發器原來的名稱，newname為觸發器的新名稱。通過Enterprise

27、Manager修改觸發器正文。通過Enterprise Manager修改觸發器正文的操作步驟與查看觸發器信息一樣。修改完觸發器后要使用“檢查語法”選項對語句進行檢查。通過Alert trigger命令修改觸發器正文。 Alert trigger命令的語法格式為： ALTER TRIGGER trigger_name ON(table|view) WITH ENCRYPTION FOR|AFTER|INSTEAD OFDELETE,INSERT,UPDATE NOT FOR REPLICATION AS sql_statementn | FOR|AFTER|INSTEAD OFINSERT,UPDATE NOT FOR REPLICATION ASIF UP DATE column AND|ORUPDATE column n |IF COLUMNS_UPDATED bitwise_operatorupdated_bitmask comparison_operatorcolumn_bitmaskn sql_statementn 其中各參數或保留字的含義參看“創