數(shù)據(jù)庫技術(shù)及應(yīng)用第10章 觸發(fā)器和游標

1、數(shù)據(jù)庫技術(shù)及應(yīng)用第數(shù)據(jù)庫技術(shù)及應(yīng)用第1010章章 觸發(fā)器和游標觸發(fā)器和游標2021-12-16210.1 觸發(fā)器概述觸發(fā)器概述 觸發(fā)器實際上就是一種特殊類型的存儲過觸發(fā)器實際上就是一種特殊類型的存儲過程，它在一定操作時自動觸發(fā)執(zhí)行。在程，它在一定操作時自動觸發(fā)執(zhí)行。在SQL Server 2005之前的版本中，觸發(fā)器是針對數(shù)之前的版本中，觸發(fā)器是針對數(shù)據(jù)表的特殊的存儲過程，當這個表發(fā)生了據(jù)表的特殊的存儲過程，當這個表發(fā)生了Insert、Update或或Delete操作時，如果該表操作時，如果該表有對應(yīng)操作的觸發(fā)器，這個觸發(fā)器就會自動激有對應(yīng)操作的觸發(fā)器，這個觸發(fā)器就會自動激活執(zhí)行。在活執(zhí)行。在

2、SQL Server 2005中，觸發(fā)器有了中，觸發(fā)器有了更進一步的功能，在數(shù)據(jù)表庫發(fā)生更進一步的功能，在數(shù)據(jù)表庫發(fā)生Create、Alter和和Drop操作時，也會自動激活執(zhí)行。操作時，也會自動激活執(zhí)行。2021-12-163完成更復(fù)雜的數(shù)據(jù)約束 檢查所做的SQL所作的操作是否允許修改其它數(shù)據(jù)表里的數(shù)據(jù) 調(diào)用更多的存儲過程 返回自定義的錯誤信息 更改原本要操作的SQL語句 防止數(shù)據(jù)表結(jié)構(gòu)被更改或數(shù)據(jù)表被刪除 2021-12-164DML觸發(fā)器觸發(fā)器 DML觸發(fā)器是當數(shù)據(jù)庫效勞器中發(fā)生觸發(fā)器是當數(shù)據(jù)庫效勞器中發(fā)生數(shù)據(jù)操作語言數(shù)據(jù)操作語言Data Manipulation Language事件時

3、執(zhí)行的存儲過程事件時執(zhí)行的存儲過程DDL觸發(fā)器觸發(fā)器 DDL觸發(fā)器是在響應(yīng)數(shù)據(jù)定義語言觸發(fā)器是在響應(yīng)數(shù)據(jù)定義語言Data Definition Language事件時執(zhí)行的事件時執(zhí)行的存儲過存儲過程。程。 2021-12-16510.2 DML觸發(fā)器觸發(fā)器 After觸發(fā)器：這類觸發(fā)器是在記錄已經(jīng)改變完觸發(fā)器：這類觸發(fā)器是在記錄已經(jīng)改變完之后之后after，才會被激活執(zhí)行，它主要是用，才會被激活執(zhí)行，它主要是用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯誤，于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯誤，也可以用也可以用Rollback Transaction語句來回滾本語句來回滾本次的操作。次的操作。Ins

4、tead Of觸發(fā)器：這類觸發(fā)器一般是用來取代觸發(fā)器：這類觸發(fā)器一般是用來取代原本要進行的操作，在記錄變更之前發(fā)生的，原本要進行的操作，在記錄變更之前發(fā)生的，它并不去執(zhí)行原來它并不去執(zhí)行原來SQL語句里的操作語句里的操作Insert、Update、Delete，而去執(zhí)行觸發(fā)器本身所，而去執(zhí)行觸發(fā)器本身所定義的操作。定義的操作。 2021-12-166After觸發(fā)器的工作原理觸發(fā)器的工作原理 After觸發(fā)器是在記錄更變完之后才被激活執(zhí)行。Instead Of觸發(fā)器的工作原理觸發(fā)器的工作原理 Instead Of觸發(fā)器與After觸發(fā)器不同。After觸發(fā)器是在Insert、Update和De

5、lete操作完成后才激活的，而Instead Of觸發(fā)器，是在這些操作進行之前就激活了，并且不再去執(zhí)行原來的SQL操作，而去運行觸發(fā)器本身的SQL語句。 2021-12-167CREATE TRIGGER語句必須是批處理中的第一 個語句創(chuàng)立DML觸發(fā)器的權(quán)限默認分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶 DML觸發(fā)器為數(shù)據(jù)庫對象，其名稱必須遵循標識符的命名規(guī)那么 只能在當前數(shù)據(jù)庫中創(chuàng)立DML觸發(fā)器 不能對臨時表或系統(tǒng)表創(chuàng)立DML觸發(fā)器WRITETEXT語句不會觸發(fā)INSERT或UPDATE觸發(fā)器2021-12-168步驟如下：1啟動“SQL Server Management Studio，

6、在“對象資源管理器下選擇“數(shù)據(jù)庫，定位到具體的數(shù)據(jù)庫，展開其下的“表樹型目錄，找到具體的表，并選中其下的“觸發(fā)器項，如以以以下圖所示。2021-12-169在圖形界面下創(chuàng)立觸發(fā)器在圖形界面下創(chuàng)立觸發(fā)器2021-12-1610在圖形界面下創(chuàng)立觸發(fā)器在圖形界面下創(chuàng)立觸發(fā)器2右擊“觸發(fā)器，在彈出的快捷菜單中選擇“新建觸發(fā)器選項，彈出“查詢編輯器對話框，在“查詢編輯器的編輯區(qū)里SQL Server已經(jīng)預(yù)寫入了一些建立觸發(fā)器相關(guān)的SQL語句，如以以以下圖所示。2021-12-1611在圖形界面下創(chuàng)立觸發(fā)器在圖形界面下創(chuàng)立觸發(fā)器3修改“查詢編輯器里的代碼4單擊工具欄中的“分析按鈕，檢查一下是否語法有錯，如

7、果在“結(jié)果對話框中出現(xiàn)“命令已成功完成，那么表示語法沒有錯誤。 5語法檢查無誤后，單擊“執(zhí)行按鈕，生成觸發(fā)器。2021-12-1612用用SQL語句創(chuàng)立觸發(fā)器語句創(chuàng)立觸發(fā)器創(chuàng)立AFTER觸發(fā)器的語法代碼如下：CREATE TRIGGER ON .WITH ENCRYPTION|EXECUTE AS CALLER|SELF|FOR|AFTERINSERT,UPDATE,DELETEWITH APPENDNOT FOR REPLICATIONAS|EXTERNAL NAME 2021-12-1613用用SQL語句創(chuàng)立觸發(fā)器語句創(chuàng)立觸發(fā)器主要參數(shù)說明：trigger_name：觸發(fā)器的名稱,必須遵循

8、標識符規(guī)schema_name：觸發(fā)器所屬架構(gòu)的名稱。table|view：指定觸發(fā)器所在的數(shù)據(jù)表或視圖。WITH ENCRYPTION：對CREATE TRIGGER語句的文本進行加密。EXECUTE AS：用于執(zhí)行該觸發(fā)器的平安上下文。AFTER：指定DML觸發(fā)器僅在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行時才被激發(fā)。DELETE,INSERT,UPDATE：指定數(shù)據(jù)修改語句，這些語句可在DML觸發(fā)器對此表或視圖進行嘗試時激活該觸發(fā)器。必須至少指定一個選項。WITH APPEND：指定應(yīng)該再添加一個現(xiàn)有類型的觸發(fā)器。 2021-12-1614AFTER觸發(fā)器例如觸發(fā)器例如 修改學生表中的

9、數(shù)據(jù)時，下述觸發(fā)器將向客戶顯示一條消息。CREATE TRIGGER 學生_updateON 學生表AFTER UPDATE ASBEGINRAISERROR (注意：有人修改學生表的數(shù)據(jù),16,10)ENDGO2021-12-1615創(chuàng)立創(chuàng)立INSTEAD OF觸發(fā)器觸發(fā)器 創(chuàng)立INSTEAD OF觸發(fā)器的語法代碼如下 ：CREATE TRIGGER ON .WITH ENCRYPTION|EXECUTE AS CALLER|SELF|INSTEAD OFINSERT,UPDATE,DELETEWITH APPENDNOT FOR REPLICATIONAS|EXTERNAL NAME 創(chuàng)立

10、Instead Of觸發(fā)器與創(chuàng)立After觸發(fā)器的語法幾乎一樣，只是簡單地把After改為Instead Of。 2021-12-1616INSTEAD OF觸發(fā)器例如觸發(fā)器例如例例10.2 當有人試圖修改學生表中的數(shù)據(jù)，利用下述觸發(fā)器可以跳過當有人試圖修改學生表中的數(shù)據(jù)，利用下述觸發(fā)器可以跳過修改數(shù)據(jù)的修改數(shù)據(jù)的SQL語句防止數(shù)據(jù)被修改，并向客戶端顯示一條消語句防止數(shù)據(jù)被修改，并向客戶端顯示一條消息。息。CREATE TRIGGER 學生學生_updateON 學生表學生表INSTEAD OF UPDATE ASBEGINRAISERROR (對不起，學生表的數(shù)據(jù)不允許修改對不起，學生表的數(shù)

11、據(jù)不允許修改,16,10)ENDGO2021-12-1617查看觸發(fā)器查看觸發(fā)器1.通過 SQL Server Management Studio2021-12-1618查看觸發(fā)器1sp_help 系統(tǒng)存儲過程sp_help可以了解如觸發(fā)器名稱、類型、創(chuàng)立時間等根本信息，其語法格式為： sp_help 觸發(fā)器名 例如：sp_help 學生_insert 2sp_helptext 系統(tǒng)存儲過程sp_helptext可以查看觸發(fā)器的文本信息，其語法格式為： sp_helptext 觸發(fā)器名 例如：sp_helptext 學生_insert2021-12-1619修改觸發(fā)器修改觸發(fā)器修改觸發(fā)器的語法

12、代碼如下：ALTER TRIGGER ON table|viewWITH ENCRYPTION|EXECUTE AS CALLER|SELF|FOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETENOT FOR REPLICATIONAS|EXTERNAL NAME 分析上述語法代碼可以發(fā)現(xiàn)，修改觸發(fā)器語法中所涉及到主要參數(shù)和創(chuàng)立觸發(fā)器的主要參數(shù)幾乎一樣，在此不再贅述。2021-12-1620刪除刪除DML觸發(fā)器觸發(fā)器 1 在圖形界面方式下刪除觸發(fā)器 按照在SQL Server Management Studio中查看觸發(fā)器的方法，找到“觸發(fā)器列表對話框。右擊要刪除的

13、某個觸發(fā)器，在彈出快捷菜單中選擇“刪除選項。2用SQL語句刪除觸發(fā)器，刪除觸發(fā)器的語法代碼如下所示： Drop Trigger 觸發(fā)器名2021-12-1621 禁用禁用DML觸發(fā)器觸發(fā)器 1在圖形界面方式下禁用在圖形界面方式下禁用DML觸發(fā)器觸發(fā)器按照在按照在SQL Server Management Studio中查看觸發(fā)器的方法翻開中查看觸發(fā)器的方法翻開“觸發(fā)觸發(fā)器列表對話框。器列表對話框。右擊其中一個觸發(fā)器，在彈出快捷菜單中右擊其中一個觸發(fā)器，在彈出快捷菜單中選擇選擇“禁用選項，即可。禁用選項，即可。 語法如下：語法如下： Alter table 數(shù)據(jù)表名數(shù)據(jù)表名 Disable tr

14、igger 觸發(fā)器名或觸發(fā)器名或ALL 如果要禁用所有觸發(fā)器，用如果要禁用所有觸發(fā)器，用“ALL來來代替觸發(fā)器名。代替觸發(fā)器名。2021-12-1622啟用啟用DML觸發(fā)器觸發(fā)器 啟用觸發(fā)器與禁用觸發(fā)器類似，只是在彈出的快捷菜單中選擇“啟用選項即可。 其語法如下： Alter table 數(shù)據(jù)表名 Enable trigger 觸發(fā)器名或ALL 如果要啟用所有觸發(fā)器，用“ALL來代替觸發(fā)器名。2021-12-162310.3 DDL觸發(fā)器觸發(fā)器DDL觸發(fā)器是SQL Server 2005新增的一個觸發(fā)器類型，像常規(guī)觸發(fā)器一樣，DDL 觸發(fā)器將激發(fā)存儲過程以響應(yīng)事件。但與DML觸發(fā)器不同的是，它

15、們不會為響應(yīng)針對表或視圖的UPDATE、INSERT或DELETE語句而激發(fā)。相反，它們會為響應(yīng)多種數(shù)據(jù)定義語言(DDL)語句而激發(fā)。這些語句主要是以 CREATE、ALTER和DROP開頭的語句。DDL觸發(fā)器可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫操作。2021-12-1624創(chuàng)立DDL觸發(fā)器的語法代碼如下：CREATE TRIGGER ON ALL SERVER|DATABASEWITH ,.nFOR|AFTERevent_type|event_group,.nASsql_statement;.n|EXTERNAL NAME;:=ENCRYPTIONEXECUTE AS Clause:=202

16、1-12-1625主要參數(shù)說明：trigger_name：觸發(fā)器的名稱,必須遵循標識符規(guī)那么，但不能以#或#開頭。DATABASE：將DDL觸發(fā)器的作用域應(yīng)用于當前數(shù)據(jù)庫。如果指定了此參數(shù)，那么只要當前數(shù)據(jù)庫中出現(xiàn)event_type或event_group，就會激發(fā)該觸發(fā)器。ALL SERVER：將DDL觸發(fā)器的作用域應(yīng)用于當前效勞器。如果指定了此參數(shù)，那么只要當前效勞器中的任何位置上出現(xiàn)event_type或event_group，就會激發(fā)該觸發(fā)器。event_type：執(zhí)行之后將導(dǎo)致激發(fā)DDL觸發(fā)器的Transact-SQL語言事件的名稱。event_group：預(yù)定義的 Transac

17、t-SQL 語言事件分組的名稱。其他參數(shù)在前面章節(jié)中已經(jīng)說明，在此不再贅述。2021-12-1626創(chuàng)立創(chuàng)立DDL觸發(fā)器例如觸發(fā)器例如的觸發(fā)器。的觸發(fā)器。具體操作步驟如下：具體操作步驟如下：1啟動啟動“SQL Server Management Studio，在，在“對象資源對象資源管理器下選擇管理器下選擇“數(shù)據(jù)庫，定位到數(shù)據(jù)庫，定位到“student數(shù)據(jù)庫。數(shù)據(jù)庫。2單擊單擊“新建查詢按鈕，在彈出的新建查詢按鈕，在彈出的“查詢編輯器的編輯區(qū)里查詢編輯器的編輯區(qū)里輸入以下代碼：輸入以下代碼：CREATE TRIGGER disable_drop_tableON DATABASEFOR DROP

18、_TABLE ASBEGINRAISERROR (對不起，對不起，student數(shù)據(jù)庫中的表不能刪除數(shù)據(jù)庫中的表不能刪除,16,10)ENDGO2021-12-1627測試測試DDL觸發(fā)器功能觸發(fā)器功能測試DDL觸發(fā)器的功能，具體操作步驟如下：啟動“SQL Server Management Studio，在“對象資源管理器下選擇“數(shù)據(jù)庫，定位到“student數(shù)據(jù)庫。單擊“新建查詢按鈕，在彈出的“查詢編輯器的編輯區(qū)里輸入以下代碼： DROP TABLE 成績表單擊“執(zhí)行按鈕，運行結(jié)果如以以以下圖所示 2021-12-1628測試結(jié)果測試結(jié)果 2021-12-1629查看和修改查看和修改DDL

19、觸發(fā)器觸發(fā)器查看查看DDL觸發(fā)器觸發(fā)器1查看作用在當前查看作用在當前SQL Server效勞器上的效勞器上的DDL觸發(fā)器觸發(fā)器 選擇所在選擇所在SQL Server效勞器，定位到效勞器，定位到“效勞器對象，效勞器對象，“觸發(fā)器觸發(fā)器，在，在“摘要對話框里就可以看到所有的作用在當前摘要對話框里就可以看到所有的作用在當前SQL Server效勞器上的效勞器上的DDL觸發(fā)器。觸發(fā)器。2查看作用在當前數(shù)據(jù)庫中的查看作用在當前數(shù)據(jù)庫中的DDL觸發(fā)器觸發(fā)器 選擇所在選擇所在SQL Server效勞器，效勞器，“數(shù)據(jù)庫，所在數(shù)據(jù)庫，定位到數(shù)據(jù)庫，所在數(shù)據(jù)庫，定位到“可編程性，可編程性，“數(shù)據(jù)庫觸發(fā)器，在摘要

20、對話框里就可以看到數(shù)據(jù)庫觸發(fā)器，在摘要對話框里就可以看到所有的當前數(shù)據(jù)庫中的所有的當前數(shù)據(jù)庫中的DDL觸發(fā)器。觸發(fā)器。3. 右擊觸發(fā)器，在彈出的快捷菜單中選擇右擊觸發(fā)器，在彈出的快捷菜單中選擇“編寫數(shù)據(jù)庫觸發(fā)器腳本編寫數(shù)據(jù)庫觸發(fā)器腳本為，為，“CREATE到，到，“新查詢編輯器對話框，然后在新翻新查詢編輯器對話框，然后在新翻開的開的“查詢編輯器對話框里可以看到該觸發(fā)器的內(nèi)容。查詢編輯器對話框里可以看到該觸發(fā)器的內(nèi)容。修改觸發(fā)器修改觸發(fā)器 在在Management Studio如果要修改如果要修改DDL觸發(fā)器內(nèi)容，就只能先觸發(fā)器內(nèi)容，就只能先刪除該刪除該觸發(fā)器，再重新建立一個觸發(fā)器，再重新建立一

21、個DDL觸發(fā)器。觸發(fā)器。 雖然在雖然在Management Studio中沒有直接提供修改中沒有直接提供修改DDL觸發(fā)器的觸發(fā)器的對話對話框，但在框，但在“查詢編輯器對話框里依然可以用查詢編輯器對話框里依然可以用SQL語句來進行修改。語句來進行修改。2021-12-1630游標是一種處理數(shù)據(jù)的方法，它可對結(jié)果集中的記錄進行逐行處理，可將游標視作一種指針，用于指向并處理結(jié)果集任意位置的數(shù)據(jù)。就本質(zhì)而言游標提供了一種對從表中檢索出的數(shù)據(jù)進行操作的靈活手段，由于游標由結(jié)果集和結(jié)果集中指向特定記錄的游標位置組成，因此當決定對結(jié)果集進行處理時，必須聲明定一個指向該結(jié)果集的游標。 2021-12-1631

22、游標的特點游標的特點 允許定位在結(jié)果集的特定行。從結(jié)果集的當前位置檢索一行或一局部行。支持對結(jié)果集中當前位置的行進行數(shù)據(jù)修改。為由其他用戶對顯示在結(jié)果集中的數(shù)據(jù)庫數(shù)據(jù)所做的更改提供不同級別的可見性支持。提供腳本、存儲過程和觸發(fā)器中用于訪問結(jié)果集中的數(shù)據(jù)的 Transact-SQL 語句 2021-12-1632游標分類游標分類 1Transact-SQL游標 Transact-SQL游標是由SQL Server效勞器實現(xiàn)的游標，主要用于存儲過程、觸發(fā)器和Transact-SQL 腳本中，它們使結(jié)果集的內(nèi)容可用于其他 Transact-SQL 語句。2API 效勞器游標 API效勞器游標在效勞器

23、上實現(xiàn)，并由 API 游標函數(shù)進行管理。當應(yīng)用程序調(diào)用 API 游標函數(shù)時，游標操作由OLE DB訪問接口或ODBC驅(qū)動程序傳送給效勞器。3客戶端游標 客戶端游標，即在客戶端實現(xiàn)的游標。在客戶端游標中，將使用默認結(jié)果集把整個結(jié)果集高速緩存在客戶端上，所有的游標操作都針對此客戶端高速緩存來執(zhí)行。將不使用 Microsoft SQL Server 2005的任何效勞器游標功能?？蛻舳擞螛藘H支持只進和靜態(tài)游標。 由于Transact-SQL游標和API 效勞器游標用于效勞器端，所以被稱為效勞器游標，也被稱為后臺游標。本章主要講述效勞器游標。2021-12-16331使用使用SOL-92語法來聲明語法

24、來聲明CURSOR語法代碼如下：DECLARE cursor_name INSENSITIVESCROLL CURSORFOR select_statementFOR READ ONLY|UPDATE OF column_name,.n 2T-SQL擴充語法來聲明擴充語法來聲明CURSOR語法代碼如下：DECLARE cursor_name CURSORLOCAL|GLOBALFORWARD_ONLY|SCROLLSTATIC|KEYSET|DYNAMIC|FAST_FORWARDREAD_ONLY|SCROLL_LOCKS|OPTIMISTICTYPE_WARNINGFOR select_s

25、tatementFOR UPDATE OF column_name ,.n 2021-12-1634聲明游標例如聲明一個名稱為學生_Cursor的游標DECLARE 學生_Cursor CURSOR FORSELECT sNo, sNameFROM 學生表;2021-12-1635翻開游標翻開游標翻開游標的語法代碼如下：OPEN GLOBAL cursor_name|cursor_variable_name主要參數(shù)說明：GLOBAL：指定cursor_name是指全局游標。cursor_name：已聲明的游標的名稱。如果全局游標和局部游標都使用cursor_name作為其名稱，那么如果指定了GLOBAL，那么cursor_name指的是全局游標；否那么cursor_name指的是局部游標。cursor_variable_name：游標變量的名稱，該變量引用一個