實驗附錄二三_存儲觸發(fā)

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

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

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

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

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

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

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

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

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

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

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

12、日志，所以該語句不能觸發(fā)DELETE型觸發(fā)器。 （8）WRITETEXT語句不能觸發(fā)INSERT或UPDATE型的觸發(fā)器。創(chuàng)建一個觸發(fā)器時，必須指定觸發(fā)器的名字、在哪一個表上定義觸發(fā)器、激活觸發(fā)器的修改語句，如INSERT、DELETE、UPDATE，當然兩個或三個不同的修改語句也可以觸發(fā)同一個觸發(fā)器，如INSERT和UPDATE語句能激活同一個觸發(fā)器。1用CREATE TRIGGER命令創(chuàng)建觸發(fā)器 可用CREATE TRIGGER命令創(chuàng)建觸發(fā)器，其語法規(guī)則如下： 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是用戶要創(chuàng)建的觸發(fā)器的名字。觸發(fā)器的名字必須符合SQL Server的命名規(guī)則，且其名字在當前數據庫中必須是唯一的。 （2）ta

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

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

16、）用來測定對某一確定列是插入操作還是更新操作，但不與刪除操作用在一起。 （12）IF（COLUMNS_UPDATED()）僅在INSERT和UPDATE類型的觸發(fā)器中使用，用其來檢查所涉及的列是被更新還是被插入。 （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指被更新的列的位掩碼。從以上語句可以看出，一個表最多可以有三個觸發(fā)器：一個插入（INSERT）觸發(fā)器、一個更新（UPDATE）觸發(fā)器、一個刪除（DELETE）觸發(fā)器。一個觸發(fā)器只能應用到一個表上，但一個觸發(fā)器可以包含很多動作，可以執(zhí)行很多功能。觸發(fā)器只能建立在基本表上，不可以建立在視圖或臨時表上。例 創(chuàng)建一個觸發(fā)器，當向s表中插入一條記錄時，自動顯示s表中的記錄。 CREATE TRIGGER Change_Dis

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

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

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

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

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

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

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

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

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

27、Manager修改觸發(fā)器正文。通過Enterprise Manager修改觸發(fā)器正文的操作步驟與查看觸發(fā)器信息一樣。修改完觸發(fā)器后要使用“檢查語法”選項對語句進行檢查。通過Alert trigger命令修改觸發(fā)器正文。 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 其中各參數或保留字的含義參看“創(chuàng)