第7章 存儲過程和觸發(fā)器

1、7 . 1 存儲存儲過程過程7 . 2 觸發(fā)器觸發(fā)器在 Microsoft SQL Server 2005 中，使用T-SQL語句編寫存儲過程。存儲過程可以接受輸入?yún)?shù)、返回表格或標題結(jié)果和消息，調(diào)用“數(shù)據(jù)定義語言（DDL）”和“數(shù)據(jù)操作語言（DML）”語句，然后返回輸出參數(shù)。7.1.1 存儲過程的類型存儲過程的類型在 Microsoft SQL Server 2005 中有下列幾種類型存儲過程：(1) 系統(tǒng)存儲過程。系統(tǒng)存儲過程是由SQL Server提供的存儲過程，可以作為命令執(zhí)行。(2) 擴展存儲過程。擴展存儲過程是指在SQL Server 2005環(huán)境之外，使用編程語言（例如C+語言）

2、創(chuàng)建的外部例程形成的動態(tài)鏈接庫（DLL）。(3) 用戶存儲過程。Microsoft SQL Server 2005中，用戶存儲過程可以使用T-SQL語言編寫，也可以使用CLR方式編寫。h1使用使用T-SQL命令創(chuàng)建存儲過程命令創(chuàng)建存儲過程創(chuàng)建存儲過程的語句是CREATE PROCEDURE或CREATE PROC，二者同義。語法：CREATE PROC | PROCEDURE /*定義參數(shù)的類型*/ VARYING = default OUT | OUTPUT /*定義參數(shù)的屬性*/ ,.n WITH ENCRYPTION /*對語句文本加密*/AS .n /*執(zhí)行的操作*/;說明：如果希望其

3、他用戶無法查看存儲過程的定義，則可以使用WITH ENCRYPTION子句創(chuàng)建存儲過程。這樣，過程定義將以不可讀的形式存儲。不要以sp_為前綴創(chuàng)建任何存儲過程。sp_前綴是SQL Server用來命名系統(tǒng)存儲過程的，使用這樣的名稱可能會與以后的某些系統(tǒng)存儲過程發(fā)生沖突VARYING：指定作為輸出參數(shù)支持的結(jié)果集。該參數(shù)由存儲過程動態(tài)構(gòu)造，其內(nèi)容可能發(fā)生改變，僅適用于cursor參數(shù)。h2存儲過程的執(zhí)行存儲過程的執(zhí)行通過EXECUTE或EXEC命令可以執(zhí)行一個已定義的存儲過程，EXEC是EXECUTE的簡寫。語法格式： EXEC | EXECUTE return_status = = | OUT

4、PUT | DEFAULT ,.n ;h3舉例舉例(1) 設(shè)計簡單的存儲過程?！纠?.1】 返回05880101號學生的成績情況，該存儲過程不使用任何參數(shù)。存儲過程定義后，執(zhí)行存儲過程student_info：EXECUTE student_info(2) 使用帶參數(shù)的存儲過程?！纠?.2】 從PXSCJ數(shù)據(jù)庫的三個表中查詢某人指定課程的成績和學分。該存儲過程接收與傳遞參數(shù)精確匹配的值。執(zhí)行存儲過程student_info1：EXECUTE student_info1 周一, java(3) 使用帶OUPUT參數(shù)的存儲過程?！纠?.3】 創(chuàng)建一個存儲過程do_action，根據(jù)條件處理相應(yīng)數(shù)據(jù)

5、，處理后輸出相應(yīng)的信息。接下來執(zhí)行存儲過程do_action，并查看結(jié)果：DECLARE s_str char(8)EXEC do_action 0, s_str OUTPUTSELECT s_str;(4) 使用帶有通配符參數(shù)的存儲過程?！纠?.4】 從三個表的連接中返回指定學生、指定課程的成績。該存儲過程在參數(shù)中使用了模式匹配，如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認值。 執(zhí)行存儲過程：EXECUTE st_info /*參數(shù)使用默認值*/或者：EXECUTE st_info 張% /*傳遞給sname 的實參為張%*/(5) 使用OUTPUT游標參數(shù)的存儲過程。OUTPUT游標用于返回存儲過程

6、的局部游標?！纠?.4】 在表student上聲明并打開一個游標。在如下的批處理中，聲明一個局部游標變量，執(zhí)行上述存儲過程，并將游標賦值給局部變量，然后通過該游標變量讀取記錄。使用界面方式定義與修改存儲過程。對于一個已創(chuàng)建的存儲過程，可有2種方法刪除：(1) 通過對象資源管理器刪除，此方法非常簡單，請讀者自己練習。(2) 利用T-SQL語句DROP PROCEDURE刪除，語法格式：DROP PROC | PROCEDURE ,.n 觸發(fā)器是一個被指定關(guān)聯(lián)到一個表的數(shù)據(jù)對象，觸發(fā)器是不需要調(diào)用的，當對一個表的特別事件出現(xiàn)時，它就會被激活。觸發(fā)口碑 代碼也是由SQL語句組成的，因此用在存儲過程中

7、的語句也可以在觸發(fā)器的定義中。觸發(fā)器是一類特殊的存儲過程，與表的關(guān)系密切，用于保護表中的數(shù)據(jù)。當有操作影響到觸發(fā)器保護的數(shù)據(jù)時，觸發(fā)器將自動執(zhí)行。 觸發(fā)器的作用觸發(fā)器的作用 觸發(fā)器的主要作用是能實現(xiàn)由主鍵和外鍵所不能保證的、復(fù)雜的參照完整性和數(shù)據(jù)的一致性，除此之外，觸發(fā)器還有其他許多不同的功能。 跟蹤變化 觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，從而禁止了數(shù)據(jù)庫未經(jīng)許可的更新和變化，使數(shù)據(jù)庫的修改、更新操作更安全，數(shù)據(jù)庫運行更穩(wěn)定。 可以強化數(shù)據(jù)條件約束 觸發(fā)器能夠?qū)崿F(xiàn)比CHECK語句更為復(fù)雜的約束，更適合在大型數(shù)據(jù)庫管理系統(tǒng)中用來約束數(shù)據(jù)的完整性。 級聯(lián)和并行運行 觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，并自動

8、地級聯(lián)影響整個數(shù)據(jù)庫的各項內(nèi)容。例如，某個表的觸發(fā)器中包含有對另外一個表的數(shù)據(jù)操作，如刪除、更新、插入，而該操作又導(dǎo)致該表上的觸發(fā)器被觸發(fā)。 由此可見，觸發(fā)器可以實現(xiàn)高級形式的業(yè)務(wù)規(guī)則、復(fù)雜行為限制和定制記錄等功能。7.2.1 觸發(fā)器的類型觸發(fā)器的類型(1) DML觸發(fā)器。當數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操縱語言（DML）事件時將調(diào)用DML觸發(fā)器。DML事件包括對表或視圖的INSERT語句、UPDATE語句和DELETE語句，因而DML觸發(fā)器可分為3種類型：INSERT、UPDATE和DELETE。(2) DDL觸發(fā)器。DDL觸發(fā)器是SQL Server 2005新增的功能，也是由相應(yīng)的事件觸發(fā)，但DDL觸

9、發(fā)器觸發(fā)的事件是數(shù)據(jù)定義語句（DDL）語句。這些語句主要是以CREATE、ALTER、DROP等關(guān)鍵字開頭的語句。h1創(chuàng)建創(chuàng)建DML觸發(fā)器觸發(fā)器語法格式：CREATE TRIGGER . ON /*指定操作對象*/ WITH ENCRYPTION /*說明是否采用加密方式*/ AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS ; .n After：用于說明觸發(fā)器在指定操作成功執(zhí)行后觸發(fā)，如After Insert表示向表中插入數(shù)據(jù)時激活觸發(fā)器。Insert|Update|Delete：指定激活觸發(fā)器的語句類型，必須至少指定一個選項。在觸發(fā)器定義中允許

10、使用上述選項的任意順序組合。如果觸發(fā)器表存儲約束，則在after觸發(fā)器執(zhí)行之前檢查這些約束。如果違反了約束，則不after觸發(fā)器。觸發(fā)器中使用的特殊表。觸發(fā)器中使用的特殊表。在執(zhí)行觸發(fā)器時，系統(tǒng)創(chuàng)建了兩個特殊的臨時表inserted表和deleted表，這兩個表的內(nèi)容如下、Inserted表：當向表中插入數(shù)據(jù)時，INSERT觸發(fā)器角發(fā)執(zhí)行，新的記錄插入到觸發(fā)器表和inserted表中。Deleted表：用于保存已從表中刪除的記錄，當觸發(fā)一個DELETED觸發(fā)器時，被刪除的記錄存放到deleted表中。修改一條記錄等于插入一條新記錄，同時刪除舊記錄。當對定義了UPDATE觸發(fā)器的表記錄修改時，表

11、中愿望錄移到deleted表中。修改過的記錄插入到inserted表中。由于inserted表和deleted表都是臨時表，它們在觸發(fā)器執(zhí)行時被創(chuàng)建，觸發(fā)器執(zhí)行完后就消失了，所以只可以在觸發(fā)器的主唱 句中使用SELECT語句查詢這兩個表。 Update(column)函數(shù)函數(shù) Update()函數(shù)返回一個布爾值，指示是否對表的指定列進行了insert或update操作。其中insert操作可是看做是把原來的null值換成了新值。 例：在student表中創(chuàng)建update和delete觸發(fā)器，當修改或刪除student表中的學號時，同時修改或刪除sc表中的該學號。Delete from stud

12、entWhere age=21update student set sno=05880304where sno=05880101 Instead of 觸發(fā)器觸發(fā)器 After觸發(fā)器是在觸發(fā)語句執(zhí)行后觸發(fā)的。與after觸發(fā)器不同的是，instead of 觸發(fā)觸發(fā)時只執(zhí)行觸發(fā)器內(nèi)部的SQL語句，而不執(zhí)行激活該觸發(fā)器的SQL語句。 在表或視圖上，每個insert、update或delete語句最多可以定義一個instead of觸發(fā)器。 Instead of觸發(fā)器的主要作用是用于不可更新視圖支持更新。如果視圖不可更新，則必須使用instead of觸發(fā)器支持基表中數(shù)據(jù)的插入、更新和刪除操作。例

13、：創(chuàng)建表table_2，屬性只有一個a，整數(shù)類型，在表中創(chuàng)建instead of insert觸發(fā)器，當向表中插入記錄時顯示相應(yīng)消息。select * from table_2查詢的表中沒有插入任何的數(shù)據(jù)查詢的表中沒有插入任何的數(shù)據(jù)insert into table_2 values(10) 視圖更新操作的限制視圖更新操作的限制 如果視圖是從多個基本表使用連接操作導(dǎo)出的，則不允許更新。 如果定義視圖的select語句包含group by、distinct、聚組函數(shù)（虛列），則不允許更新。 如果建立視圖時帶with read only選項，則不能對該視圖進行任何插入、更新和刪除操作。h2創(chuàng)建創(chuàng)建

14、DDL觸發(fā)器觸發(fā)器語法格式：CREATE TRIGGER ON ALL SERVER | DATABASE WITH ENCRYPTION AFTER event_type | event_group ,.n AS ; .n DDL觸發(fā)要用于：防止對數(shù)據(jù)庫架構(gòu)進行某些修改；希望數(shù)據(jù)庫中發(fā)生某些變化以利于相應(yīng)數(shù)據(jù)庫架構(gòu)中的更改；記錄數(shù)據(jù)庫架構(gòu)中的更改或事件。DDL觸發(fā)器只在響應(yīng)由T-SQL語法所指定的DDL事件時才會觸發(fā)。ALL SERVER | DATABASE：ALL SERVER是指當前DDL觸發(fā)器的作用于當前服務(wù)器。DATABASE指DDL觸發(fā)器作用于當前數(shù)據(jù)庫Event_type：此選項包括CREATE_TABLE、ALTER_TABLE、DROP_TABLE、CREATE_VIEW等。Event_group：ON關(guān)鍵字后面為ALL SERVER此選項使用該名稱，如CREATE_DATA