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

數(shù)據(jù)庫應用技術(shù)SQLServer2012主編：屈武江霍艷飛張健新世紀應用型高等教育計算機類課程規(guī)劃教材SQLSERVER2012SHUJUKUYINGYONGJISHU第8章存儲過程和觸發(fā)器8.1存儲過程存儲過程就是將一些需要多次調(diào)用的固定操作語句編寫成程序段，這些程序段存儲在服務器上，通過調(diào)用存儲過程實現(xiàn)其功能。存儲過程有如下優(yōu)點：8.1.1存儲過程概述（1）存儲過程可實現(xiàn)模塊化的程序設計。（2）存儲過程能夠?qū)崿F(xiàn)較快的執(zhí)行速度。（3）存儲過程能夠減少網(wǎng)絡流量。（4）存儲過程可被作為一種安全機制來充分利用。8.1存儲過程1．系統(tǒng)存儲過程系統(tǒng)存儲過程是SQLServer系統(tǒng)自身提供的存儲過程，可以作為命令執(zhí)行各種操作。系統(tǒng)存儲過程主要用來從系統(tǒng)表中獲取信息，使用系統(tǒng)存儲過程完成數(shù)據(jù)庫服務器的管理工作，為系統(tǒng)管理員提供幫助，為用戶查看數(shù)據(jù)庫對象提供方便，系統(tǒng)存儲過程位于數(shù)據(jù)庫服務器中，并且以sp_開頭。系統(tǒng)存儲過程定義在系統(tǒng)定義和用戶定義的數(shù)據(jù)庫中，在調(diào)用時不必在存儲過程前加數(shù)據(jù)庫限定名。8.1.2

存儲過程的類型8.1存儲過程2．用戶自定義存儲過程用戶自定義存儲過程即用戶使用Transact-SQL語句為了實現(xiàn)某一特定功能在用戶數(shù)據(jù)庫中編寫的Transact-SQL語句集合。創(chuàng)建用戶自定義存儲過程時，存儲過程名前加上“##”，表示創(chuàng)建了一個全局的臨時存儲過程；存儲過程前加上“#”，表示創(chuàng)建的局部臨時存儲過程。局部臨時存儲過程只能在創(chuàng)建它的會話中使用，會話結(jié)束時，將被刪除。這兩種存儲過程都存儲在tempdb數(shù)據(jù)庫中。8.1.2

存儲過程的類型8.1存儲過程創(chuàng)建存儲過程的語法格式如下：8.1.3創(chuàng)建存儲過程的語法CREATEPROC[EDURE]存儲過程名稱[;number][{@參數(shù)名稱參數(shù)類型}[=default][OUTPUT]][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASTransact-SQL語句塊8.1存儲過程1．創(chuàng)建無參數(shù)的存儲過程2．創(chuàng)建帶參數(shù)的存儲過程3．創(chuàng)建帶默認參數(shù)的存儲過程4．創(chuàng)建帶OUTPUT參數(shù)的存儲過程（1）輸入?yún)?shù)執(zhí)行存儲過程時，將實際參數(shù)的值傳遞給對應的形式參數(shù)，參與存儲過程中的數(shù)據(jù)處理。輸入?yún)?shù)在存儲過程名后、AS關鍵字前定義。（2）輸出參數(shù)和返回值使用輸出參數(shù)和返回值，存儲過程可將信息返回給調(diào)用的存儲過程。若要返回信息，則必須在創(chuàng)建和執(zhí)行存儲過程時同時指定OUTPUT關鍵字；若執(zhí)行存儲過程省略了OUTPUT關鍵字，則存儲過程正常執(zhí)行，但不會返回信息。8.1.4

創(chuàng)建存儲過程8.1存儲過程1．執(zhí)行無參數(shù)存儲過程語法格式為：EXEC存儲過程名2．執(zhí)行帶參數(shù)存儲過程（1）順序法：傳遞的參數(shù)和定義的參數(shù)順序一致，執(zhí)行存儲過程傳遞參數(shù)值時，不指定參數(shù)名。語法格式為：EXEC存儲過程名參數(shù)1值，參數(shù)2值，……（2）提示法：各個參數(shù)的順序可以任意排列，執(zhí)行存儲過程傳遞參數(shù)值時，指定參數(shù)名。語法格式為：EXEC存儲過程名@參數(shù)1=值，@參數(shù)2=值，……8.1.5執(zhí)行存儲過程8.1存儲過程3．使用SSMS管理工具執(zhí)行存儲過程SQLServer除了可以使用Transact-SQL語句執(zhí)行存儲過程，還可以使用SSMS管理工具執(zhí)行存儲過程。8.1.5執(zhí)行存儲過程8.1存儲過程1．修改存儲過程修改存儲過程的語法格式為：ALTERPROCEDURE存儲過程名稱[;number][{@參數(shù)名稱參數(shù)類型}[=參數(shù)默認值][OUTPUT]][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASTransact-SQL語句塊參數(shù)說明：修改存儲過程的語法格式與創(chuàng)建存儲過程的語法格式大致相同，區(qū)別在于用關鍵字ALTER替換CREATE，參數(shù)說明同創(chuàng)建存儲過程中的參數(shù)說明。8.1.6管理存儲過程8.1存儲過程2．刪除存儲過程刪除存儲過程的語法格式為：DROPPROCEDURE存儲過程名[,…]參數(shù)說明：一次可以刪除多個存儲過程，存儲過程名之間用逗號分隔。8.1.6管理存儲過程8.2觸發(fā)器1．觸發(fā)器的作用（1）觸發(fā)器可以用來對表實施復雜的完整性約束，當觸發(fā)器所保護的數(shù)據(jù)發(fā)生改變時，觸發(fā)器自動被激活，從而防止對數(shù)據(jù)的不正確的修改。（2）如果發(fā)現(xiàn)引起觸發(fā)器執(zhí)行的SQL語句執(zhí)行了非法操作，可以回滾事務使語句不執(zhí)行，返回到事務執(zhí)行前的狀態(tài)。（3）實現(xiàn)級聯(lián)更新、級聯(lián)刪除操作。（4）可以根據(jù)表中更新前與更新后的數(shù)據(jù)對比，進行相應的操作。（5）防止對數(shù)據(jù)庫架構(gòu)進行某些更改。（6）當希望數(shù)據(jù)庫中發(fā)生某種情況時，響應數(shù)據(jù)庫架構(gòu)的更改。8.2.1觸發(fā)器概述8.2觸發(fā)器2．觸發(fā)器中的兩個特殊的臨時表觸發(fā)器執(zhí)行時會產(chǎn)生兩個臨時表：inserted表和deleted表。臨時表與觸發(fā)器表結(jié)構(gòu)相同，inserted表與deleted表只在觸發(fā)器中存在，而且是只讀的，當觸發(fā)器執(zhí)行完畢，系統(tǒng)自動刪除這兩個表?？梢酝ㄟ^測試臨時表，來確定應該執(zhí)行什么樣的操作。對觸發(fā)器表進行DML操作時，其操作過程與臨時表的關聯(lián)如下：（1）執(zhí)行INSERT操作：插入觸發(fā)器表中的新行被插入inserted表中；（2）執(zhí)行DELETE操作：從觸發(fā)器表中刪除的行被插入deleted表中；（3）執(zhí)行UPDATE操作：從觸發(fā)器表中刪除的舊行被插入deleted表中；插入觸發(fā)器表中的新行被插入inserted表中。8.2.1觸發(fā)器概述8.2觸發(fā)器1．按照觸發(fā)事件的不同分類8.2.2觸發(fā)器的類型DML觸發(fā)器DDL觸發(fā)器8.2觸發(fā)器2．按照被激活的時機不同分類8.2.2觸發(fā)器的類型AFTER觸發(fā)器INSTEADOF觸發(fā)器8.2觸發(fā)器在對表進行數(shù)據(jù)操作（插入、刪除、更新）時，可以通過在表中創(chuàng)建觸發(fā)器實現(xiàn)對表的完整性數(shù)據(jù)約束，或?qū)嵤碗s的數(shù)據(jù)約束，以及對表中記錄進行查看、修改、刪除。1．創(chuàng)建DML觸發(fā)器創(chuàng)建DML觸發(fā)器的語法格式：8.2.3創(chuàng)建觸發(fā)器CREATETRIGGER<觸發(fā)器名>ON<基本表名|視圖名>[WITHENCRYPTION]FOR|AFTER|INSTEADOF{[INSERT][,UPDATE][,DELETE]}AS<Transact-SQL語句塊>8.2觸發(fā)器在對表進行數(shù)據(jù)操作（插入、刪除、更新）時，可以通過在表中創(chuàng)建觸發(fā)器實現(xiàn)對表的完整性數(shù)據(jù)約束，或?qū)嵤碗s的數(shù)據(jù)約束，以及對表中記錄進行查看、修改、刪除。1．創(chuàng)建DML觸發(fā)器創(chuàng)建DML觸發(fā)器的語法格式：8.2.3創(chuàng)建觸發(fā)器CREATETRIGGER<觸發(fā)器名>ON<基本表名|視圖名>[WITHENCRYPTION]FOR|AFTER|INSTEADOF{[INSERT][,UPDATE][,DELETE]}AS<Transact-SQL語句塊>8.2觸發(fā)器2．創(chuàng)建DDL觸發(fā)器創(chuàng)建DDL觸發(fā)器語法格式為：8.2.3創(chuàng)建觸發(fā)器CREATETRIGGER<觸發(fā)器名>ON{ALLSERVER|DATABASE}[WITHENCRYPTION]FOR|AFTER{激活DDL觸發(fā)器的事件}AS<Transact-SQL語句塊>8.2觸發(fā)器無論是DML觸發(fā)器還是DDL觸發(fā)器，如果出現(xiàn)一個觸發(fā)器執(zhí)行啟動另一個觸發(fā)器的操作，這種情況稱之為觸發(fā)器嵌套。8.2.4

觸發(fā)器的嵌套8.2觸發(fā)器觸發(fā)器創(chuàng)建之后，可根據(jù)情況，對觸發(fā)器進行修改或刪除，或者在保留觸發(fā)器的基礎上，禁用觸發(fā)器，暫時停止觸發(fā)器的使用，在需要的時候啟用觸發(fā)器即可。1．修改觸發(fā)器修改觸發(fā)器的語法格式為（以DML觸發(fā)器為例）：8.2.5觸發(fā)器的管理ALTERTRIGGER<觸發(fā)器名>ON<基本表名|視圖名>[WITHENCRYPTION]FOR|AFTER|INSTEADOF{[INSERT][,UPDATE][,DELETE]}AS<Trantsact-SQL語句塊>8.2觸發(fā)器2．刪除觸發(fā)器刪除觸發(fā)器的語法格式：DROPTRIGGER觸發(fā)器名[,…]說明：一次可以刪除多個觸發(fā)器，觸發(fā)器名之間用逗號分隔。8.2.5觸發(fā)器的管理8.2觸發(fā)器3．觸發(fā)器的禁用和啟用（1）禁用觸發(fā)器禁用觸發(fā)器不會刪除該觸發(fā)器。觸發(fā)器仍然作為對象存在于當前數(shù)據(jù)庫中，但是，當執(zhí)行INSERT、UPDATE、DELETE語句時（針對DML觸發(fā)器）或其他數(shù)據(jù)定義語句時（針對DDL觸發(fā)器），觸發(fā)器將不會被激發(fā)。禁用觸發(fā)器的語法格式為：DISABLETRIGGER觸發(fā)器名ON{表|視圖|DATABASE|ALLSERVER}8.2.5觸發(fā)器的管理8.2觸發(fā)器3．觸發(fā)器的禁用和啟用（2）啟用觸發(fā)器啟用觸發(fā)器的語法格式為：ENABLETRIGGER觸發(fā)器名ON{表|視圖|DATABASE|A