第11章 觸發(fā)器

1、第第1111章章 觸發(fā)器觸發(fā)器 觸發(fā)器是客戶觸發(fā)器是客戶/ /服務(wù)器數(shù)據(jù)庫的一服務(wù)器數(shù)據(jù)庫的一種關(guān)鍵特性。使用觸發(fā)器，開發(fā)人員可種關(guān)鍵特性。使用觸發(fā)器，開發(fā)人員可以在數(shù)據(jù)庫引擎上穩(wěn)固地實(shí)現(xiàn)復(fù)雜的業(yè)以在數(shù)據(jù)庫引擎上穩(wěn)固地實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則。務(wù)規(guī)則。 本章介紹觸發(fā)器的概念、使用方法。本章介紹觸發(fā)器的概念、使用方法。 第第11章章 觸發(fā)器觸發(fā)器n教學(xué)目標(biāo)：觸發(fā)器也是一種存儲(chǔ)過程，它是一種在基本表被修改時(shí)自動(dòng)執(zhí)行的內(nèi)嵌過程，它主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行。n教學(xué)提示：通過本章的學(xué)習(xí)，要求掌握觸發(fā)器的概念、用途、創(chuàng)建、修改等管理和操作，能編寫簡單運(yùn)用INSERT觸發(fā)器、UPDATE觸發(fā)器和DELETE

2、觸發(fā)器的代碼。11.1 11.1 觸發(fā)器的概念觸發(fā)器的概念 觸發(fā)器是一種特殊類型的存儲(chǔ)過程，它與前觸發(fā)器是一種特殊類型的存儲(chǔ)過程，它與前面章節(jié)講解的存儲(chǔ)過程不同。存儲(chǔ)過程可以通過面章節(jié)講解的存儲(chǔ)過程不同。存儲(chǔ)過程可以通過存儲(chǔ)過程名來調(diào)用，而觸發(fā)器是一段能自動(dòng)執(zhí)行存儲(chǔ)過程名來調(diào)用，而觸發(fā)器是一段能自動(dòng)執(zhí)行的程序，不由用戶直接調(diào)用，不能帶有參數(shù)，也的程序，不由用戶直接調(diào)用，不能帶有參數(shù)，也沒有返回值。當(dāng)用戶對表進(jìn)行了諸如沒有返回值。當(dāng)用戶對表進(jìn)行了諸如INSERTINSERT、UPDATEUPDATE、DELETEDELETE等操作時(shí)，等操作時(shí)，SQL ServerSQL Server就會(huì)自動(dòng)就會(huì)

3、自動(dòng)執(zhí)行觸發(fā)器所事先定義好的語句。執(zhí)行觸發(fā)器所事先定義好的語句。 11.1.1 11.1.1 觸發(fā)器的作用觸發(fā)器的作用 使用觸發(fā)器的最終目標(biāo)是為了更好地維護(hù)使用觸發(fā)器的最終目標(biāo)是為了更好地維護(hù)企業(yè)的業(yè)務(wù)規(guī)則，實(shí)現(xiàn)由主鍵和外鍵所不能保企業(yè)的業(yè)務(wù)規(guī)則，實(shí)現(xiàn)由主鍵和外鍵所不能保證的復(fù)雜參照完整性和數(shù)據(jù)一致性。除此以外，證的復(fù)雜參照完整性和數(shù)據(jù)一致性。除此以外，使用觸發(fā)器還有以下的一些功能：使用觸發(fā)器還有以下的一些功能：1.1.可以強(qiáng)化約束可以強(qiáng)化約束 觸發(fā)器可以實(shí)現(xiàn)比觸發(fā)器可以實(shí)現(xiàn)比checkcheck子句更為復(fù)雜的條子句更為復(fù)雜的條件約束，件約束，checkcheck約束在限制數(shù)據(jù)輸入時(shí)不能參照約

4、束在限制數(shù)據(jù)輸入時(shí)不能參照其它表中的數(shù)據(jù)。其它表中的數(shù)據(jù)。2.2.可以實(shí)現(xiàn)數(shù)據(jù)庫中多張表的級聯(lián)運(yùn)行可以實(shí)現(xiàn)數(shù)據(jù)庫中多張表的級聯(lián)運(yùn)行 觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，并自動(dòng)觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，并自動(dòng)地級聯(lián)整個(gè)數(shù)據(jù)庫的各項(xiàng)內(nèi)容。地級聯(lián)整個(gè)數(shù)據(jù)庫的各項(xiàng)內(nèi)容。3.3.可以跟蹤數(shù)據(jù)變化可以跟蹤數(shù)據(jù)變化 觸發(fā)器可以跟蹤數(shù)據(jù)庫內(nèi)的操作，當(dāng)數(shù)據(jù)觸發(fā)器可以跟蹤數(shù)據(jù)庫內(nèi)的操作，當(dāng)數(shù)據(jù)庫發(fā)生了未經(jīng)許可的更新和變化時(shí)，撤銷或者庫發(fā)生了未經(jīng)許可的更新和變化時(shí)，撤銷或者回滾操作使數(shù)據(jù)庫修改、更新操作更安全，數(shù)回滾操作使數(shù)據(jù)庫修改、更新操作更安全，數(shù)據(jù)庫運(yùn)行更穩(wěn)定。據(jù)庫運(yùn)行更穩(wěn)定。4.4.可以調(diào)用存儲(chǔ)過程可以調(diào)用存

5、儲(chǔ)過程 觸發(fā)器操作可以通過調(diào)用一個(gè)或多個(gè)存儲(chǔ)觸發(fā)器操作可以通過調(diào)用一個(gè)或多個(gè)存儲(chǔ)過程，甚至可以通過調(diào)用外部過程完成相應(yīng)的過程，甚至可以通過調(diào)用外部過程完成相應(yīng)的操作。操作。 11.1.2 11.1.2 觸發(fā)器的分類觸發(fā)器的分類 SQL ServerSQL Server包括三種常規(guī)類型的觸發(fā)器：包括三種常規(guī)類型的觸發(fā)器： DMLDML觸發(fā)器觸發(fā)器（DMLDML數(shù)據(jù)操作語言）數(shù)據(jù)操作語言） DDLDDL觸發(fā)器觸發(fā)器（DDLDDL數(shù)據(jù)定義語言）數(shù)據(jù)定義語言） 登錄觸發(fā)器登錄觸發(fā)器1. DML1. DML觸發(fā)器觸發(fā)器 當(dāng)數(shù)據(jù)庫中發(fā)生當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作數(shù)據(jù)操作語言（語言（DMLDML）事件時(shí)將調(diào)）事

6、件時(shí)將調(diào)用用DMLDML觸發(fā)器。觸發(fā)器。DMLDML事件包括在指定表或視圖中修改數(shù)事件包括在指定表或視圖中修改數(shù)據(jù)的據(jù)的INSERTINSERT語句、語句、UPDATEUPDATE語句或語句或DELETEDELETE語句。語句。DMLDML觸發(fā)觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的器可以查詢其他表，還可以包含復(fù)雜的Transact-SQLTransact-SQL語句。語句。 將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對待。如果檢測到錯(cuò)誤（例如，磁盤空間的單個(gè)事務(wù)對待。如果檢測到錯(cuò)誤（例如，磁盤空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。不足），則整個(gè)事

7、務(wù)即自動(dòng)回滾。DMLDML觸發(fā)器在以下方面非常有用：觸發(fā)器在以下方面非常有用： 1) DML1) DML觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級聯(lián)修改。觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級聯(lián)修改。不過，通過級聯(lián)引用完整性約束可以更有效地進(jìn)行這些更不過，通過級聯(lián)引用完整性約束可以更有效地進(jìn)行這些更改。改。 2) DML2) DML觸發(fā)器可以防止惡意或錯(cuò)誤的觸發(fā)器可以防止惡意或錯(cuò)誤的INSERTINSERT、UPDATEUPDATE以以及及DELETEDELETE操作，并強(qiáng)制執(zhí)行比操作，并強(qiáng)制執(zhí)行比CHECKCHECK約束定義的限制更為復(fù)約束定義的限制更為復(fù)雜的其他限制。與雜的其他限制。與CHECKCHE

8、CK約束不同，約束不同，DMLDML觸發(fā)器可以引用其觸發(fā)器可以引用其他表中的列。他表中的列。例如：例如：觸發(fā)器可以使用另一個(gè)表中的觸發(fā)器可以使用另一個(gè)表中的SELECTSELECT比較插入或更比較插入或更新的數(shù)據(jù)，以及執(zhí)行其他操作，如修改數(shù)據(jù)或顯示用戶錯(cuò)新的數(shù)據(jù)，以及執(zhí)行其他操作，如修改數(shù)據(jù)或顯示用戶錯(cuò)誤信息。誤信息。 3) DML3) DML觸發(fā)器可以評估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)觸發(fā)器可以評估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施。該差異采取措施。 4) 4) 一個(gè)表中的多個(gè)同類一個(gè)表中的多個(gè)同類DMLDML觸發(fā)器（觸發(fā)器（INSERTINSERT、UPDATEUPDATE或或DEL

9、ETEDELETE）允許采?。┰试S采取 多個(gè)不同的操作來響應(yīng)同一個(gè)修改語多個(gè)不同的操作來響應(yīng)同一個(gè)修改語句。句。 用戶可以設(shè)計(jì)以下類型的用戶可以設(shè)計(jì)以下類型的DMLDML觸發(fā)器：觸發(fā)器：(1) (1) 后觸發(fā)器（后觸發(fā)器（AFTER AFTER 觸發(fā)器）觸發(fā)器）(2) (2) 替代觸發(fā)器（替代觸發(fā)器（INSTEAD OF INSTEAD OF 觸發(fā)器）觸發(fā)器）P168 2. DDL觸發(fā)器觸發(fā)器 DDLDDL觸發(fā)器將激發(fā)存儲(chǔ)過程以響應(yīng)事件觸發(fā)器將激發(fā)存儲(chǔ)過程以響應(yīng)事件。但與。但與DMLDML觸發(fā)觸發(fā)器不同的是，它們不會(huì)為響應(yīng)針對表或視圖的器不同的是，它們不會(huì)為響應(yīng)針對表或視圖的UPDATEUPD

10、ATE、INSERTINSERT或或DELETEDELETE語句而激發(fā)。相反，它們將為了響應(yīng)各種語句而激發(fā)。相反，它們將為了響應(yīng)各種數(shù)據(jù)定義語言（數(shù)據(jù)定義語言（DDLDDL）事件而激發(fā)。這些事件主要與以關(guān)鍵事件而激發(fā)。這些事件主要與以關(guān)鍵字字CREATECREATE、ALTERALTER和和DROPDROP開頭的開頭的T-SQLT-SQL語句對應(yīng)。語句對應(yīng)。DDLDDL觸發(fā)器觸發(fā)器可用于管理任務(wù)，可用于管理任務(wù)，例如：例如：審核和控制數(shù)據(jù)庫操作。審核和控制數(shù)據(jù)庫操作。如果要執(zhí)行以下操作，可使用如果要執(zhí)行以下操作，可使用DDLDDL觸發(fā)器：觸發(fā)器：1)1)要防止對數(shù)據(jù)庫架構(gòu)進(jìn)行某些更改。要防止對

11、數(shù)據(jù)庫架構(gòu)進(jìn)行某些更改。2)2)希望數(shù)據(jù)庫中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫架構(gòu)中的更改。希望數(shù)據(jù)庫中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫架構(gòu)中的更改。3)3)要記錄數(shù)據(jù)庫架構(gòu)中的更改或事件。要記錄數(shù)據(jù)庫架構(gòu)中的更改或事件。僅在運(yùn)行觸發(fā)僅在運(yùn)行觸發(fā)DDL觸發(fā)器的觸發(fā)器的DDL語句后，語句后，DDL觸發(fā)器才會(huì)激發(fā)。觸發(fā)器才會(huì)激發(fā)。DDL觸發(fā)器觸發(fā)器無法作為無法作為INSTEAD OF觸發(fā)器使用。觸發(fā)器使用。3. 3. 登錄觸發(fā)器登錄觸發(fā)器 登錄觸發(fā)器將為響應(yīng)登錄觸發(fā)器將為響應(yīng)LOG ONLOG ON事件而激發(fā)存事件而激發(fā)存儲(chǔ)過程。與儲(chǔ)過程。與SQL ServerSQL Server實(shí)例建立用戶會(huì)話時(shí)將實(shí)例建立用戶會(huì)

12、話時(shí)將引發(fā)此事件。登錄觸發(fā)器將在登錄的身份驗(yàn)證引發(fā)此事件。登錄觸發(fā)器將在登錄的身份驗(yàn)證階段完成之后且用戶會(huì)話實(shí)際建立之前激發(fā)。階段完成之后且用戶會(huì)話實(shí)際建立之前激發(fā)。 可以使用登錄觸發(fā)器來審核和控制服務(wù)器可以使用登錄觸發(fā)器來審核和控制服務(wù)器會(huì)話，會(huì)話，例如：例如：通過跟蹤登錄活動(dòng)、限制通過跟蹤登錄活動(dòng)、限制SQL SQL ServerServer的登錄名或限制特定登錄名的會(huì)話數(shù)。的登錄名或限制特定登錄名的會(huì)話數(shù)。 本章將重點(diǎn)介紹本章將重點(diǎn)介紹DMLDML觸發(fā)器觸發(fā)器。 觸發(fā)器的作用觸發(fā)器的作用 可以對數(shù)據(jù)庫進(jìn)行級聯(lián)修改。 可以完成比CHECK更復(fù)雜的約束。與與CHECK約約束不同，在觸發(fā)器中可

13、以引用其他的表束不同，在觸發(fā)器中可以引用其他的表。比如，當(dāng)向圖書借閱表圖書借閱表中插入一條借閱記錄時(shí)，可以查看對應(yīng)讀者在讀者信息表讀者信息表中的是否借書數(shù)量已超過限借數(shù)量，從而判定能否再為該讀者借書。 可以發(fā)現(xiàn)改變前后表中不同的數(shù)據(jù)，并據(jù)此來進(jìn)行相應(yīng)的操作。 對于一個(gè)表上的不同的操作對于一個(gè)表上的不同的操作(INSERT、UPDATE或或DELETE)可以采用不同的觸發(fā)器可以采用不同的觸發(fā)器，相同的語句可以調(diào)用不同的觸發(fā)器完成不同的操作。觸發(fā)器具有以下特點(diǎn)觸發(fā)器具有以下特點(diǎn) 觸發(fā)器定義在指定的表上，這個(gè)表稱為觸發(fā)器表。觸發(fā)器定義在指定的表上，這個(gè)表稱為觸發(fā)器表。 它是在操作有效后才執(zhí)行的，它是

14、在操作有效后才執(zhí)行的，即其他約束優(yōu)先于即其他約束優(yōu)先于觸發(fā)器觸發(fā)器 它與存儲(chǔ)過程的不同之處在于存儲(chǔ)過程可以由用它與存儲(chǔ)過程的不同之處在于存儲(chǔ)過程可以由用戶直接調(diào)用，戶直接調(diào)用，而觸發(fā)器不能被直接調(diào)用，是由操縱事而觸發(fā)器不能被直接調(diào)用，是由操縱事件觸發(fā)的件觸發(fā)的 一個(gè)表可以有多個(gè)觸發(fā)器，在不同表上同一種類一個(gè)表可以有多個(gè)觸發(fā)器，在不同表上同一種類型的觸發(fā)器也可以有多個(gè)型的觸發(fā)器也可以有多個(gè) 觸發(fā)器允許嵌套，最多為觸發(fā)器允許嵌套，最多為32層層 是操縱事務(wù)的一部分可以將觸發(fā)器和觸發(fā)它的語是操縱事務(wù)的一部分可以將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對待。句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)

15、對待。 比較：觸發(fā)器與約束比較：觸發(fā)器與約束 約束和約束和DMLDML觸發(fā)器在特殊情況下各有優(yōu)點(diǎn)。觸發(fā)器在特殊情況下各有優(yōu)點(diǎn)。DMLDML觸發(fā)器的主要優(yōu)點(diǎn)在于它們可以包含使用觸發(fā)器的主要優(yōu)點(diǎn)在于它們可以包含使用Transact-SQLTransact-SQL代碼的復(fù)雜處理邏輯。因此，代碼的復(fù)雜處理邏輯。因此，DMLDML觸發(fā)器可以支持約束的所有功能；但觸發(fā)器可以支持約束的所有功能；但DMLDML觸發(fā)器觸發(fā)器對于給定的功能并不總是最好的方法。對于給定的功能并不總是最好的方法。 當(dāng)約束支持的功能無法滿足應(yīng)用程序的功當(dāng)約束支持的功能無法滿足應(yīng)用程序的功能要求時(shí)，能要求時(shí)，DMLDML觸發(fā)器非常有用。

16、觸發(fā)器非常有用。 詳細(xì)介紹見詳細(xì)介紹見P169P169頁頁DMLDML觸發(fā)器的分類觸發(fā)器的分類 觸發(fā)器與表或視圖觸發(fā)器與表或視圖是不能分開的，觸發(fā)器定義在一個(gè)是不能分開的，觸發(fā)器定義在一個(gè)表或視圖中，當(dāng)在表或視圖中執(zhí)行表或視圖中，當(dāng)在表或視圖中執(zhí)行插入（插入（ INSERT）、）、修改（修改（UPDATE）、刪除（）、刪除（DELETE）操作時(shí)觸發(fā)器被操作時(shí)觸發(fā)器被觸發(fā)自動(dòng)執(zhí)行。觸發(fā)自動(dòng)執(zhí)行。當(dāng)表或視圖被刪除時(shí)與它關(guān)聯(lián)的觸發(fā)器當(dāng)表或視圖被刪除時(shí)與它關(guān)聯(lián)的觸發(fā)器也一同被刪除。也一同被刪除。分類：分類：1.據(jù)觸發(fā)時(shí)刻分為據(jù)觸發(fā)時(shí)刻分為:AFTER(之后之后)觸發(fā)器和觸發(fā)器和INSTEAD(替代替代

17、)觸發(fā)器觸發(fā)器2.據(jù)觸發(fā)語句分為據(jù)觸發(fā)語句分為:INSERT、UPDATE和和DELETE觸發(fā)器觸發(fā)器AFTER(之后之后) 和和 INSTEAD(替代替代)觸發(fā)器觸發(fā)器*AFTER觸發(fā)器觸發(fā)器是在是在處理完約束之后處理完約束之后執(zhí)行觸發(fā)執(zhí)行觸發(fā)操作（操作（INSERT、UPDATE 或或 DELETE）后激）后激發(fā)；只宜建在表上發(fā)；只宜建在表上(主要掌握主要掌握)*INSTEAD觸發(fā)器觸發(fā)器是由觸發(fā)器的程序代替是由觸發(fā)器的程序代替代替代替INSERT、UPDATE 或或 DELETE語句語句執(zhí)行，在執(zhí)行，在處理約束之前處理約束之前激發(fā)；可以建在激發(fā)；可以建在表或視圖表或視圖上。上。所以所以:

18、 若執(zhí)行若執(zhí)行INSERT、UPDATE 或或 DELETE語句語句違犯違犯約束條件約束條件時(shí)，將不執(zhí)行時(shí)，將不執(zhí)行AFTER觸發(fā)器觸發(fā)器； 而在定義而在定義INSTEAD OF觸發(fā)器觸發(fā)器的表或視圖上的表或視圖上執(zhí)行執(zhí)行INSERT、UPDATE 或或 DELETE語句時(shí)，語句時(shí)，會(huì)激發(fā)觸發(fā)器而不執(zhí)行這些數(shù)據(jù)操作語句本身。會(huì)激發(fā)觸發(fā)器而不執(zhí)行這些數(shù)據(jù)操作語句本身。11.2 11.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器 可以使用可以使用CREAT TRIGGERCREAT TRIGGER語句來完成對觸發(fā)語句來完成對觸發(fā)器的創(chuàng)建操作，創(chuàng)建器的創(chuàng)建操作，創(chuàng)建DMLDML觸發(fā)器的基本語法如下：觸發(fā)器的基本語法如下

19、：CREATE TRIGGER trigger_name CREATE TRIGGER trigger_name ON table | view ON table | view WITH ENCRYPTION WITH ENCRYPTION FOR | AFTER | INSTEAD OF FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE INSERT , UPDATE , DELETE WITH APPEND WITH APPEND NOT FOR REPLICATION NOT FOR REPLICATION ASAS SQL SQL語句系

20、列語句系列GOCREATE TRIGGER 觸發(fā)器名觸發(fā)器名ON 表名或視圖名表名或視圖名FOR |AFTER |INSTEAD OF INSERT,UPDATE,DELETEWITH ENCRYPTIONAS SQL語句語句GO注意：注意：1) 1) CREATE TRIGGERCREATE TRIGGER語句必須是一個(gè)批處理的第語句必須是一個(gè)批處理的第一條語句。一條語句。2) 2) 創(chuàng)建創(chuàng)建DMLDML觸發(fā)器的權(quán)限默認(rèn)分配給表的所有觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。3) 3) 一個(gè)觸發(fā)器只能創(chuàng)建在一個(gè)表上；一個(gè)表可一個(gè)觸發(fā)器只能創(chuàng)

21、建在一個(gè)表上；一個(gè)表可以有一個(gè)替代觸發(fā)器和多個(gè)后觸發(fā)器。以有一個(gè)替代觸發(fā)器和多個(gè)后觸發(fā)器。4) 4) 雖然雖然DMLDML觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建對象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建DMLDML觸發(fā)器。觸發(fā)器。注意：注意：5) 5) 觸發(fā)器的定義語句中不能有任何用觸發(fā)器的定義語句中不能有任何用CREATECREATE創(chuàng)建、用創(chuàng)建、用ALTERALTER修改數(shù)據(jù)庫或各種對象的語句，不允許使用任何修改數(shù)據(jù)庫或各種對象的語句，不允許使用任何的的DROPDROP語句。語句。6) 6) TRUNCATE TABLETRUNCATE TABLE雖

22、然在功能上與雖然在功能上與DELETEDELETE類似，但是類似，但是由于由于TRUNCATETRUNCATE刪除記錄時(shí)不被記入事務(wù)日志，所以該刪除記錄時(shí)不被記入事務(wù)日志，所以該語句不能激活語句不能激活deletedelete刪除操作的觸發(fā)器。刪除操作的觸發(fā)器。7) 7) WRITETEXTWRITETEXT命令不會(huì)觸發(fā)命令不會(huì)觸發(fā)INSERTINSERT或者或者UPDATEUPDATE觸發(fā)器觸發(fā)器運(yùn)行。觸發(fā)器作為一種數(shù)據(jù)庫對象，在運(yùn)行。觸發(fā)器作為一種數(shù)據(jù)庫對象，在syscommentssyscomments系系統(tǒng)表中存儲(chǔ)其完整的定義信息，統(tǒng)表中存儲(chǔ)其完整的定義信息，在在sysobjectss

23、ysobjects系統(tǒng)表系統(tǒng)表中有該對象的記錄。中有該對象的記錄。sysobjectssysobjects表中表中namename列對應(yīng)對象列對應(yīng)對象名，名，typetype或或xtypextype列對象對象類型，觸發(fā)器用列對象對象類型，觸發(fā)器用“TR”TR”表表示。示。 簡單小結(jié)：簡單小結(jié)： (1)觸發(fā)器的定義必須是一段批文件的第一條觸發(fā)器的定義必須是一段批文件的第一條命令命令 (2) AFTER觸發(fā)器觸發(fā)器只能只能在表上定義在表上定義 (3)觸發(fā)器觸發(fā)器盡量盡量不在視圖上定義不在視圖上定義 (4)觸發(fā)器不能處理觸發(fā)器不能處理TEXT和和IMAGE數(shù)據(jù)類型數(shù)據(jù)類型的大型二進(jìn)制對象表列的大型二

24、進(jìn)制對象表列 (5)建議建議不要使用觸發(fā)器返回一個(gè)不要使用觸發(fā)器返回一個(gè)結(jié)果集結(jié)果集 觸發(fā)器中用到的兩條觸發(fā)器中用到的兩條SQL命令：命令：功能：功能：提交操作到服務(wù)器提交操作到服務(wù)器語法如下：語法如下：COMMIT功能：功能：回滾、撤銷當(dāng)前操作回滾、撤銷當(dāng)前操作語法如下：語法如下：ROLLBACK觸發(fā)器的運(yùn)行機(jī)制1DELETE觸發(fā)器觸發(fā)器 對表進(jìn)行刪除時(shí)，如果此表有DELETE型的觸發(fā)器，則觸發(fā)器被觸發(fā)執(zhí)行。被刪除的記錄存在DELETED表中。當(dāng)執(zhí)行DELETE操作時(shí)，應(yīng)應(yīng)注意以下幾點(diǎn)：注意以下幾點(diǎn)： (1)被放到DELETED表中的記錄，不再存于觸發(fā)器表中，因此觸發(fā)器表觸發(fā)器表與DELET

25、ED表沒有共同的記錄。 (2)DELETED表放在內(nèi)存中，執(zhí)行效率很高。【例例11-211-2】定義一個(gè)定義一個(gè)DELETEDELETE觸發(fā)器，實(shí)現(xiàn)當(dāng)刪除觸發(fā)器，實(shí)現(xiàn)當(dāng)刪除某個(gè)讀者信息后，就刪除該讀者的借閱記錄。某個(gè)讀者信息后，就刪除該讀者的借閱記錄。代碼清單如下：代碼清單如下： USE Library GOIF EXISTS ( SELECT * FROM sysobjects WHERE name=user_tri and type=TR) DROP TRIGGER user_triGOCREATE TRIGGER user_tri -創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器ON UserTbAFTER DE

26、LETEASBEGINDECLARE t char(6)SELECT t=UserId FROM DELETED -使用使用DELETED表中數(shù)據(jù)表中數(shù)據(jù)DELETE FROM Lending WHERE UserId=tENDGO-引起觸發(fā)器工作的刪除語句引起觸發(fā)器工作的刪除語句DELETE FROM UserTb -從從UserTb表中刪除記錄表中刪除記錄WHERE UserName=王凱王凱GO 【例【例1】 在在Library庫的庫的book2表上建立一個(gè)名為表上建立一個(gè)名為book_trigger1的的DELETE觸發(fā)器，該觸發(fā)器將對觸發(fā)器，該觸發(fā)器將對book2表中刪除記錄的操作給

27、出提示信息，并取消當(dāng)表中刪除記錄的操作給出提示信息，并取消當(dāng)前的刪除操作。前的刪除操作。goCREATE TRIGGER book_trigger1ON book2FOR DELETEAS BEGIN print 刪除失??！刪除失?。?ROLLBACK -回滾命令，取消刪除操作回滾命令，取消刪除操作ENDDELETE FROM book2WHERE 編號(hào)編號(hào)=YBZT2411注意：注意：此句運(yùn)行引起觸發(fā)器工作。此句運(yùn)行引起觸發(fā)器工作?！纠纠?】 在在Library庫的庫的book2表上建立一個(gè)名為表上建立一個(gè)名為book_trigger1的的DELETE觸發(fā)器，該觸發(fā)器將對觸發(fā)器，該觸發(fā)器將

28、對book2表中刪除記錄的操作給出提示信息，并取消當(dāng)表中刪除記錄的操作給出提示信息，并取消當(dāng)前的刪除操作。前的刪除操作。GOCREATE TRIGGER book_trigger2ON book2INSTEAD OF DELETEAS PRINT 數(shù)據(jù)刪除不成功數(shù)據(jù)刪除不成功GO此題與例此題與例1效果效果相同相同【例【例3】 在圖書信息表中創(chuàng)建一個(gè)觸發(fā)器，該觸發(fā)器保在圖書信息表中創(chuàng)建一個(gè)觸發(fā)器，該觸發(fā)器保證每次只能刪除一本圖書。如果要?jiǎng)h除的圖書超過一證每次只能刪除一本圖書。如果要?jiǎng)h除的圖書超過一本，觸發(fā)器就返回提示并回滾。本，觸發(fā)器就返回提示并回滾。GO CREATE TRIGGER tr_T

29、SSCON 圖書信息表圖書信息表FOR DELETEAS IF (SELECT COUNT(*) FROM deleted) 1 BEGIN print 對不起，一次只能刪除一本圖書！對不起，一次只能刪除一本圖書！ ROLLBACK TRANSACTIONENDGO例例 DELETE觸發(fā)器觸發(fā)器3.sql觸發(fā)器的運(yùn)行機(jī)制2INSERT觸發(fā)器觸發(fā)器 當(dāng)向表中插入數(shù)據(jù)時(shí)，所有數(shù)據(jù)約束都通過之后，INSERT觸發(fā)器就會(huì)執(zhí)行。新的記錄不但加到觸發(fā)器表中，而且還會(huì)有副本加入INSERTED表中。因此觸發(fā)器表與插入信息表有共同的記錄。 INSERTED表與DELETED表一樣，它們的記錄是可讀的，可以進(jìn)行

30、比較，以便確認(rèn)這些數(shù)據(jù)是否正確?！纠纠?】 在在Library數(shù)據(jù)庫的數(shù)據(jù)庫的book2表上創(chuàng)建一表上創(chuàng)建一個(gè)個(gè)book_trigger3觸發(fā)器，當(dāng)執(zhí)行觸發(fā)器，當(dāng)執(zhí)行INSERT操作操作時(shí)，該觸發(fā)器被觸發(fā)時(shí)，該觸發(fā)器被觸發(fā)(即向所定義觸發(fā)器的表中即向所定義觸發(fā)器的表中插入數(shù)據(jù)時(shí)觸發(fā)器被觸發(fā)插入數(shù)據(jù)時(shí)觸發(fā)器被觸發(fā))。goCREATE TRIGGER book_trigger3ON book2FOR INSERTAS PRINT 數(shù)據(jù)插入成功數(shù)據(jù)插入成功GO2) INSERT觸發(fā)器觸發(fā)器當(dāng)用戶向當(dāng)用戶向book2表中插入數(shù)據(jù)時(shí)將觸發(fā)表中插入數(shù)據(jù)時(shí)將觸發(fā)觸發(fā)器，而且數(shù)據(jù)被插入表中，如向表觸發(fā)器，而

31、且數(shù)據(jù)被插入表中，如向表中加入如下記錄內(nèi)容：中加入如下記錄內(nèi)容：INSERT INTO book2VALUES(YB001, SQL2005,25.00, 中中山大學(xué)出版社山大學(xué)出版社, 2010-1-2, Z001 )思考：思考：能插入數(shù)據(jù)嗎？能插入數(shù)據(jù)嗎？【例例11-311-3】定義一個(gè)定義一個(gè)INSERTINSERT觸發(fā)器，實(shí)現(xiàn)當(dāng)向觸發(fā)器，實(shí)現(xiàn)當(dāng)向BookBook表中添加記錄時(shí)，把新的圖書類別插入到圖表中添加記錄時(shí)，把新的圖書類別插入到圖書類別表書類別表BookCategoryBookCategory中。中。USE Library USE Library GOGOIF EXISTS(SE

32、LECT IF EXISTS(SELECT * * FROM FROM sysobjectssysobjects WHERE name=bookcate_tri WHERE name=bookcate_tri and type=TR) and type=TR)DROP TRIGGER bookcate_triDROP TRIGGER bookcate_triGOGOGOGOCREATE TRIGGER bookcate_triCREATE TRIGGER bookcate_tri -創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器ON BookON BookAFTER INSERTAFTER INSERTAS AS BE

33、GINBEGIN DECLARE t varchar(6) DECLARE t varchar(6) SELECT t=BkCateId SELECT t=BkCateId FROM INSERTED FROM INSERTED -使用使用INSERTEDINSERTED表表 INSERT INTO BookCategory(BkCateIdINSERT INTO BookCategory(BkCateId) VALUES(t) VALUES(t)ENDENDGOGO-Book-Book表中插入記錄表中插入記錄INSERT INTO Book VALUES(AB001,INSERT INTO

34、Book VALUES(AB001,管理專業(yè)英語管理專業(yè)英語,WK,WK,趙趙偉偉,教育出版社教育出版社,2006-2-2,default,30,1),2006-2-2,default,30,1)GOGO 1.要先刪除外鍵約束才能觸發(fā)要先刪除外鍵約束才能觸發(fā)2.必須判別必須判別WK類型沒有再插入類型沒有再插入3.無法知道類別名稱無法知道類別名稱GOGOCREATE TRIGGER bookcate_triCREATE TRIGGER bookcate_tri -創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器ON BookON BookAFTER INSERTAFTER INSERTAS AS BEGINBEGIN DE

35、CLARE t varchar(6) DECLARE t varchar(6) SELECT t=BkCateId SELECT t=BkCateId FROM INSERTED FROM INSERTED -使用使用INSERTEDINSERTED表表 if not EXISTS(SELECT * FROM BookCategory WHERE BkCateId=t) INSERT INTO BookCategory(BkCateId) VALUES(t)ENDENDGOGO-Book-Book表中插入記錄表中插入記錄INSERT INTO Book VALUES(AB001,INSERT

36、INTO Book VALUES(AB001,管理專業(yè)英語管理專業(yè)英語,WK,WK,趙偉趙偉,教育出教育出版社版社,2006-2-2,default,30,1),2006-2-2,default,30,1)GOGO 觸發(fā)器的運(yùn)行機(jī)制3UPDATE觸發(fā)器觸發(fā)器 利用UPDATE修改一條記錄時(shí)，相當(dāng)于刪除一條記錄后再增加一條新記錄。所以UPDATE操作使用DELETED和INSERTED兩個(gè)表。當(dāng)使用UPDATE操作時(shí)，觸發(fā)器表中原來的記錄被移到DELETED表中，修改過的記錄插入到INSERTED表中，觸發(fā)器可以檢查這兩個(gè)表，以便確定應(yīng)執(zhí)行什么樣的操作?！纠纠?】 在在book數(shù)據(jù)庫的數(shù)據(jù)庫的

37、book2表上建立一個(gè)名為表上建立一個(gè)名為book_trigger4的觸發(fā)器，該觸發(fā)器將被的觸發(fā)器，該觸發(fā)器將被UPDATE操作激活，該操作激活，該觸發(fā)器將不允許用戶修改表的觸發(fā)器將不允許用戶修改表的“定價(jià)定價(jià)”列運(yùn)行結(jié)果顯示：列運(yùn)行結(jié)果顯示：“禁禁止修改價(jià)格止修改價(jià)格!”說明操作無法進(jìn)行，觸發(fā)器起到保護(hù)作用。說明操作無法進(jìn)行，觸發(fā)器起到保護(hù)作用。goCREATE TRIGGER book_trigger4ON book2FOR UPDATEASIF UPDATE(定價(jià)定價(jià))BEGIN print 禁止修改價(jià)格禁止修改價(jià)格! ROLLBACK END-調(diào)用指令調(diào)用指令UPDATE bookSE

38、T 定價(jià)定價(jià)=5000WHERE 編號(hào)編號(hào)=YBZT2411【例例11-411-4】定義一個(gè)定義一個(gè)UPDATEUPDATE觸發(fā)器，更新讀者類別表觸發(fā)器，更新讀者類別表UserCateUserCate中讀者類別名稱中讀者類別名稱CateNameCateName時(shí)，把讀者表時(shí)，把讀者表UserTbUserTb中相應(yīng)的讀者類別也進(jìn)行修改。中相應(yīng)的讀者類別也進(jìn)行修改。USE Library USE Library GOGOIF EXISTS(SELECT IF EXISTS(SELECT * * FROM sysobjects FROM sysobjects WHERE name=usercate_

39、tri WHERE name=usercate_tri and type=TR) and type=TR)DROP TRIGGER usercate_triDROP TRIGGER usercate_triGOGOCREATE TRIGGER usercate_triCREATE TRIGGER usercate_tri -創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器ON UserCateON UserCateAFTER UPDATEAFTER UPDATEASASBEGINBEGIN DECLARE old varchar(10) DECLARE old varchar(10) DECLARE new varcha

40、r(10) DECLARE new varchar(10) SELECT old=CateName SELECT old=CateName FROM DELETED FROM DELETED -使用使用DELETEDDELETED表和表和INSERTEDINSERTED表表 SELECT new=CateNameSELECT new=CateName FROM INSERTED FROM INSERTED UPDATE UserTb UPDATE UserTb SET CateName=new WHERE CateName SET CateName=new WHERE CateName=old

41、=oldENDENDGOGO-修改修改UserCateUserCate表中記錄表中記錄 注意注意:先要?jiǎng)h除外鍵先要?jiǎng)h除外鍵UPDATE UserCateUPDATE UserCate SET CateNameSET CateName=高校教師高校教師 WHERE CateNameWHERE CateName=教師教師 GOGO【例例11-111-1】創(chuàng)建一個(gè)后觸發(fā)的觸發(fā)器，顯示修改創(chuàng)建一個(gè)后觸發(fā)的觸發(fā)器，顯示修改記錄的條數(shù)。記錄的條數(shù)。USE Library GO-檢測是否存在相同名字的觸發(fā)器檢測是否存在相同名字的觸發(fā)器,如果存在就把如果存在就把它刪除它刪除,避免調(diào)試時(shí)的麻煩避免調(diào)試時(shí)的麻煩

42、IF EXISTS(SELECT * FROM sysobjects WHERE name=book_tri and type=TR) DROP TRIGGER book_triGOGOCREATE TRIGGER book_tri -創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器ON BookAFTER UPDATEASDECLARE c intSELECT c= rowcountPRINT 一共修改了一共修改了+char(48+c)+行行RETURN GO -在查詢頁中輸入以上代碼，單擊按鈕，運(yùn)行結(jié)果如在查詢頁中輸入以上代碼，單擊按鈕，運(yùn)行結(jié)果如圖圖11-1所示。所示。 【例例11-511-5】定義一個(gè)替代觸發(fā)器，

43、不允許對定義一個(gè)替代觸發(fā)器，不允許對“AdminTbAdminTb”表進(jìn)行修改、刪除。表進(jìn)行修改、刪除。USE Library USE Library GOGOIF EXISTS(SELECT IF EXISTS(SELECT * * FROM sysobjects FROM sysobjects WHERE name= admin_tri WHERE name= admin_tri and type= TR) and type= TR) DROP TRIGGER admin_tri DROP TRIGGER admin_triGOGOCREATE TRIGGER admin_triCREAT

44、E TRIGGER admin_tri -創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器ON AdminTbON AdminTbINSTEAD OF UPDATE,DELETEINSTEAD OF UPDATE,DELETEAS AS PRINT PRINT 請?jiān)徴堅(jiān)? ,管理員表中數(shù)據(jù)不允許修改和刪管理員表中數(shù)據(jù)不允許修改和刪除！除！GOGODELETE AdminTbDELETE AdminTb -刪除刪除AdminTbAdminTb表中記錄表中記錄WHERE Admins=abcWHERE Admins=abcGOGO 11.3 11.3 修改、刪除、重命名和查看觸發(fā)器修改、刪除、重命名和查看觸發(fā)器 11.3

45、.1 11.3.1 修改觸發(fā)器修改觸發(fā)器修改觸發(fā)器的方法很簡單，其語法格式如下：修改觸發(fā)器的方法很簡單，其語法格式如下：ALTER TRIGGER trigger_nameALTER TRIGGER trigger_nameON table | view ON table | view WITH ENCRYPTION WITH ENCRYPTION FOR | AFTER | INSTEAD OF FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE INSERT , UPDATE , DELETE NOT FOR REPLICATION NOT

46、FOR REPLICATION ASASSQLSQL語句系列語句系列 修改觸發(fā)器語法與創(chuàng)建觸發(fā)器幾乎相同，只是把修改觸發(fā)器語法與創(chuàng)建觸發(fā)器幾乎相同，只是把CREATECREATE改改為為ALTERALTER即可。即可。 11.3.2 11.3.2 刪除觸發(fā)器刪除觸發(fā)器 DROP TRIGGERDROP TRIGGER語句可以從當(dāng)前數(shù)據(jù)庫中刪語句可以從當(dāng)前數(shù)據(jù)庫中刪除一個(gè)或多個(gè)除一個(gè)或多個(gè)DMLDML或或DDLDDL觸發(fā)器，語法格式如下：觸發(fā)器，語法格式如下： DROP TRIGGER trigger_name ,DROP TRIGGER trigger_name ,n n 11.3.3 11.3.3 重命名觸發(fā)器重命名觸發(fā)器 系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程sp_renamesp_rename用來重命名，語法格式如下：用來重命名，語法格式如下： EXECUTE sp_rename objname EXECUTE sp_rename objname = = object_na