[計算機]Ch08-存儲過程和觸發(fā)器ppt課件

1、網(wǎng)絡(luò)數(shù)據(jù)庫開發(fā)技術(shù)存儲過程和觸發(fā)器 2什么是存儲過程SQL ServerSQL Server提供了一種方法，它可以將一些固定提供了一種方法，它可以將一些固定的操作集中起來由的操作集中起來由SQL ServerSQL Server數(shù)據(jù)庫效勞器來完數(shù)據(jù)庫效勞器來完成，以實現(xiàn)某個任務(wù)，這種方法就是存儲過程。成，以實現(xiàn)某個任務(wù)，這種方法就是存儲過程。23存儲過程的類型 1 1 系統(tǒng)存儲過程系統(tǒng)存儲過程 系統(tǒng)存儲過程是由系統(tǒng)提供的存儲過程，作為命令執(zhí)行各種操作。系統(tǒng)存儲過程是由系統(tǒng)提供的存儲過程，作為命令執(zhí)行各種操作。 2 2 本地存儲過程本地存儲過程 本地存儲過程是指在用戶數(shù)據(jù)庫中創(chuàng)立的存儲過程，這種

2、存儲過程完成本地存儲過程是指在用戶數(shù)據(jù)庫中創(chuàng)立的存儲過程，這種存儲過程完成特定數(shù)據(jù)庫操作任務(wù)，其名稱不能以特定數(shù)據(jù)庫操作任務(wù)，其名稱不能以sp_sp_為前綴。為前綴。3 3 臨時存儲過程臨時存儲過程 臨時存儲過程屬于本地存儲過程。假如本地存儲過程的名稱前面有一個臨時存儲過程屬于本地存儲過程。假如本地存儲過程的名稱前面有一個“# #，該存儲過程就稱為部分臨時存儲過程，這種存儲過程只能在一個用戶，該存儲過程就稱為部分臨時存儲過程，這種存儲過程只能在一個用戶會話中使用。會話中使用。4 4 遠程存儲過程遠程存儲過程 遠程存儲過程指從遠程效勞器上調(diào)用的存儲過程。遠程存儲過程指從遠程效勞器上調(diào)用的存儲過程

3、。5 5 擴展存儲過程擴展存儲過程 在在SQL ServerSQL Server環(huán)境之外執(zhí)行的動態(tài)鏈接庫稱為擴展存儲過程，其前綴是環(huán)境之外執(zhí)行的動態(tài)鏈接庫稱為擴展存儲過程，其前綴是sp_sp_。使用時需要先加載到。使用時需要先加載到SQL ServerSQL Server系統(tǒng)中，并且按照使用存儲過程的方法系統(tǒng)中，并且按照使用存儲過程的方法執(zhí)行。執(zhí)行。34存儲過程使用存儲過程的優(yōu)點：使用存儲過程的優(yōu)點： 1 存儲過程在效勞器端運行，執(zhí)行速度快。存儲過程在效勞器端運行，執(zhí)行速度快。 2 存儲過程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲存儲過程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲器，在以后的

4、操作中，只需從高速緩沖存儲器中調(diào)用已編譯好的二器，在以后的操作中，只需從高速緩沖存儲器中調(diào)用已編譯好的二進制代碼執(zhí)行，進步了系統(tǒng)性能。進制代碼執(zhí)行，進步了系統(tǒng)性能。 3 確保數(shù)據(jù)庫的平安。使用存儲過程可以完成所有數(shù)據(jù)庫操確保數(shù)據(jù)庫的平安。使用存儲過程可以完成所有數(shù)據(jù)庫操作，并可通過編程方式控制上述操作對數(shù)據(jù)庫信息訪問的權(quán)限。作，并可通過編程方式控制上述操作對數(shù)據(jù)庫信息訪問的權(quán)限。 4 自動完成需要預(yù)先執(zhí)行的任務(wù)。存儲過程可以在系統(tǒng)啟動自動完成需要預(yù)先執(zhí)行的任務(wù)。存儲過程可以在系統(tǒng)啟動時自動執(zhí)行，而不必在系統(tǒng)啟動后再進展手工操作，大大方便了用時自動執(zhí)行，而不必在系統(tǒng)啟動后再進展手工操作，大大方便

5、了用戶的使用，可以自動完成一些需要預(yù)先執(zhí)行的任務(wù)。戶的使用，可以自動完成一些需要預(yù)先執(zhí)行的任務(wù)。45存儲過程與函數(shù)的比較函數(shù)與存儲過程根本類似，調(diào)用時函數(shù)要用表達式函數(shù)與存儲過程根本類似，調(diào)用時函數(shù)要用表達式方式，而存儲過程不能直接調(diào)用，必須采用方式，而存儲過程不能直接調(diào)用，必須采用“EXEC EXEC 存儲過程名或存儲過程名或“CALL CALL 存儲過程名。存儲過程名。56創(chuàng)立存儲過程 在在SQL ServerSQL Server中，可以使用三種方法創(chuàng)立存儲過程中，可以使用三種方法創(chuàng)立存儲過程 ：使用創(chuàng)立存儲過程向?qū)?chuàng)立存儲過程。使用創(chuàng)立存儲過程向?qū)?chuàng)立存儲過程。利用利用SQL Serve

6、r SQL Server 企業(yè)管理器創(chuàng)立存儲過程。企業(yè)管理器創(chuàng)立存儲過程。使用使用Transact-SQLTransact-SQL語句中的語句中的CREATE PROCEDURECREATE PROCEDURE命令創(chuàng)立存命令創(chuàng)立存儲過程。儲過程。 67用戶存儲過程的創(chuàng)立與執(zhí)行 n 在用戶存儲過程的定義中不能使用以下對象創(chuàng)立語句：在用戶存儲過程的定義中不能使用以下對象創(chuàng)立語句：CREATE VIEWCREATE DEFAULT CREATE RULE 1通過通過SQL命令創(chuàng)立和執(zhí)行存儲過程命令創(chuàng)立和執(zhí)行存儲過程假如要通過假如要通過SQL命令定義一個存儲過程查詢命令定義一個存儲過程查詢XSCJ數(shù)據(jù)

7、庫中每個同學(xué)各數(shù)據(jù)庫中每個同學(xué)各門功課的成績門功課的成績定義如下存儲過程定義如下存儲過程USE XSCJGoCREATE PROCEDURE student_gradeAS SELECT XS.學(xué)號學(xué)號,XS.姓名姓名,KC.課程名課程名, XS_KC.成績成績FROM XS,XS_KC,KCWHERE XS.學(xué)號學(xué)號=XS_KC.學(xué)號學(xué)號 AND XS_KC.課程號課程號=KC.課程號課程號Go78存儲過程n 調(diào)用存儲過程調(diào)用存儲過程EXEC student_gradeGO1 創(chuàng)立存儲過程創(chuàng)立存儲過程語法格式語法格式:CREATE PROC | PROCEDURE procedure_nam

8、e /*定義過程名定義過程名*/ parameter data_type /*定義參數(shù)的類型定義參數(shù)的類型*/ VARYING = default OUTPUT /*定義參數(shù)的屬性定義參數(shù)的屬性*/ ,.n1 WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION /*定義存儲過程的處理方式定義存儲過程的處理方式*/AS sql_statement .n2 /*執(zhí)行的操作執(zhí)行的操作*/89存儲過程對于存儲過程要注意以下幾點：對于存儲過程要注意以下幾點：1 1 用戶定義的存儲過程只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)立。用戶定義的存儲過程只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)立。2

9、 2 成功執(zhí)行成功執(zhí)行 CREATE PROCEDURE CREATE PROCEDURE 語句后，過程名稱存儲在語句后，過程名稱存儲在sysobjects sysobjects 系統(tǒng)表中，而系統(tǒng)表中，而 CREATE PROCEDURE CREATE PROCEDURE 語句的文本存儲在語句的文本存儲在 syscomments syscomments 中。中。3 3 自動執(zhí)行存儲過程自動執(zhí)行存儲過程SQL Server SQL Server 啟動時可以自動執(zhí)行一個或多個存儲過程。這些存儲過程啟動時可以自動執(zhí)行一個或多個存儲過程。這些存儲過程必須由系統(tǒng)管理員在必須由系統(tǒng)管理員在 master

10、master 數(shù)據(jù)庫中創(chuàng)立，并在數(shù)據(jù)庫中創(chuàng)立，并在 sysadmin sysadmin 固定效勞固定效勞器角色下作為后臺過程執(zhí)行。器角色下作為后臺過程執(zhí)行。 4 4一個存儲過程的最大尺寸為一個存儲過程的最大尺寸為128M128M。5 5創(chuàng)立存儲過程的權(quán)限默認屬于數(shù)據(jù)庫所有者，該所有者可將此權(quán)創(chuàng)立存儲過程的權(quán)限默認屬于數(shù)據(jù)庫所有者，該所有者可將此權(quán)限授予其他用戶。限授予其他用戶。910存儲過程2存儲過程的執(zhí)行存儲過程的執(zhí)行通過通過EXEC命令可以執(zhí)行一個已定義的存儲過程。命令可以執(zhí)行一個已定義的存儲過程。語法格式：語法格式： EXEC UTE return_status = procedure_

11、name ;number | procedure_name_var parameter = value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE 存儲過程的執(zhí)行要注意以下幾點：存儲過程的執(zhí)行要注意以下幾點：1 假如存儲過程名的前三個字符為假如存儲過程名的前三個字符為 sp_，SQL Server 會在會在 Master 數(shù)據(jù)庫中尋找該過程。假如沒能找到合法的過程名，數(shù)據(jù)庫中尋找該過程。假如沒能找到合法的過程名，SQL Server 會尋找會尋找所有者名稱為所有者名稱為 dbo 的過程。的過程。2 參數(shù)可以通過參數(shù)可以通過 value 或或 p

12、arameter_name = value 提供。提供。 3 執(zhí)行存儲過程時，假設(shè)語句是批處理中的第一個語句，那么不一執(zhí)行存儲過程時，假設(shè)語句是批處理中的第一個語句，那么不一定要指定定要指定EXECUTE 關(guān)鍵字。關(guān)鍵字。1011存儲過程3 舉例舉例1 設(shè)計簡單的存儲過程設(shè)計簡單的存儲過程例例從從XSCJ數(shù)據(jù)庫的三個表中查詢，返回學(xué)生學(xué)號、姓名、課程名、數(shù)據(jù)庫的三個表中查詢，返回學(xué)生學(xué)號、姓名、課程名、成績、學(xué)分。該存儲過程不使用任何參數(shù)。成績、學(xué)分。該存儲過程不使用任何參數(shù)。USE XSCJCREATE PROCEDURE student_info/*創(chuàng)立存儲過程創(chuàng)立存儲過程*/ASSELE

13、CT a.學(xué)號學(xué)號, 姓名姓名, 課程名課程名, 成績成績, 學(xué)分學(xué)分 FROM XS a INNER JOIN XS_KC b ON a.學(xué)號學(xué)號 = b.學(xué)號學(xué)號 INNER JOIN KC t ON b.課程號課程號= t.課程號課程號 GO1112存儲過程2 使用帶參數(shù)的存儲過程使用帶參數(shù)的存儲過程例例從從XSCJ數(shù)據(jù)庫的三個表中查詢某人指定課程的成績和學(xué)分。該存儲數(shù)據(jù)庫的三個表中查詢某人指定課程的成績和學(xué)分。該存儲過程承受與傳遞參數(shù)準確匹配的值。過程承受與傳遞參數(shù)準確匹配的值。USE XSCJCREATE PROCEDURE student_info1 name char 8,cna

14、me char16 AS SELECT a.學(xué)號學(xué)號, 姓名姓名, 課程名課程名, 成績成績, 學(xué)分學(xué)分 FROM XS a INNER JOIN XS_KC b ON a.學(xué)號學(xué)號 = b.學(xué)號學(xué)號 INNER JOIN KC t ON b.課程號課程號= t.課程號課程號 WHERE a.姓名姓名=name and t.課程名課程名=cnameGO1213存儲過程3 使用帶有通配符參數(shù)的存儲過程使用帶有通配符參數(shù)的存儲過程例例從三個表的聯(lián)接中返回指定學(xué)生的學(xué)號、姓名、所選課程名稱及該從三個表的聯(lián)接中返回指定學(xué)生的學(xué)號、姓名、所選課程名稱及該課程的成績。該存儲過程在參數(shù)中使用了形式匹配，假如

15、沒有提供參數(shù)，課程的成績。該存儲過程在參數(shù)中使用了形式匹配，假如沒有提供參數(shù)，那么使用預(yù)設(shè)的默認值。那么使用預(yù)設(shè)的默認值。USE XSCJCREATE PROCEDURE st_infoname varchar30 = 劉劉%AS SELECT a.學(xué)號學(xué)號,a.姓名姓名,c.課程名課程名,b.成績成績FROM XS a INNER JOIN XS_KC b ON a.學(xué)號學(xué)號 =b.學(xué)號學(xué)號 INNER JOIN KC c ON c.課程號課程號= b.課程號課程號 WHERE 姓名姓名 LIKE nameGO 1314存儲過程4 4 使用帶使用帶OUTPUTOUTPUT參數(shù)的存儲過程參數(shù)的

16、存儲過程 例例 用于計算指定學(xué)生的總學(xué)分，存儲過程中使用了一個輸入?yún)?shù)和一用于計算指定學(xué)生的總學(xué)分，存儲過程中使用了一個輸入?yún)?shù)和一個輸出參數(shù)。個輸出參數(shù)。USE XSCJUSE XSCJGOGOIF EXISTSIF EXISTSSELECT name FROM sysobjectsSELECT name FROM sysobjects WHERE name = totalcredit AND type = P WHERE name = totalcredit AND type = PDROP PROCEDURE totalcreditDROP PROCEDURE totalcreditGO

17、GOUSE XSCJUSE XSCJGOGOCREATE PROCEDURE totalcredit name varcharCREATE PROCEDURE totalcredit name varchar4040, , total int OUTPUTtotal int OUTPUTASASSELECT total= SUMSELECT total= SUM學(xué)分學(xué)分FROM XS,XS_KC,KCFROM XS,XS_KC,KCWHERE WHERE 姓名姓名=name AND XS.=name AND XS.學(xué)號學(xué)號= XS_KC.= XS_KC.學(xué)號學(xué)號 GROUP BY XS.GRO

18、UP BY XS.學(xué)號學(xué)號GOGO1415存儲過程5 5 使用使用OUTPUTOUTPUT游標參數(shù)的存儲過程游標參數(shù)的存儲過程OUTPUT OUTPUT 游標參數(shù)用于返回存儲過程的部分游標。游標參數(shù)用于返回存儲過程的部分游標。 例例 在在 XSCJXSCJ數(shù)據(jù)庫的數(shù)據(jù)庫的XSXS表上聲明并翻開一個游標。表上聲明并翻開一個游標。USE XSCJUSE XSCJIF EXISTS IF EXISTS SELECT name FROM sysobjects SELECT name FROM sysobjects WHERE name = st_cursor and type = P WHERE na

19、me = st_cursor and type = PDROP PROCEDURE st_cursorDROP PROCEDURE st_cursorGOGOCREATE PROCEDURE st_cursor st_cursor CURSOR VARYING CREATE PROCEDURE st_cursor st_cursor CURSOR VARYING OUTPUTOUTPUTASASSET st_cursor = CURSOR FORWARD_ONLY STATIC FORSET st_cursor = CURSOR FORWARD_ONLY STATIC FORSELECT SE

20、LECT * * FROM XS FROM XSOPEN st_cursorOPEN st_cursorGOGO1516存儲過程 6 6 使用使用 WITH ENCRYPTION WITH ENCRYPTION 選項選項WITH ENCRYPTION WITH ENCRYPTION 子句對用戶隱藏存儲過程的文本。子句對用戶隱藏存儲過程的文本。 例例 創(chuàng)立加密過程，使用創(chuàng)立加密過程，使用 sp_helptext sp_helptext 系統(tǒng)存儲過程獲取關(guān)于加密過系統(tǒng)存儲過程獲取關(guān)于加密過程的信息，然后嘗試直接從程的信息，然后嘗試直接從 syscomments syscomments 表中獲取關(guān)于

21、該過程的信息。表中獲取關(guān)于該過程的信息。IF EXISTS IF EXISTS SELECT name FROM sysobjectsSELECT name FROM sysobjects WHERE name = encrypt_this AND type = P WHERE name = encrypt_this AND type = P DROP PROCEDURE encrypt_this DROP PROCEDURE encrypt_thisGOGOUSE XSCJUSE XSCJGOGOCREATE PROCEDURE encrypt_this WITH ENCRYPTIONCRE

22、ATE PROCEDURE encrypt_this WITH ENCRYPTIONASASSELECT SELECT * * FROM XS FROM XSGOGO1617存儲過程7 創(chuàng)立用戶定義的系統(tǒng)存儲過程創(chuàng)立用戶定義的系統(tǒng)存儲過程例例 創(chuàng)立一個過程，顯示表名以創(chuàng)立一個過程，顯示表名以 xs 開頭的所有表及其對應(yīng)的索引。假如開頭的所有表及其對應(yīng)的索引。假如沒有指定參數(shù)，該過程將返回表名以沒有指定參數(shù)，該過程將返回表名以 kc 開頭的所有表及對應(yīng)的索引開頭的所有表及對應(yīng)的索引。IF EXISTS SELECT name FROM sysobjectsWHERE name = sp_show

23、table AND type = P DROP PROCEDURE sp_showtableGOUSE masterCREATE PROCEDURE sp_showtable TABLE varchar30 = kc%AS SELECT AS TABLE_NAME, AS INDEX_NAME, indid AS INDEX_IDFROM sysindexes inx INNER JOIN sysobjects tab ON tab.id = inx.id WHERE LIKE TABLEGO USE XSCJEXEC sp_showtab

24、le xs%GO1718存儲過程2利用企業(yè)管理器創(chuàng)立用戶存儲過程利用企業(yè)管理器創(chuàng)立用戶存儲過程第第1步步 在在SQL Server 企業(yè)管理器窗口中，選擇相應(yīng)的效勞器、數(shù)據(jù)企業(yè)管理器窗口中，選擇相應(yīng)的效勞器、數(shù)據(jù)庫和存儲過程圖標本例選擇庫和存儲過程圖標本例選擇XSCJ數(shù)據(jù)庫，單擊鼠標右鍵，出現(xiàn)如數(shù)據(jù)庫，單擊鼠標右鍵，出現(xiàn)如下圖的快捷菜單。下圖的快捷菜單。 第第2步步 選擇新建存儲過程，那么出現(xiàn)編輯存儲過程的屬性窗口，在窗選擇新建存儲過程，那么出現(xiàn)編輯存儲過程的屬性窗口，在窗口中輸入定義的存儲過程，如下圖，然后選擇口中輸入定義的存儲過程，如下圖，然后選擇“確定按鈕。確定按鈕。 1819用戶存儲過

25、程的編輯修改 語法格式：語法格式：ALTER PROC EDURE procedure_name ; number parameter data_type VARYING 0= default OUTPUT ,.n1 WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n2 例例 對存儲過程對存儲過程student_info1student_info1進展修改。進展修改。USE XSCJUSE XSCJGOGOALTER PROCEDURE student_info1ALT

26、ER PROCEDURE student_info1 name charname char8 8,cname char,cname char1616 AS AS SELECT a.SELECT a.學(xué)號學(xué)號, , 姓名姓名, , 課程名課程名, , 成績成績, , 學(xué)分學(xué)分 FROM XS a INNER join XS_KC bFROM XS a INNER join XS_KC b ON a.ON a.學(xué)號學(xué)號 = b.= b.學(xué)號學(xué)號 INNER JOIN KC tINNER JOIN KC t ON b.ON b.課程號課程號= t.= t.課程號課程號 WHERE a.WHERE a

27、.姓名姓名=name and t.=name and t.課程名課程名=cname=cnameGOGO1920用戶存儲過程的刪除 使用使用DROP PROCEDUREDROP PROCEDURE語句可永久地刪除存儲過程。在此之前，必須確認該存語句可永久地刪除存儲過程。在此之前，必須確認該存儲過程沒有任何依賴關(guān)系。儲過程沒有任何依賴關(guān)系。語法格式：語法格式：DROP PROCEDURE procedure ,.n DROP PROCEDURE procedure ,.n 例例 刪除刪除 XSCJXSCJ數(shù)據(jù)庫中的數(shù)據(jù)庫中的student_info1 student_info1 存儲過程。存儲過程

28、。USE XSCJGODROP PROCEDURE student_info1procedureprocedure指要刪除的存儲過程或存儲過程組的名稱；指要刪除的存儲過程或存儲過程組的名稱；n n：表示可以指定多個存儲過程同時刪除。表示可以指定多個存儲過程同時刪除。 2021存儲過程的平安性例如假定假定EMPEMP表是某用戶表是某用戶USER1USER1的私有表，用戶的私有表，用戶SCOTTSCOTT是開發(fā)者，是開發(fā)者，最終用戶最終用戶GREENGREEN，如今要求，如今要求GREENGREEN只能通過只能通過SCOTTSCOTT創(chuàng)立的存創(chuàng)立的存儲過程儲過程HIRE_EMPHIRE_EMP存取

29、存取EMPEMP表表, ,該存儲過程查詢或插入雇員該存儲過程查詢或插入雇員記錄記錄. .從從USER1USER1用戶環(huán)境下用戶環(huán)境下, ,為為SCOTTSCOTT用戶授予對用戶授予對EMPEMP表的相表的相應(yīng)權(quán)限。應(yīng)權(quán)限。解：解：GRANT SELECTGRANT SELECT，INSERTINSERT，UPDATEUPDATE，DELETE ON DELETE ON EMP TO SCOTTEMP TO SCOTT；SCOTTSCOTT一旦創(chuàng)立完一旦創(chuàng)立完HIRE_EMPHIRE_EMP過程過程, ,給給GREENGREEN用戶授予對該過用戶授予對該過程的程的EXECUTEEXECUTE權(quán)限

30、權(quán)限. . GRANT EXECUTE ON HIRE_EMP TO GREEN; GRANT EXECUTE ON HIRE_EMP TO GREEN;2122使用系統(tǒng)存儲過程來查看存儲過程 sp_help：用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型：用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型 sp_help objname= name 參數(shù)參數(shù)name為要查看的存儲過程的名稱。為要查看的存儲過程的名稱。 sp_helptext：用于顯示存儲過程的源代碼：用于顯示存儲過程的源代碼 sp_helptext objname= name 參數(shù)參數(shù)name為要查看的存儲過程的名稱。為要查看的存儲過程的名稱。sp_d

31、epends：顯示和存儲過程相關(guān)的數(shù)據(jù)庫對象：顯示和存儲過程相關(guān)的數(shù)據(jù)庫對象 sp_depends objname=object 參數(shù)參數(shù)object為要查看依賴關(guān)系的存儲過程的名稱。為要查看依賴關(guān)系的存儲過程的名稱。sp_stored_procedures：用于返回當(dāng)前數(shù)據(jù)庫中的存儲過程列：用于返回當(dāng)前數(shù)據(jù)庫中的存儲過程列表表2223觸發(fā)器觸發(fā)器是一種特殊的存儲過程，它在特定語言事件發(fā)生時觸發(fā)器是一種特殊的存儲過程，它在特定語言事件發(fā)生時自動執(zhí)行，通常用于實現(xiàn)強迫業(yè)務(wù)規(guī)那么和數(shù)據(jù)完好性。自動執(zhí)行，通常用于實現(xiàn)強迫業(yè)務(wù)規(guī)那么和數(shù)據(jù)完好性。觸發(fā)器主要是通過事件進展觸發(fā)而被執(zhí)行的，而存儲過程觸發(fā)器

32、主要是通過事件進展觸發(fā)而被執(zhí)行的，而存儲過程可以通過存儲過程名稱而被直接調(diào)用?？梢酝ㄟ^存儲過程名稱而被直接調(diào)用。觸發(fā)器的主要作用是實現(xiàn)由主鍵和外鍵所不能保證的復(fù)雜觸發(fā)器的主要作用是實現(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完好性和數(shù)據(jù)一致性。的參照完好性和數(shù)據(jù)一致性。2324 觸發(fā)器主要優(yōu)點 觸發(fā)器是自動的：當(dāng)對表中的數(shù)據(jù)作了任何修改比方手觸發(fā)器是自動的：當(dāng)對表中的數(shù)據(jù)作了任何修改比方手工輸入或者應(yīng)用程序采取的操作之后立即被激活。工輸入或者應(yīng)用程序采取的操作之后立即被激活。 觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進展層疊更改。觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進展層疊更改。 觸發(fā)器可以強迫限制，這些限制比用觸

33、發(fā)器可以強迫限制，這些限制比用 CHECK CHECK 約束所定義約束所定義的更復(fù)雜。的更復(fù)雜。 2425利用SQL命令創(chuàng)立觸發(fā)器 語法格式語法格式CREATE TRIGGER trigger_name ON table | view /*指定操作對象指定操作對象*/ WITH ENCRYPTION /*說明是否采用加密方式說明是否采用加密方式*/ FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION /*說明該觸發(fā)器不用于復(fù)制說明該觸發(fā)器不用于復(fù)制*/ AS IF UPDATE column AND

34、| OR UPDATE column .n | IF COLUMNS_UPDATED bitwise_operator updated_bitmask comparison_operator column_bitmask .n /*兩個兩個IF子句用于說明觸發(fā)器執(zhí)行的條件子句用于說明觸發(fā)器執(zhí)行的條件*/ sql_statement .n /*一條或假設(shè)干條一條或假設(shè)干條SQL語句語句*/ 2526利用SQL命令創(chuàng)立觸發(fā)器2觸發(fā)器中使用的特殊表觸發(fā)器中使用的特殊表 inserted 邏輯表：當(dāng)向表中插入數(shù)據(jù)時，邏輯表：當(dāng)向表中插入數(shù)據(jù)時，INSERT觸發(fā)器觸發(fā)執(zhí)行，觸發(fā)器觸發(fā)執(zhí)行，新的記錄插入到

35、觸發(fā)器表和新的記錄插入到觸發(fā)器表和inserted表中。表中。 deleted邏輯表：用于保存已從表中刪除的記錄，當(dāng)觸發(fā)一個邏輯表：用于保存已從表中刪除的記錄，當(dāng)觸發(fā)一個DELETE觸發(fā)器時，被刪除的記錄存放到觸發(fā)器時，被刪除的記錄存放到deleted邏輯表中。邏輯表中。 3使用觸發(fā)器的限制使用觸發(fā)器的限制使用觸發(fā)器有以下限制：使用觸發(fā)器有以下限制：1 CREATE TRIGGER 必須是批處理中的第一條語句，并且只能應(yīng)用必須是批處理中的第一條語句，并且只能應(yīng)用到一個表中。到一個表中。 2 觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫中創(chuàng)立，但觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫中創(chuàng)立，但觸發(fā)器可以引

36、用當(dāng)前數(shù)據(jù)庫的外部對象。的外部對象。 3 假如指定觸發(fā)器所有者名限定觸發(fā)器，要以一樣的方式限定表名。假如指定觸發(fā)器所有者名限定觸發(fā)器，要以一樣的方式限定表名。 4 在同一在同一CREATE TRIGGER語句中，可以為多種操作如語句中，可以為多種操作如 INSERT 和和 UPDATE定義一樣的觸發(fā)器操作。定義一樣的觸發(fā)器操作。 5 一個表的外鍵在一個表的外鍵在 DELETE、UPDATE 操作上定義了級聯(lián)，不能在操作上定義了級聯(lián)，不能在該表上定義該表上定義 INSTEAD OF DELETE、INSTEAD OF UPDATE 觸發(fā)器。觸發(fā)器。2627利用SQL命令創(chuàng)立觸發(fā)器 7 7 在觸發(fā)

37、器內(nèi)可以指定任意的在觸發(fā)器內(nèi)可以指定任意的 SET SET 語句，所選擇的語句，所選擇的 SET SET 選項選項在觸發(fā)器執(zhí)行期間有效，并在觸發(fā)器執(zhí)行完后恢復(fù)到以前的設(shè)置。在觸發(fā)器執(zhí)行期間有效，并在觸發(fā)器執(zhí)行完后恢復(fù)到以前的設(shè)置。 8 8 觸發(fā)器中不允許包含以下觸發(fā)器中不允許包含以下 T-SQL T-SQL 語句：語句：CREATE DATABASE CREATE DATABASE 、ALTER DATABASE ALTER DATABASE 、LOAD DATABASE LOAD DATABASE 、RESTORE RESTORE DATABASE DATABASE 、DROP DATABA

38、SEDROP DATABASE、LOAD LOG LOAD LOG 、RESTORE LOG RESTORE LOG 、DISK INITDISK INIT、DISK RESIZEDISK RESIZE和和RECONFIGURERECONFIGURE 9 9 觸發(fā)器不能返回任何結(jié)果，為了阻止從觸發(fā)器返回結(jié)果，不觸發(fā)器不能返回任何結(jié)果，為了阻止從觸發(fā)器返回結(jié)果，不要在觸發(fā)器定義中包含要在觸發(fā)器定義中包含 SELECT SELECT 語句或變量賦值。語句或變量賦值。4 4權(quán)限權(quán)限CREATE TRIGGER CREATE TRIGGER 權(quán)限默認授予定義觸發(fā)器的表所有者、權(quán)限默認授予定義觸發(fā)器的表

39、所有者、sysadmin sysadmin 固固定效勞器角色成員、定效勞器角色成員、db_owner db_owner 和和 db_ddladmin db_ddladmin 固定數(shù)據(jù)庫角色成員，固定數(shù)據(jù)庫角色成員，并且不可轉(zhuǎn)讓。并且不可轉(zhuǎn)讓。2728利用SQL命令創(chuàng)立觸發(fā)器5舉例舉例例例對于對于XSCJ數(shù)據(jù)庫，假如在數(shù)據(jù)庫，假如在XS表中添加或更改數(shù)據(jù)，那么將向客戶表中添加或更改數(shù)據(jù)，那么將向客戶端顯示一條信息。端顯示一條信息。USE XSCJ /*使用帶有提示消息的觸發(fā)器使用帶有提示消息的觸發(fā)器*/IF EXISTS SELECT name FROM sysobjects WHERE nam

40、e = reminder AND type = TR DROP TRIGGER reminderGOCREATE TRIGGER reminder ON XSFOR INSERT, UPDATE AS RAISERROR 4008, 16, 10GO注：注：4008為用戶自定義消息，可用為用戶自定義消息，可用sp_addmessage將自定義消息添加到將自定義消息添加到sysmessages表中。表中。2829利用SQL命令創(chuàng)立觸發(fā)器例例在數(shù)據(jù)庫在數(shù)據(jù)庫XSCJ中創(chuàng)立一觸發(fā)器，當(dāng)向中創(chuàng)立一觸發(fā)器，當(dāng)向XS_KC表插入一記錄時，檢查表插入一記錄時，檢查該記錄的學(xué)號在該記錄的學(xué)號在XS表是否存在，

41、檢查課程號在表是否存在，檢查課程號在KC表中是否存在，假設(shè)有表中是否存在，假設(shè)有一項為否，那么不允許插入。一項為否，那么不允許插入。USE XSCJIF EXISTS SELECT name FROM sysobjects WHERE name = check_trig AND type = TR DROP TRIGGER check_trigGOCREATE TRIGGER check_trig ON XS_KCFOR INSERTASSELECT * FROM inserted a WHERE a.學(xué)號學(xué)號 NOT IN SELECT b.學(xué)號學(xué)號 FROM XS b OR a.課程號課程

42、號 NOT IN SELECT c.課程號課程號 FROM KC cBEGIN RAISERROR 違犯數(shù)據(jù)的一致性違犯數(shù)據(jù)的一致性., 16, 1 ROLLBACK TRANSACTIONEND2930利用SQL命令創(chuàng)立觸發(fā)器例例在在XSCJ數(shù)據(jù)庫的數(shù)據(jù)庫的XS_KC表上創(chuàng)立一觸發(fā)器，假設(shè)對學(xué)號列和課程號表上創(chuàng)立一觸發(fā)器，假設(shè)對學(xué)號列和課程號列修改，那么給出提示信息，并取消修改操作。列修改，那么給出提示信息，并取消修改操作。USE XSCJGOCREATE TRIGGER update_trigON XS_KC FOR update AS/*檢查學(xué)號列檢查學(xué)號列C0和課程號列和課程號列C1是

43、否被修改，假如有某些列被修是否被修改，假如有某些列被修改了，那么取消修改操作。改了，那么取消修改操作。*/IF COLUMNS_UPDATED & 3 0BEGINRAISERROR 違犯數(shù)據(jù)的一致性違犯數(shù)據(jù)的一致性., 16, 1 ROLLBACK TRANSACTION ENDGO3031利用SQL命令創(chuàng)立觸發(fā)器6. INSTEAD OF6. INSTEAD OF觸發(fā)器的設(shè)計觸發(fā)器的設(shè)計假如視圖的數(shù)據(jù)來自于多個基表，那么必須使用假如視圖的數(shù)據(jù)來自于多個基表，那么必須使用INSTEAD OF INSTEAD OF 觸發(fā)觸發(fā)器支持引用表中數(shù)據(jù)的插入、更新和刪除操作。器支持引用表中數(shù)據(jù)的

44、插入、更新和刪除操作。假如視圖的列為以下幾種情況之一：假如視圖的列為以下幾種情況之一： 1 1基表中的計算列?；碇械挠嬎懔?。2 2IDENTITY INSERT IDENTITY INSERT 為為 OFF OFF 的基表中的標識列。的基表中的標識列。3 3具有具有 timestamp timestamp 數(shù)據(jù)類型的基表列。數(shù)據(jù)類型的基表列。 該視圖的該視圖的INSERTINSERT語句必須為這些列指定值，語句必須為這些列指定值，INSTEAD OF INSTEAD OF 觸發(fā)器觸發(fā)器在構(gòu)成將值插入基表的在構(gòu)成將值插入基表的 INSERT INSERT 語句時會忽略指定的值。語句時會忽略指定

45、的值。3132 例例 在在XSCJXSCJ數(shù)據(jù)庫中創(chuàng)立表、視圖和觸發(fā)器，以說明數(shù)據(jù)庫中創(chuàng)立表、視圖和觸發(fā)器，以說明INSTEAD OF INSERTINSTEAD OF INSERT觸發(fā)器觸發(fā)器的使用。的使用。USE XSCJUSE XSCJCREATE TABLE booksCREATE TABLE books BookKey int IDENTITY BookKey int IDENTITY1,11,1, , BookName nvarchar BookName nvarchar1010 NOT NULL, NOT NULL, Color nvarchar Color nvarchar10

46、10 NOT NULL, NOT NULL, ComputedCol AS ComputedCol AS BookName +ColorBookName +Color, , Pages int Pages intGOGOCREATE VIEW View2/CREATE VIEW View2/* *建立一個視圖，包含基表的所有列建立一個視圖，包含基表的所有列* */ /AS AS SELECT BookKey, BookName ,Color, ComputedCol, PagesSELECT BookKey, BookName ,Color, ComputedCol, PagesFROM bo

47、oksFROM booksGOGO/ /* *在在View2View2視圖上創(chuàng)立一個視圖上創(chuàng)立一個 INSTEAD OF INSERT INSTEAD OF INSERT 觸發(fā)器觸發(fā)器* */ /CREATE TRIGGER InsteadTrig on View2CREATE TRIGGER InsteadTrig on View2INSTEAD OF INSERTINSTEAD OF INSERTASASBEGIN /BEGIN /* *實際插入時，實際插入時，INSERTINSERT語句中不包含語句中不包含BookKeyBookKey字段和字段和ComputedColComputedCo

48、l值值* */ /INSERT INTO books INSERT INTO books SELECT BookName ,Color, Pages FROM insertedSELECT BookName ,Color, Pages FROM insertedENDENDGOGO3233利用企業(yè)管理器創(chuàng)立觸發(fā)器 步驟如下：步驟如下： 第第1步步 展開效勞器組，然后展開效勞器。展開效勞器組，然后展開效勞器。第第2步步 展開展開“數(shù)據(jù)庫文件夾，展開將定義觸發(fā)器的表所屬的數(shù)據(jù)數(shù)據(jù)庫文件夾，展開將定義觸發(fā)器的表所屬的數(shù)據(jù)庫，然后單擊庫，然后單擊“表文件夾。表文件夾。第第3步步 選擇將在其上創(chuàng)立觸發(fā)器

49、的表右擊，出現(xiàn)快捷菜單，選擇選擇將在其上創(chuàng)立觸發(fā)器的表右擊，出現(xiàn)快捷菜單，選擇“所所有任務(wù)菜單項下的有任務(wù)菜單項下的“管理觸發(fā)器子菜單項，如下圖。執(zhí)行該命令管理觸發(fā)器子菜單項，如下圖。執(zhí)行該命令后，進入如下圖的界面。后，進入如下圖的界面。 第第4步步 在在“名稱中，單擊名稱中，單擊 “新建，在新建，在“文本框中輸入觸發(fā)器文文本框中輸入觸發(fā)器文本。假設(shè)要檢查語法，單擊本。假設(shè)要檢查語法，單擊“檢查語法命令。檢查語法命令。 3334觸發(fā)器的修改1利用利用SQL命令修改觸發(fā)器命令修改觸發(fā)器語法格式：語法格式：ALTER TRIGGER trigger_nameON table | view WITH

50、 ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE NOT FOR REPLICATION AS sql_statement .n | FOR | AFTER | INSTEAD OF INSERT , UPDATE NOT FOR REPLICATION AS IF UPDATE column AND | OR UPDATE column .n | IF COLUMNS_UPDATED bitwise_operator updated_bitmask comparison_operator column_bitmask

51、.n sql_statement .n 3435觸發(fā)器的修改 例例 修改修改XSCJXSCJ數(shù)據(jù)庫中在數(shù)據(jù)庫中在XSXS表上定義的觸發(fā)器表上定義的觸發(fā)器reminderreminder。USE XSCJUSE XSCJALTER TRIGGER reminder ON XSALTER TRIGGER reminder ON XSFOR UPDATE FOR UPDATE AS RAISERROR AS RAISERROR “執(zhí)行的操作是修改執(zhí)行的操作是修改, 16, 10, 16, 10GOGO2. 2. 通過企業(yè)管理器修改觸發(fā)器通過企業(yè)管理器修改觸發(fā)器進入企業(yè)管理器，修改觸發(fā)器的步驟與創(chuàng)立的

52、步驟一樣，進入界面后進入企業(yè)管理器，修改觸發(fā)器的步驟與創(chuàng)立的步驟一樣，進入界面后在在“名稱對應(yīng)的下拉表中選擇要修改的觸發(fā)器名即可進入觸發(fā)器修改名稱對應(yīng)的下拉表中選擇要修改的觸發(fā)器名即可進入觸發(fā)器修改狀態(tài)。狀態(tài)。說明：說明：trigger_name：指要更改的現(xiàn)有觸發(fā)器。：指要更改的現(xiàn)有觸發(fā)器。 假如原來的觸發(fā)器定義是用假如原來的觸發(fā)器定義是用 WITH ENCRYPTION 或或 RECOMPILE 創(chuàng)立的，創(chuàng)立的，那么只有在那么只有在ALTER TRIGGER中也包含這些選項時，這些選項才有效。中也包含這些選項時，這些選項才有效。其他參數(shù)含義參考創(chuàng)立觸發(fā)器命令部分。其他參數(shù)含義參考創(chuàng)立觸發(fā)器

53、命令部分。3536觸發(fā)器的刪除1. 利用利用SQL命令刪除觸發(fā)器命令刪除觸發(fā)器語法格式：語法格式：DROP TRIGGER trigger ,.n 說明：說明：trigger：指要刪除的觸發(fā)器名稱，包含觸發(fā)器所有者名。：指要刪除的觸發(fā)器名稱，包含觸發(fā)器所有者名。n：表示可以指定多個觸發(fā)器。：表示可以指定多個觸發(fā)器。例例 刪除觸發(fā)器刪除觸發(fā)器reminder。USE XSCJIF EXISTS SELECT name FROM sysobjects WHERE name = reminder AND type = TR DROP TRIGGER reminderGO2通過企業(yè)管理器刪除觸發(fā)器通過

54、企業(yè)管理器刪除觸發(fā)器進入界面后在進入界面后在“名稱對應(yīng)的下拉表中選擇要刪除的觸發(fā)器名，然名稱對應(yīng)的下拉表中選擇要刪除的觸發(fā)器名，然后選擇后選擇“刪除按鈕。刪除按鈕。 3637sp_help、sp_helptext和sp_depends詳細用處和語法形式 sp_helpsp_help：用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、：用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)立時間。屬性、類型和創(chuàng)立時間。sp_help sp_help 觸發(fā)器名稱觸發(fā)器名稱 sp_helptextsp_helptext：用于查看觸發(fā)器的正文信息：用于查看觸發(fā)器的正文信息sp_helptext sp_helptext 觸發(fā)器名稱觸發(fā)器名稱 sp_dependssp_depends：用于查看指定觸發(fā)器所引用的表或者指定的：用于查看指定觸發(fā)器所引用的表或者指定的表涉及到的所有觸發(fā)器。表涉及到的所有觸發(fā)器。sp_depends sp_depends 觸發(fā)器名稱觸發(fā)器名稱 sp_depends sp_depends 表名表名 3738附例：用戶登錄驗證CREATE PROC upUserLoginCREATE PROC upUser