數(shù)據(jù)庫(kù)系統(tǒng)管理、設(shè)計(jì)和實(shí)例分析下

1、.wd.wd.wd.第13章 觸發(fā)器原理及使用在上一章我們介紹了一般意義的存儲(chǔ)過(guò)程，即用戶自定義的存儲(chǔ)過(guò)程和系統(tǒng)存儲(chǔ)過(guò)程。本章將介紹一種特殊的存儲(chǔ)過(guò)程，即觸發(fā)器。以下幾個(gè)分幾個(gè)局部對(duì)觸發(fā)器的概念、作用、工作原理以及觸發(fā)器的設(shè)計(jì)和使用做盡介紹，使讀者了解如何定義觸發(fā)器，創(chuàng)立和使用各種不同復(fù)雜程度的觸發(fā)器。131 觸發(fā)器 基本概念 1311 觸發(fā)器的概念及作用觸發(fā)器可以看作是一類特殊的存儲(chǔ)過(guò)程，它在滿足某個(gè)特定條件時(shí)自動(dòng)觸發(fā)執(zhí)行。 觸發(fā)器是為表上的更新、插入、刪除操作定義的，也就是說(shuō)當(dāng)表上發(fā)生更新、插入或刪除操作時(shí)觸發(fā)器將執(zhí)行。存儲(chǔ)過(guò)程和觸發(fā)器同是提高數(shù)據(jù)庫(kù)服務(wù)器性能的有力工具。觸發(fā)器作為一種特殊

2、類型的存儲(chǔ)過(guò)程，它不同于我們前面介紹過(guò)的存儲(chǔ)過(guò)程。觸發(fā)器主要是通過(guò)事件進(jìn)展觸發(fā)而被執(zhí)行的，而存儲(chǔ)過(guò)程可以通過(guò)存儲(chǔ)過(guò)程名字而被直接調(diào)用。當(dāng)對(duì)某一表進(jìn)展諸如UPDATE、 INSERT、 DELETE 這些操作時(shí)，SQL Server 就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL 語(yǔ)句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL 語(yǔ)句所定義的規(guī)那么。觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性。除此之外，觸發(fā)器還有其它許多不同的功能：1 強(qiáng)化約束Enforce restriction觸發(fā)器可以偵測(cè)數(shù)據(jù)庫(kù)內(nèi)的操作，從而不允許數(shù)據(jù)庫(kù)中未經(jīng)許可的更新和變化。2級(jí)聯(lián)運(yùn)行Casca

3、ded Operation觸發(fā)器可以偵測(cè)數(shù)據(jù)庫(kù)內(nèi)的操作，并自動(dòng)地級(jí)聯(lián)影響整個(gè)數(shù)據(jù)庫(kù)的各項(xiàng)內(nèi)容。例如，某個(gè)表上的觸發(fā)器包含對(duì)另外一個(gè)表的數(shù)據(jù)操作如刪除、更新、插入，而該操作又導(dǎo)致該表上的觸發(fā)器被觸發(fā)。3存儲(chǔ)過(guò)程的調(diào)用(Stored Procedure Invocation)為了響應(yīng)數(shù)據(jù)庫(kù)更新，觸發(fā)器可以調(diào)用一個(gè)或多個(gè)存儲(chǔ)過(guò)程，甚至可以通過(guò)外部過(guò)程的調(diào)用而在DBMS之外進(jìn)展操作。由此可見(jiàn)，觸發(fā)器可以解決高級(jí)形式的業(yè)務(wù)規(guī)那么或復(fù)雜行為限制以及實(shí)現(xiàn)定制記錄等問(wèn)題。例如，觸發(fā)器能夠找出某一表在數(shù)據(jù)修改前后狀態(tài)發(fā)生的差異，并根據(jù)這種差異執(zhí)行一定的操作。此外一個(gè)表的同一類型INSERT、UPDATE、DEL

4、ETE的多個(gè)觸發(fā)器能夠?qū)ν环N數(shù)據(jù)操作采取多種不同的操作。 觸發(fā)器可以用于維護(hù)數(shù)據(jù)參照完整性和以下一些場(chǎng)合： 1觸發(fā)器可以通過(guò)級(jí)聯(lián)的方式對(duì)相關(guān)的表進(jìn)展修改。比方，對(duì)父表的修改，可以引起對(duì)子孫表的一系列修改，從而保證數(shù)據(jù)的一致性和完整性。 2觸發(fā)器可以制止或撤消違反參照完整性的修改。 3觸發(fā)器可以強(qiáng)制比用CHECK約束定義更加復(fù)雜的限制。 觸發(fā)器也是一個(gè)數(shù)據(jù)庫(kù)對(duì)象。一個(gè)觸發(fā)器和三局部?jī)?nèi)容有關(guān)：激活觸發(fā)器的表、激活觸發(fā)器的數(shù)據(jù)修改語(yǔ)句和觸發(fā)器要采取的動(dòng)作。 但是，觸發(fā)器性能通常比較低。當(dāng)運(yùn)行觸發(fā)器時(shí)，系統(tǒng)處理的大局部時(shí)間花費(fèi)在參照其它表的這一處理上，因?yàn)檫@些表既不在內(nèi)存中也不在數(shù)據(jù)庫(kù)設(shè)備上，而刪除

5、表和插入表總是位于內(nèi)存中?？梢?jiàn)觸發(fā)器所參照的其它表的位置決定了操作要花費(fèi)的時(shí)間長(zhǎng)短。1312 觸發(fā)器的種類SQL Server 2000 支持兩種類型的觸發(fā)器：AFTER觸發(fā)器和INSTEAD OF觸發(fā)器。1AFTER觸發(fā)器即為SQL Server 2000版本以前所介紹的觸發(fā)器。該類型觸發(fā)器要求只有執(zhí)行完某一操作INSERT、UPDATE、DELETE，并處理過(guò)所有約束后，觸發(fā)器才被觸發(fā)，且只能在表上定義。如果操作違反約束條件，將導(dǎo)致事務(wù)回滾，這時(shí)就不會(huì)執(zhí)行后觸發(fā)器?？梢詾獒槍?duì)表的同一操作定義多個(gè)AFTER觸發(fā)器。AFTER觸發(fā)器可以指定哪一個(gè)觸發(fā)器被最先觸發(fā)，哪一個(gè)被最后觸發(fā)，通常使用系統(tǒng)

6、過(guò)程sp_settriggerorder來(lái)完成此任務(wù)。2INSTEAD OF觸發(fā)器該類觸發(fā)器表示并不執(zhí)行其所定義的操作INSERT、UPDATE、DELETE，而僅是執(zhí)行觸發(fā)器本身。既可在表上定義INSTEAD OF觸發(fā)器，也可以在視圖上定義INSTEAD OF觸發(fā)器，但對(duì)同一操作只能定義一個(gè)INSTEAD OF觸發(fā)器。132 觸發(fā)器原理從以上的介紹中我們可以已了解到觸發(fā)器具有強(qiáng)大的功能，那么MS SQL Server 是如何使得觸發(fā)器能夠感知數(shù)據(jù)庫(kù)數(shù)據(jù)的變化、維護(hù)數(shù)據(jù)庫(kù)參照完整性及比CHECK約束更復(fù)雜的約束呢下面我們將對(duì)其工作原理及實(shí)現(xiàn)做較為詳細(xì)的介紹，以便大家學(xué)習(xí)創(chuàng)立、理解和使用各種類型

7、的觸發(fā)器，完成各種任務(wù)。每個(gè)觸發(fā)器有兩個(gè)特殊的表：插入表和刪除表，分別為inserted和deleted。有以下幾個(gè)特點(diǎn)：1這兩個(gè)表是邏輯表，并且這兩個(gè)表是由系統(tǒng)管理的，存儲(chǔ)在內(nèi)存中，不是存儲(chǔ)在數(shù)據(jù)庫(kù)中，因此不允許用戶直接對(duì)其修改。2這兩個(gè)表的構(gòu)造總是與被該觸發(fā)器作用的表有一樣的表構(gòu)造。3這兩個(gè)表是動(dòng)態(tài)駐留在內(nèi)存中的，當(dāng)觸發(fā)器工作完成，這兩個(gè)表也被刪除。這兩個(gè)表主要保存因用戶操作而被影響到的原數(shù)據(jù)值或新數(shù)據(jù)值。4另外，這兩個(gè)表是只讀的，且只在觸發(fā)器內(nèi)部可讀，即用戶不能向這兩個(gè)表寫入內(nèi)容，但可以在觸發(fā)器中引用表中的數(shù)據(jù)。例如在觸發(fā)器內(nèi)可用如下語(yǔ)句查看DELETED表中的信息：Select * f

8、rom deleted 下面詳細(xì)介紹這兩個(gè)表的功能。13.2.1 插入表的功能inserted對(duì)一個(gè)定義了插入類型觸發(fā)器的表來(lái)講，一旦對(duì)該表執(zhí)行了插入INSERT操作，那么對(duì)該表插入的所有行來(lái)說(shuō)，都有一個(gè)相應(yīng)的副本級(jí)存放到插入表inserted中，即插入表就是用來(lái)存儲(chǔ)原表插入的新數(shù)據(jù)行。13.2.2 刪除表的功能deleted對(duì)一個(gè)定義了刪除類型觸發(fā)器的表來(lái)講，一旦對(duì)該表執(zhí)行了刪除DELETE操作，那么將所有的被刪除的行存放至刪除表deleted表中。這樣做的目的是，一旦觸發(fā)器遇到了強(qiáng)迫它中止的語(yǔ)句被執(zhí)行時(shí)，刪除的那些行可以從刪除表(deleted表)中得以復(fù)原。 需要強(qiáng)調(diào)的是，更新UPDAT

9、E操作包括兩個(gè)局部，即先將舊的內(nèi)容刪除，然后將新值插入。因此，對(duì)一個(gè)定義了更新類型觸發(fā)器的表來(lái)講，當(dāng)執(zhí)行更新操作時(shí)，在刪除表中存放了修改之前的舊值，然后在插入表中存放的是修改之后的新值。由于觸發(fā)器僅當(dāng)被定義的操作被執(zhí)行時(shí)才被激活，即僅當(dāng)在執(zhí)行插入、刪除、和更新操作時(shí)，觸發(fā)器將執(zhí)行。每條SQL 語(yǔ)句僅能激活觸發(fā)器一次，可能存在一條語(yǔ)句影響多條記錄的情況。在這種情況下就需要變量rowcount 的值，該變量存儲(chǔ)了一條SQL 語(yǔ)句執(zhí)行后所影響的記錄數(shù)，可以使用該值對(duì)觸發(fā)器的SQL 語(yǔ)句執(zhí)行后所影響的記錄求合計(jì)值。一般來(lái)說(shuō)，首先要用IF 語(yǔ)句測(cè)試rowcount 的值以確定后面的語(yǔ)句是否執(zhí)行。13.2

10、.3插入視圖和刪除視圖當(dāng)在定義了觸發(fā)器的表上發(fā)生修改操作時(shí)會(huì)自動(dòng)派生出兩個(gè)視圖，一個(gè)是插入視圖，一個(gè)是刪除視圖。當(dāng)在表上發(fā)生插入操作時(shí)，新插入的行將出現(xiàn)在inserted表中形成插入視圖；當(dāng)在表上發(fā)生刪除操作時(shí)，被刪除的舊行將出現(xiàn)deleted表中，形成刪除視圖。而更新的實(shí)現(xiàn)過(guò)程是先刪除舊行，然后再插入新行。133 觸發(fā)器的創(chuàng)立和管理1331 創(chuàng)立觸發(fā)器上面介紹了有關(guān)觸發(fā)器的概念、作用和工作原理，下面我們將分別介紹在MS SQLServer 中如何用SQL Server 管理工具Enterprise Manager 和Transaction_SQL 來(lái)創(chuàng)立觸發(fā)器。在創(chuàng)立觸發(fā)器以前必須考慮到以下

11、幾個(gè)方面： CREATE TRIGGER 語(yǔ)句必須是批處理的第一個(gè)語(yǔ)句，將該批處理中隨后的其它所有語(yǔ)句解釋為 CREATE TRIGGER 語(yǔ)句定義的一局部； 創(chuàng)立觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶； 觸發(fā)器是數(shù)據(jù)庫(kù)對(duì)象，所以其命名必須符合命名規(guī)那么； 盡管在觸發(fā)器的SQL 語(yǔ)句中可以引用其它數(shù)據(jù)庫(kù)中的對(duì)象，但是，觸發(fā)器只能創(chuàng)立在當(dāng)前數(shù)據(jù)庫(kù)中； 雖然不能在臨時(shí)表或系統(tǒng)表上創(chuàng)立觸發(fā)器，但是觸發(fā)器可以引用臨時(shí)表。不應(yīng)引用系統(tǒng)表，而應(yīng)使用信息架構(gòu)視圖。有關(guān)更多信息，請(qǐng)參見(jiàn)第9章關(guān)于信息架構(gòu)視圖的內(nèi)容。一個(gè)觸發(fā)器只能對(duì)應(yīng)一個(gè)表，這是由觸發(fā)器的機(jī)制決定的；在含有用 DELETE

12、 或 UPDATE 操作定義的外鍵的表中，不能定義 INSTEAD OF 和 INSTEAD OF UPDATE 觸發(fā)器。 盡管TRUNCATE TABLE 語(yǔ)句如同沒(méi)有WHERE 從句的DELETE 語(yǔ)句，但是由于TRUNCATE TABLE 語(yǔ)句沒(méi)有被記入日志，所以該語(yǔ)句不能觸發(fā)DELETE 型觸發(fā)器； WRITETEXT 語(yǔ)句不能觸發(fā)INSERT 或UPDATE 型的觸發(fā)器。 在觸發(fā)器定義中，所有建設(shè)和更改數(shù)據(jù)庫(kù)以及數(shù)據(jù)庫(kù)對(duì)象的語(yǔ)句、所有的drop語(yǔ)句都不允許在觸發(fā)器中使用。在觸發(fā)器定義中，可使用IF UPDATE子句來(lái)測(cè)試INSERT、UPDATE語(yǔ)句中是否對(duì)指定字段有影響。如果將一個(gè)

13、值賦給指定字段或更改了指定的字段，那么這個(gè)子句為真。通常不要在觸發(fā)器中返回任何結(jié)果，因此不要在觸發(fā)器定義中使用select語(yǔ)句或變量賦值語(yǔ)句。當(dāng)創(chuàng)立一個(gè)觸發(fā)器時(shí)，必須指定觸發(fā)器的名字，在哪一個(gè)表上定義觸發(fā)器，激活觸發(fā)器的修改語(yǔ)句，如INSERT、 DELETE、 UPDATE。 當(dāng)然兩個(gè)或三個(gè)不同的修改語(yǔ)句也可以都觸發(fā)同一個(gè)觸發(fā)器，如INSERT 和UPDATE 語(yǔ)句都能激活同一個(gè)觸發(fā)器。1、用Enterprise Manger創(chuàng)立觸發(fā)器的步驟如下：1啟動(dòng)Enterprise Manager,登錄到要使用的服務(wù)器。2在Enterprise Manager的左窗格中，展開(kāi)要?jiǎng)?chuàng)立觸發(fā)器的數(shù)據(jù)庫(kù)文件

14、夾， 單擊“表文件夾，此時(shí)在右窗格中顯示該數(shù)據(jù)庫(kù)的所有表。3在右窗格中，右擊要?jiǎng)?chuàng)立觸發(fā)器的數(shù)據(jù)表，在彈出的快捷菜單中，將鼠標(biāo)指向“所有任務(wù)，在出現(xiàn)的下一級(jí)子菜單中選擇“管理觸發(fā)器菜單項(xiàng)，此時(shí)會(huì)出現(xiàn) “觸發(fā)器屬性對(duì)話框。圖13-1 選擇“管理觸發(fā)器菜單項(xiàng)圖13-2 “觸發(fā)器屬性對(duì)話框4在“名稱下拉框中選擇“，“文本編輯框中輸入觸發(fā)器的文本命令。圖13-3 輸入觸發(fā)器名字和T-SQL文本5單擊上圖所示的“檢查語(yǔ)法按鈕，檢查語(yǔ)句是否正確。如果正確轉(zhuǎn)第6部，否那么進(jìn)展修改6單擊“應(yīng)用按鈕，在“名稱下拉列框中會(huì)顯示新創(chuàng)立的觸發(fā)器名字。2、用CREATE TRIGGER命令創(chuàng)立觸發(fā)器可用CREATE TR

15、IGGER命令創(chuàng)立觸發(fā)器，其語(yǔ)法規(guī)那么如下CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bit

16、mask .n Sql 語(yǔ)句 .n 從以上語(yǔ)句可以看出，一個(gè)表最多可以有三類觸發(fā)器：插入INSERT觸發(fā)器、更新UPDATE觸發(fā)器、刪除DELETE觸發(fā)器。一個(gè)觸發(fā)器只能應(yīng)用到一個(gè)表上，但一個(gè)觸發(fā)器可以包含很多動(dòng)作，可以執(zhí)行很多功能。各參數(shù)的說(shuō)明如下：1trigger_name是用戶要?jiǎng)?chuàng)立的觸發(fā)器的名字。觸發(fā)器的名字必須符合SQL Server的命名規(guī)那么，且其名字在當(dāng)前數(shù)據(jù)庫(kù)中必須是惟一的。 2tablel|view是與用戶創(chuàng)立的觸發(fā)器相關(guān)聯(lián)的表的名字或視圖的名稱，并且此表或視圖必須已經(jīng)存在。3WITH ENCRYPTION 表示對(duì)包含有CREATE TRIGGER 文本的syscommen

17、ts 表進(jìn)展加密。4AFTER 表示只有在執(zhí)行了指定的操作INSERT、 DELETE、 UPDATE之后觸發(fā)器才被激活，所有的引用級(jí)聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行觸發(fā)器中的SQL 語(yǔ)句。指定觸發(fā)器只有在觸發(fā) SQL 語(yǔ)句中指定的所有操作都已成功執(zhí)行后才激發(fā)。假設(shè)使用關(guān)鍵字FOR， 那么表示為AFTER 觸發(fā)器，且該類型觸發(fā)器僅能在表上創(chuàng)立，不能在視圖上定義 AFTER 觸發(fā)器。5INSTEAD OF 指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā) SQL 語(yǔ)句，從而替代觸發(fā)語(yǔ)句的操作。在表或視圖上，每個(gè) INSERT、UPDATE 或 DELETE 語(yǔ)句最多可以定義一個(gè) INSTEAD OF 觸發(fā)

18、器。然而，可以在每個(gè)具有 INSTEAD OF 觸發(fā)器的視圖上定義視圖。INSTEAD OF 觸發(fā)器不能在 WITH CHECK OPTION 的可更新視圖上定義。如果向指定了 WITH CHECK OPTION 選項(xiàng)的可更新視圖添加 INSTEAD OF 觸發(fā)器，SQL Server 將產(chǎn)生一個(gè)錯(cuò)誤。用戶必須用 ALTER VIEW 刪除該選項(xiàng)后才能定義 INSTEAD OF 觸發(fā)器。6DELETE , INSERT , UPDATE 關(guān)鍵字用來(lái)指明哪種數(shù)據(jù)操作將激活觸發(fā)器。至少要指明其中的一個(gè)選項(xiàng)，在觸發(fā)器的定義中，三者的順序不受限制，各選項(xiàng)要用逗號(hào)隔開(kāi)。對(duì)于 INSTEAD OF 觸發(fā)器

19、，不允許在具有 ON DELETE 級(jí)聯(lián)操作引用關(guān)系的表上使用 DELETE 選項(xiàng)。同樣，也不允許在具有 ON UPDATE 級(jí)聯(lián)操作引用關(guān)系的表上使用 UPDATE 選項(xiàng)。7WITH APPEND說(shuō)明增加另外一個(gè)已存在的觸發(fā)器。只有在兼容性水平指某一數(shù)據(jù)庫(kù)行為與以前版本的SQL Server兼容程度不大于65時(shí)才使用該選項(xiàng)。WITH APPEND 不能與 INSTEAD OF 觸發(fā)器一起使用，或者，如果顯式聲明 AFTER 觸發(fā)器，也不能使用該子句。只有當(dāng)出于向后兼容而指定 FOR 時(shí)沒(méi)有 INSTEAD OF 或 AFTER，才能使用 WITH APPEND。以后的版本將不支持 WITH

20、APPEND 和 FOR將被解釋為 AFTER。8NOT FOR REPLICATION 說(shuō)明當(dāng)復(fù)制處理修改與觸發(fā)器相關(guān)聯(lián)的表時(shí)，觸發(fā)器不能被執(zhí)行。9AS是觸發(fā)器將要執(zhí)行的動(dòng)作。 10sql語(yǔ)句 是包含在觸發(fā)器中的條件語(yǔ)句和處理語(yǔ)句。觸發(fā)器的條件語(yǔ)句定義了另外的標(biāo)準(zhǔn)來(lái)決定將被執(zhí)行的INSERT、 DELETE、 UPDATE 語(yǔ)句是否激活觸發(fā)器。當(dāng)嘗試 DELETE、INSERT 或 UPDATE 操作時(shí)，Transact-SQL語(yǔ)句中指定的觸發(fā)器操作將生效。觸發(fā)器可以包含任意數(shù)量和種類的 Transact-SQL 語(yǔ)句。觸發(fā)器旨在根據(jù)數(shù)據(jù)修改語(yǔ)句檢查或更改數(shù)據(jù)；它不應(yīng)將數(shù)據(jù)返回給用戶。觸發(fā)器

21、中的 Transact-SQL 語(yǔ)句常常包含控制流語(yǔ)言。CREATE TRIGGER 語(yǔ)句中使用兩個(gè)特殊的表：deleted 和 inserted 表，它們是邏輯概念表。這些表在構(gòu)造上類似于定義觸發(fā)器的表，用于保存用戶操作可能更改的行的舊值或新值。11IF UPDATE column 測(cè)試在指定的列上進(jìn)展的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作。可以指定多列。因?yàn)樵?ON 子句中指定了表名，所以在 IF UPDATE 子句中的列名前不要包含表名。假設(shè)要測(cè)試在多個(gè)列上進(jìn)展的 INSERT 或 UPDATE 操作，請(qǐng)?jiān)诘谝粋€(gè)操作后指定單獨(dú)的 UPDATE(column

22、) 子句。在 INSERT 操作中 IF UPDATE 將返回 TRUE 值，因?yàn)檫@些列插入了顯式值或隱性 (NULL) 值。說(shuō)明：IF UPDATE (column) 子句的功能等同于 IF、IF.ELSE 或 WHILE 語(yǔ)句，并且可以使用 BEGIN.END 語(yǔ)句塊。可以在觸發(fā)器主體中的任意位置使用 UPDATE (column)。其中column是要測(cè)試 INSERT 或 UPDATE 操作的列名。該列可以是 SQL Server 支持的任何數(shù)據(jù)類型，但是，計(jì)算列不能用于該環(huán)境中。 12IF COLUMNS_UPDATED 僅在INSERT 和UPDATE 類型的觸發(fā)器中使用，用其來(lái)檢

23、查所涉及的列是被更新還是被插入。 COLUMNS_UPDATED 返回 varbinary 位模式，表示插入或更新了表中的哪些列。COLUMNS_UPDATED 函數(shù)以從左到右的順序返回位，最左邊的為最不重要的位。最左邊的位表示表中的第一列；向右的下一位表示第二列，依此類推。如果在表上創(chuàng)立的觸發(fā)器包含 8 列以上，那么 COLUMNS_UPDATED 返回多個(gè)字節(jié)，最左邊的為最不重要的字節(jié)。在 INSERT 操作中 COLUMNS_UPDATED 將對(duì)所有列返回 TRUE 值，因?yàn)檫@些列插入了顯式值或隱性 (NULL) 值?？梢栽谟|發(fā)器主體中的任意位置使用 COLUMNS_UPDATED。13

24、Bitwise_operatorj 是在比較中使用的位邏輯運(yùn)算符。 14Pdated_bitmask 是那些被更新或插入的列的整形位掩碼。例如，如果表T 包括C1， C2， C3， C4， C5五列。為了確定是否只有C2 列被修改，可用2 來(lái)做位掩碼，如果想確定是否C1， C3，C4 都被修改，可用13 來(lái)做位掩碼。 15Comparison_operator 是一比較操作符，用“= 表示檢查在updated_bitmask 中定義的所有列是否都被更新，用“ 表示檢查是否在updated_bitmask 中定義的某些列被更新。 16Column_bitmask 指那些被檢查是否被更新的列的位掩

25、碼。例13-1 創(chuàng)立一個(gè)觸發(fā)器，當(dāng)向STUDENT表中插入一條學(xué)生記錄時(shí)，自動(dòng)顯示該表中的記錄。 CREATE TRIGGER Change_Display On STUDENT FOR INSERT AS SELECT * FROM STUDENT該觸發(fā)器建設(shè)完畢后，當(dāng)對(duì)STUDENT表的插入操作執(zhí)行成功時(shí)，將會(huì)顯示STUDENT數(shù)據(jù)表中的全部記錄。權(quán)限說(shuō)明：CREATE TRIGGER 權(quán)限默認(rèn)授予定義觸發(fā)器的表所有者、sysadmin 固定服務(wù)器角色成員以及 db_owner 和 db_ddladmin 固定數(shù)據(jù)庫(kù)角色成員，并且不可轉(zhuǎn)讓。假設(shè)要檢索表或視圖中的數(shù)據(jù)，用戶必須在表或視圖中擁

26、有 SELECT 語(yǔ)句權(quán)限。假設(shè)要更新表或視圖的內(nèi)容，用戶必須在表或視圖中擁有 INSERT、DELETE 和 UPDATE 語(yǔ)句權(quán)限。如果視圖中存在 INSTEAD OF 觸發(fā)器，用戶必須在該視圖中有 INSERT、DELETE 和 UPDATE 特權(quán)，以對(duì)該視圖發(fā)出 INSERT、DELETE 和 UPDATE 語(yǔ)句，而不管實(shí)際上是否在視圖上執(zhí)行了這樣的操作。1332管理觸發(fā)器如果要顯示作用于表上的觸發(fā)器終究對(duì)表有哪些操作，必須查看觸發(fā)器信息。在SQL Server中，有多種方法查看觸發(fā)器信息。接下來(lái)，我們將介紹兩種常用的方法，即通過(guò)SQL Server 的管理工具Enterprise M

27、anager 以及系統(tǒng)存儲(chǔ)過(guò)程sp_help、sp_helptext和sp_depends來(lái)查看觸發(fā)器信息。1使用Enterprise Manager顯示觸發(fā)器信息使用Enterprise Manager顯示觸發(fā)器信息，步驟如下。 1啟動(dòng)Enterprise Manager,登錄到要使用的服務(wù)器。 2在Enterprise Manager的左窗格中，展開(kāi)要?jiǎng)?chuàng)立觸發(fā)器的數(shù)據(jù)庫(kù)文件夾，單擊“表文件夾，此時(shí)在右窗格中顯示該數(shù)據(jù)庫(kù)的所有表。 3在右窗格中，右擊要?jiǎng)?chuàng)立觸發(fā)器的數(shù)據(jù)表，在彈出的快捷菜單中，將鼠標(biāo)指向“所有任務(wù)，在出現(xiàn)的下一級(jí)子菜單中選擇“管理觸發(fā)器菜單項(xiàng)，出現(xiàn)“觸發(fā)器屬性對(duì)話框，如圖13-

28、4所示。圖13-4 選擇“管理觸發(fā)器圖13-5 查看觸發(fā)器文本4在“名稱下拉列表中選擇所要查看的觸發(fā)器的名稱，在“文本編輯框中顯示出該觸發(fā)器的文本命令，如圖13-5所示。2使用系統(tǒng)存儲(chǔ)過(guò)程查看觸發(fā)器系統(tǒng)存儲(chǔ)過(guò)程 sp_help、 sp_helptext和sp_depends分別提供有關(guān)觸發(fā)器的不同信息。下面我們將分別對(duì)其進(jìn)展介紹。1sp_help通過(guò)該系統(tǒng)過(guò)程，可以了解觸發(fā)器的一般信息，如觸發(fā)器的名字、屬性、類型、創(chuàng)立時(shí)間。使用sp_help系統(tǒng)過(guò)程的命令格式為： sp_help 觸發(fā)器名字例13-2 查看我們已經(jīng)建設(shè)的change_display觸發(fā)器。 sp_help change_dis

29、play (2) sp_helptext 通過(guò)sp_helptext能夠查看觸發(fā)器的正文信息，其語(yǔ)法格式為： sp_helptext 觸發(fā)器名例13-3 查看我們已經(jīng)建設(shè)的chang_display觸發(fā)器的命令文本。 sp_helptext change_display (3) sp_depends通過(guò)sp_depends能夠查看指定觸發(fā)器所引用的表或指定的表所涉及到的所有觸發(fā)器。其語(yǔ)法形式如下： sp_depends 觸發(fā)器名字 sp_depends 表名例13-4 查看我們已經(jīng)建設(shè)的change-display觸發(fā)器所涉及的表。 sp_depends change_display注意：用戶

30、必須在當(dāng)前數(shù)據(jù)庫(kù)中查看觸發(fā)器的信息，而且被查看的觸發(fā)器必須已經(jīng)被創(chuàng)立。1333 修改、刪除觸發(fā)器通過(guò)Enterprise Manager修改觸發(fā)器正文和刪除觸發(fā)器，其步驟與查看觸發(fā)器信息一樣。修改完觸發(fā)器后要使用“檢查語(yǔ)法選項(xiàng)對(duì)語(yǔ)句進(jìn)展檢查，這里不再贅述。以下介紹用T-SQL命令修改觸發(fā)器。1、修改觸發(fā)器1使用sp_rename命令，修改觸發(fā)器的名字 sp_rename命令的語(yǔ)法格式為： sp_rename oldname, newnameoldname為觸發(fā)器原來(lái)的名稱，newname為觸發(fā)器的新名稱。2使用Alert trigger，修改觸發(fā)器的正文 Alert trigger命令的語(yǔ)法格

31、式為：ALTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n Sql 語(yǔ)句 .n 其

32、中各參數(shù)或保存字的含義參看“創(chuàng)立觸發(fā)器一節(jié)。 2、刪除觸發(fā)器可以使用企業(yè)管理器和T-SQL語(yǔ)句刪除觸發(fā)器。T-SQL語(yǔ)句的語(yǔ)句格式為：DROP TRIGGER trigger_name ,.n 功能是：從當(dāng)前數(shù)據(jù)庫(kù)中刪除一個(gè)或多個(gè)觸發(fā)器。注釋：可以通過(guò)除去觸發(fā)器或除去觸發(fā)器表刪除觸發(fā)器。除去表時(shí)，也將除去所有與表關(guān)聯(lián)的觸發(fā)器。除去觸發(fā)器時(shí)，將從 sysobjects 和 syscomments 系統(tǒng)表中刪除有關(guān)觸發(fā)器的信息。權(quán)限：默認(rèn)情況下，將 DROP TRIGGER 權(quán)限授予觸發(fā)器表的所有者，該權(quán)限不可轉(zhuǎn)讓。然而，db_owner 和 db_dlladmin 固定數(shù)據(jù)庫(kù)角色成員或 sysa

33、dmin 固定服務(wù)器角色成員可以通過(guò)在 DROP TRIGGER 語(yǔ)句內(nèi)顯式指定所有者除去任何對(duì)象。例13-5除去 change-display 觸發(fā)器。USE uni_dbgoIF EXISTS (SELECT name FROM sysobjects WHERE name =change_display AND type = TR) DROP TRIGGER change_displayGO134 使用觸發(fā)器實(shí)現(xiàn)強(qiáng)制業(yè)務(wù)規(guī)那么用觸發(fā)器可以實(shí)現(xiàn)強(qiáng)制的業(yè)務(wù)規(guī)那么，常用的方法有：使用INSERT、UPDATE、DELETE、INSTEAD OF 觸發(fā)器、使用嵌套觸發(fā)器和遞歸出發(fā)器。1341 IN

34、SERT 觸發(fā)器INSERT出發(fā)器當(dāng)向表中添加記錄時(shí)觸發(fā)，為了維護(hù)數(shù)據(jù)完整性，當(dāng)表中添加了新的記錄后，應(yīng)該對(duì)其關(guān)聯(lián)表的數(shù)據(jù)進(jìn)展調(diào)整，以實(shí)時(shí)反響數(shù)據(jù)的變化。例如當(dāng)某個(gè)學(xué)生選定了某門課的開(kāi)課方案后，應(yīng)當(dāng)更新選課人數(shù)。需要用到inserted表,因?yàn)樽鯥NSERT操作時(shí)，在inserted表中存放的是要增加到該觸發(fā)器作用的表中的新的元組，而deleted表示此時(shí)為空。例13-6 在大學(xué)數(shù)據(jù)庫(kù)中參照第9章實(shí)例分析中調(diào)整過(guò)的構(gòu)造，當(dāng)新的學(xué)生選課注冊(cè)信息增加到ENROLLMENT表中的時(shí)候，要對(duì)OFFERING表中學(xué)生選課人數(shù)進(jìn)展更新，且當(dāng)人數(shù)超過(guò)最多能容納的人數(shù)時(shí)，要提示選課人數(shù)已滿的信息。USE un

35、i_dbgoCREATE TRIGGER Tri_ENROLL_INSERTON ENROLLMENTFOR INSERTASBEGINDECLARE E_NUM INT,L_NUM INTSELECT E_NUM=ENROLL_NUM+1, L_NUM=LIMIT_NUMFROM OFFERING O,inserted iWhere O.ONO=i.ONOIF (E_NUM L_NUM) BEGIN PRINT 選修人數(shù)已滿！ ROLLBACK TRANSACTION ENDUPDATE OFFERINGSET ENROLL_NUM=E_NUM FROM OFFERING O, insert

36、ed i Where O.ONO=i.ONOEND1342 UPDATE 觸發(fā)器當(dāng)更新表中的元組時(shí)觸發(fā)執(zhí)行UPDATE觸發(fā)器?？梢杂肬PDATE (column)測(cè)試在指定的列上進(jìn)展的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作?？梢灾付ǘ嗔?。因?yàn)樵?ON 子句中指定了表名，所以在 IF UPDATE 子句中的列名前不要包含表名。在 INSERT 操作中 IF UPDATE 將返回 TRUE 值，因?yàn)檫@些列插入了顯式值或隱性 (NULL) 值。也可以用COLUMNS_UPDATED()來(lái)測(cè)試是否更新了指定的列。COLUMNS_UPDATED函數(shù)返回varbinary位模

37、式，表示插入或更新了表中的哪些列。COLUMNS_UPDATED函數(shù)以從左到右的順序返回位，最右邊的位表示表中的第一列；向左的下一位表示第二列，依此類推。例如表中有5列，要判斷第二列是不是被更新，那么要測(cè)試COLUMNS_UPDATED是否返回2二進(jìn)制00010。此時(shí)用到inserted和deleted表，在inserted表中，存放的是執(zhí)行UPDATE操作的表中被修改的那些記錄修改之后的新值，而在delelted表中，存放的是執(zhí)行UPDATE操作的表中被修改的那些記錄修改之前的舊值。UPDATE觸發(fā)器通常用于數(shù)據(jù)的級(jí)聯(lián)修改。例13-7使用觸發(fā)器實(shí)現(xiàn)當(dāng)某門課程的代碼發(fā)生變化時(shí)，級(jí)聯(lián)修改開(kāi)課方案

38、中該課程的代碼如果已有開(kāi)課方案的話。USE uni_dbGOIF EXISTS (SELECT name FROM sysobjects WHERE name =Tri_COURSE_UPD AND type = TR) DROP TRIGGER Tri_COURSE_UPDGOCREATE TRIGGER Tri_COURSE_UPDON COURSEFOR UPDATEASBEGIN-課程號(hào)CNO是COURSE表中的第一列，共7列IF ( COLUMNS_UPDATED() & 1 = 1)BeginDECLARE OLD_CNO CHAR(5),NEW_CNO CHAR(5)SELECT

39、 OLD_CNO=CNOFROM deletedSELECT NEW_CNO=i.CNOFROM inserted i, COURSE CWHERE i.CNO=C.CNO UPDATE OFFERING SET CNO=NEW_CNO WHERE CNO=OLD_CNOEndEND當(dāng)然該觸發(fā)器只能實(shí)現(xiàn)只修改了一門課程的課程號(hào)的級(jí)聯(lián)修改，如果UPDATE語(yǔ)句一次修改了多門課程的課程號(hào)，那么可以在觸發(fā)器中使用游標(biāo)實(shí)現(xiàn)逐門課程的級(jí)聯(lián)修改。1343 DELETE 觸發(fā)器當(dāng)刪除表中數(shù)據(jù)時(shí)觸發(fā)執(zhí)行DELETE觸發(fā)器，用它可以實(shí)現(xiàn)級(jí)聯(lián)刪除。此時(shí)，用到deleted表，該表中存放的是剛剛被刪除的那些元組，而

40、inserted表為空。例13-8 當(dāng)某個(gè)學(xué)生退學(xué)時(shí)，須刪除該學(xué)生的 基本數(shù)據(jù)，并級(jí)聯(lián)刪除該學(xué)生的選課記錄。USE uni_dbGOIF EXISTS (SELECT name FROM sysobjects WHERE name =Tri_STUDENT_DEL AND type = TR) DROP TRIGGER Tri_STUDENT_DELGOCREATE TRIGGER Tri_STUDENT_DELON STUDENTFOR DELETEASBEGINDELETE FROM ENROLLMENTFROM ENROLLMENT E,deleted dWHERE E.SNO=d.SN

41、OEND1344 INSTEAD OF 觸發(fā)器用INSTEAD OF可以指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)語(yǔ)句本身，從而屏蔽原來(lái)的SQL語(yǔ)句，而轉(zhuǎn)向執(zhí)行觸發(fā)器內(nèi)部的SQL語(yǔ)句。對(duì)同一操作只能定義一個(gè)INSTEAD OF觸發(fā)器?？梢杂肐NSTEAD OF進(jìn)展業(yè)務(wù)規(guī)那么的判斷，進(jìn)而決定是否執(zhí)行觸發(fā)SQL語(yǔ)句。例13-9 當(dāng)刪除某開(kāi)課方案時(shí)，需先查看該開(kāi)課方案有沒(méi)有學(xué)生注冊(cè)，如果有，那么不能刪除。USE uni_dbGOIF EXISTS (SELECT name FROM sysobjects WHERE name =Tri_OFFERING_DEL AND type = TR) DROP TRIGGE

42、R Tri_OFFERING_DELGOCREATE TRIGGER Tri_OFFERING_DELON OFFERINGINSTEAD OF DELETEASBEGINIF EXISTS(SELECT * FROM ENROLLMENT E,deleted dWhere e.ONO=d.ONO) PRINT 有學(xué)生注冊(cè)該課程開(kāi)課方案，不能刪除ELSE DELETE FROM ENROLLMENT FROM ENROLLMENT E,deleted dWhere e.ONO=d.ONOEND1345 遞歸觸發(fā)器當(dāng)在 sp_dboption 中啟用 recursive triggers 設(shè)置時(shí)

43、，SQL Server 還允許觸發(fā)器的遞歸調(diào)用。遞歸觸發(fā)器允許發(fā)生兩種類型的遞歸： 間接遞歸直接遞歸 使用間接遞歸時(shí)，應(yīng)用程序更新表 T1，從而激發(fā)觸發(fā)器 TR1，該觸發(fā)器更新表 T2。在這種情況下，觸發(fā)器 T2 將激發(fā)并更新 T1。使用直接遞歸時(shí)，應(yīng)用程序更新表 T1，從而激發(fā)觸發(fā)器 TR1，該觸發(fā)器更新表 T1。由于表 T1 被更新，觸發(fā)器 TR1 再次激發(fā)，依此類推。下例既使用了間接觸發(fā)器遞歸，又使用了直接觸發(fā)器遞歸。假定在表 T1 中定義了兩個(gè)更新觸發(fā)器 TR1 和 TR2。觸發(fā)器 TR1 遞歸地更新表 T1。UPDATE 語(yǔ)句使 TR1 和 TR2 各執(zhí)行一次。而 TR1 的執(zhí)行將觸

44、發(fā) TR1遞歸和 TR2 的執(zhí)行。給定觸發(fā)器的 inserted 和 deleted 表只包含與喚醒調(diào)用觸發(fā)器的 UPDATE 語(yǔ)句相對(duì)應(yīng)的行。說(shuō)明 只有啟用 sp_dboption 的 recursive triggers 設(shè)置，才會(huì)發(fā)生上述行為。對(duì)于為給定事件定義的多個(gè)觸發(fā)器，并沒(méi)有確定的執(zhí)行順序。每個(gè)觸發(fā)器都應(yīng)是自包含的。禁用 recursive triggers 設(shè)置只能制止直接遞歸。假設(shè)要也禁用間接遞歸，請(qǐng)使用 sp_configure 將 nested triggers 服務(wù)器選項(xiàng)設(shè)置為 0。如果任一觸發(fā)器執(zhí)行了 ROLLBACK TRANSACTION 語(yǔ)句，那么無(wú)論嵌套級(jí)是多少

45、，都不會(huì)進(jìn)一步執(zhí)行其它觸發(fā)器。1346 嵌套觸發(fā)器觸發(fā)器最多可以嵌套 32 層。如果一個(gè)觸發(fā)器更改了包含另一個(gè)觸發(fā)器的表，那么第二個(gè)觸發(fā)器將激活，然后該觸發(fā)器可以再調(diào)用第三個(gè)觸發(fā)器，依此類推。如果鏈中任意一個(gè)觸發(fā)器引發(fā)了無(wú)限循環(huán)，那么會(huì)超出嵌套級(jí)限制，從而導(dǎo)致取消觸發(fā)器。假設(shè)要禁用嵌套觸發(fā)器，請(qǐng)用 sp_configure 將 nested triggers 選項(xiàng)設(shè)置為 0關(guān)閉。默認(rèn)配置允許嵌套觸發(fā)器。如果嵌套觸發(fā)器是關(guān)閉的，那么也將禁用遞歸觸發(fā)器，與 sp_dboption 的 recursive triggers 設(shè)置無(wú)關(guān)。135 使用觸發(fā)器的T-SQL限制CREATE TRIGGER 必

46、須是批處理中的第一條語(yǔ)句，并且只能應(yīng)用到一個(gè)表中。 觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫(kù)中創(chuàng)立，不過(guò)觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫(kù)的外部對(duì)象。 如果指定觸發(fā)器所有者名稱以限定觸發(fā)器，請(qǐng)以一樣的方式限定表名。 在同一條 CREATE TRIGGER 語(yǔ)句中，可以為多種用戶操作如 INSERT 和 UPDATE定義一樣的觸發(fā)器操作。 如果一個(gè)表的外鍵在 DELETE/UPDATE 操作上定義了級(jí)聯(lián)，那么不能在該表上定義 INSTEAD OF DELETE/UPDATE 觸發(fā)器。在觸發(fā)器內(nèi)可以指定任意的 SET 語(yǔ)句。所選擇的 SET 選項(xiàng)在觸發(fā)器執(zhí)行期間有效，并在觸發(fā)器執(zhí)行完后恢復(fù)到以前的設(shè)置。與使用存儲(chǔ)過(guò)程一樣，

47、當(dāng)觸發(fā)器激發(fā)時(shí)，將向調(diào)用應(yīng)用程序返回結(jié)果。假設(shè)要防止由于觸發(fā)器激發(fā)而向應(yīng)用程序返回結(jié)果，請(qǐng)不要包含返回結(jié)果的 SELECT 語(yǔ)句，也不要包含在觸發(fā)器中進(jìn)展變量賦值的語(yǔ)句。包含向用戶返回結(jié)果的 SELECT 語(yǔ)句或進(jìn)展變量賦值的語(yǔ)句的觸發(fā)器需要特殊處理；這些返回的結(jié)果必須寫入允許修改觸發(fā)器表的每個(gè)應(yīng)用程序中。如果必須在觸發(fā)器中進(jìn)展變量賦值，那么應(yīng)該在觸發(fā)器的開(kāi)頭使用 SET NOCOUNT 語(yǔ)句以防止返回任何結(jié)果集。 DELETE 觸發(fā)器不能捕獲 TRUNCATE TABLE 語(yǔ)句。盡管 TRUNCATE TABLE 語(yǔ)句實(shí)際上是沒(méi)有 WHERE 子句的 DELETE它刪除所有行，但它是無(wú)日志記

48、錄的，因而不能執(zhí)行觸發(fā)器。因?yàn)?TRUNCATE TABLE 語(yǔ)句的權(quán)限默認(rèn)授予表所有者且不可轉(zhuǎn)讓，所以只有表所有者才需要考慮無(wú)意中用 TRUNCATE TABLE 語(yǔ)句躲避 DELETE 觸發(fā)器的問(wèn)題。無(wú)論有日志記錄還是無(wú)日志記錄，WRITETEXT 語(yǔ)句都不激活觸發(fā)器。觸發(fā)器中不允許使用以下T-SQL語(yǔ)句：ALTER DATABASE、CREATE DATABASE、DISK INIT、DISK RESIZE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE和RESTORE LOG。說(shuō)明：由于 SQL Ser

49、ver 不支持系統(tǒng)表中的用戶定義觸發(fā)器，因此建議不要在系統(tǒng)表中創(chuàng)立用戶定義觸發(fā)器。136 觸發(fā)器應(yīng)用實(shí)例分析問(wèn)題提出：我們?cè)诘?2章實(shí)例分析1中，使用帶鉛條游標(biāo)的存儲(chǔ)過(guò)程核查并修改學(xué)生獲取學(xué)分情況，但為了進(jìn)一步保證數(shù)據(jù)的一致性，我們必須要保證，在學(xué)生考試完畢錄入成績(jī)后，要能夠根據(jù)成績(jī)及時(shí)將學(xué)生新獲取的課程的學(xué)分累計(jì)上去，獲取學(xué)分的條件是考試成績(jī)不低于60分；而當(dāng)成績(jī)被修改時(shí)，要能夠根據(jù)所做修改更新學(xué)分比方原來(lái)不及格，修改后及格的話要增加學(xué)分，反之要減掉原來(lái)加上去的學(xué)分，如果及格屬性不變，那么不修改學(xué)分。同時(shí)如果某學(xué)生某一門課程的成績(jī)?nèi)咳∠脑挘軌蛲瑫r(shí)取消這門課程所獲得的學(xué)分。分析1：由于

50、學(xué)生選課管理的實(shí)際情況，學(xué)生在期初或前一學(xué)期完畢之前就進(jìn)展選課，而成績(jī)是在學(xué)期末考試后輸入，所以錄入成績(jī)實(shí)際上是對(duì)ENROLLENT表的數(shù)據(jù)的修改。故我們可以創(chuàng)立該表的修改觸發(fā)器，實(shí)現(xiàn)學(xué)分的自動(dòng)累計(jì)。由于成績(jī)修改UPDATE語(yǔ)句可能涉及多個(gè)學(xué)生，故我們要在觸發(fā)器中使用游標(biāo)對(duì)每個(gè)學(xué)生進(jìn)展判斷修改。例13-10 創(chuàng)立ENROLLMEN表的UPDATE觸發(fā)器，實(shí)現(xiàn)學(xué)分的級(jí)聯(lián)修改。CREATE TRIGGER Tri_ENROLL_UPDON ENROLLMENTFOR UPDATEASBEGINIF (ROWCOUNT0)Begin Declare old_grade float, new_grad

51、e float Declare SNO_d char(6),ONO_d char(6),SNO_i char(6),ONO_i char(6) Declare ogpa int DECLARE ENROLL_inserted_cur cursorFor Select SNO,ONO,GRADEFrom insertedDECLARE ENROLL_deleted_cur cursorFor Select SNO,ONO,GRADEFrom deleted Open ENROLL_inserted_curOpen ENROLL_deleted_curFetch next from ENROLL_

52、inserted_cur into SNO_i, ONO_i,new_gradeFetch next from ENROLL_deleted_cur into SNO_d, ONO_d,old_gradeselect ogpa=OGPAfrom OFERRINGWHERE ONO=ONO_iwhile fetch_status=0begin if (old_grade is null) and (new_grade=60) update STUDENT SET SGPA=SGPA+ogpa Where SNO=SNO_iIf (old_grade = 60) update STUDENT SE

53、T SGPA=SGPA+ogpa Where SNO=SNO_iIf (old_grade = 60) and (new_grade 0)Begin Declare grade float Declare SNO char(6),ONO char(6) Declare ogpa int DECLARE ENROLL_deleted_cur cursorFor Select SNO,ONO,GRADEFrom deleted Open ENROLL_deleted_curFetch next from ENROLL_deleted_cur into SNO, ONO,gradeselect og

54、pa=OGPAfrom OFERRINGWHERE ONO=ONOwhile fetch_status=0begin If (grade = 60) update STUDENT SET SGPA=SGPA - ogpa Where SNO=SNOFetch next from ENROLL_deleted_cur into SNO, ONO,gradeselect ogpa=OGPAfrom OFERRINGWHERE ONO=ONOendEnd END小結(jié)觸發(fā)器是一種特殊類型的存儲(chǔ)過(guò)程，與其他類型存儲(chǔ)過(guò)程不同的是：它是通過(guò)事件進(jìn)展觸發(fā)而被執(zhí)行的，而存儲(chǔ)過(guò)程可以通過(guò)存儲(chǔ)過(guò)程名字而被直接調(diào)用。

55、當(dāng)對(duì)某一表進(jìn)展諸如UPDATE、 INSERT、 DELETE 這些操作時(shí)，SQL Server 就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL 語(yǔ)句。本章我們主要介紹了觸發(fā)器的概念、觸發(fā)器的種類、觸發(fā)器的工作原理以及對(duì)如何使用觸發(fā)器實(shí)現(xiàn)復(fù)雜強(qiáng)制約束，包括觸發(fā)器的創(chuàng)立、修改、刪除以及對(duì)觸發(fā)器的管理，并通過(guò)實(shí)例介紹對(duì)觸發(fā)器的應(yīng)用。思考與練習(xí)1、什么是觸發(fā)器觸發(fā)器有哪些種類2、觸發(fā)器與存儲(chǔ)過(guò)程區(qū)別 3、什么是插入視圖什么是更新視圖4、簡(jiǎn)述觸發(fā)器的工作原理。5、SQL Server系統(tǒng)如何感知數(shù)據(jù)庫(kù)表中數(shù)據(jù)的變化6、設(shè)計(jì)PARTICIPATION表的插入觸發(fā)器，保證插入PARTICIPATION表的元組的ENO和

56、PNO分別參照EMPLOYEE表和PROJECT表的主碼，如果不是，那么回滾該插入操作，以維護(hù)參照完整性。7、設(shè)計(jì)EMPLOYEE表的插入觸發(fā)器，當(dāng)新員工數(shù)據(jù)增加到數(shù)據(jù)庫(kù)時(shí)，實(shí)現(xiàn)在DEPARTMENT表中相應(yīng)部門人數(shù)的增加。8、設(shè)計(jì)EMPLOYEE表的刪除觸發(fā)器，當(dāng)刪除員工數(shù)據(jù)時(shí)，實(shí)現(xiàn)相應(yīng)部門人數(shù)的減少。9、設(shè)計(jì)EMPLOYEE表的修改觸發(fā)器，當(dāng)修改員工的編號(hào)時(shí)，要級(jí)聯(lián)修改PARTICIPATION表中相應(yīng)員工的編號(hào)，當(dāng)修改員工所在部門時(shí)員工進(jìn)展內(nèi)部調(diào)動(dòng)，要級(jí)聯(lián)修改相關(guān)部門的人數(shù)。10、為了維護(hù)數(shù)據(jù)的完整性和一致性，考慮還可以設(shè)計(jì)工程管理數(shù)據(jù)庫(kù)其它表上的哪些觸發(fā)器，并實(shí)現(xiàn)它們。第14章 用戶自

57、定義函數(shù)141 用戶自定義函數(shù)概述除了使用系統(tǒng)提供的函數(shù)外，用戶還可以根據(jù)需要自定義函數(shù)。用戶自定義函數(shù)User Defined Functions是SQL Server 2000 新增的數(shù)據(jù)庫(kù)對(duì)象，是SQL Server 的一大改進(jìn)。用戶自定義函數(shù)不能用于執(zhí)行一系列改變數(shù)據(jù)庫(kù)狀態(tài)的操作，但它可以像系統(tǒng)函數(shù)一樣在查詢或存儲(chǔ)過(guò)程等的程序段中使用，也可以像存儲(chǔ)過(guò)程一樣通過(guò)EXECUTE 命令來(lái)執(zhí)行。用戶自定義函數(shù)中存儲(chǔ)了一個(gè)Transact-SQL 例程，可以返回一定的值?？墒褂?CREATE FUNCTION 語(yǔ)句創(chuàng)立、使用 ALTER FUNCTION 語(yǔ)句修改、以及使用 DROP FUNCT

58、ION 語(yǔ)句除去用戶定義函數(shù)。每個(gè)完全合法的用戶定義函數(shù)名 (database_name.owner_name.function_name) 必須唯一。必須被授予 CREATE FUNCTION 權(quán)限才能創(chuàng)立、修改或除去用戶定義函數(shù)。不是所有者的用戶在 Transact-SQL 語(yǔ)句中使用某個(gè)函數(shù)之前，必須先給此用戶授予該函數(shù)的適當(dāng)權(quán)限。假設(shè)要?jiǎng)?chuàng)立或更改在 CHECK 約束、DEFAULT 子句或計(jì)算列定義中引用用戶定義函數(shù)的表，還必須具有函數(shù)的 REFERENCES 權(quán)限。在SQL Server 2000 中根據(jù)函數(shù)返回值形式的不同將用戶自定義函數(shù)分為三種類型：標(biāo)量型函數(shù)Scalar fun

59、ctions、內(nèi)嵌表值型函數(shù)Inline table-valued functions和多語(yǔ)句表值型函數(shù)Multi-statement table-valued functions。標(biāo)量型函數(shù)Scalar functions標(biāo)量型函數(shù)返回在 RETURNS 子句中定義的類型的單個(gè)數(shù)據(jù)值?？梢允褂盟袠?biāo)量數(shù)據(jù)類型，包括 bigint 和 sql_variant。不支持 timestamp 數(shù)據(jù)類型、用戶定義數(shù)據(jù)類型和非標(biāo)量類型如 table 或 cursor。在 BEGIN.END 塊中定義的函數(shù)主體包含返回該值的 Transact-SQL 語(yǔ)句系列。返回類型可以是除 text、ntext、im

60、age、cursor 、timestamp 和table 之外的任何數(shù)據(jù)類型。內(nèi)嵌表值型函數(shù)Inline table-valued functions內(nèi)嵌表值型函數(shù)以表的形式返回一個(gè)返回值，即它返回的是一個(gè)表。內(nèi)嵌表值型函數(shù)沒(méi)有由BEGIN-END 語(yǔ)句括起來(lái)的函數(shù)體。其返回的表由一個(gè)位于RETURN 子句中的SELECT 命令段從數(shù)據(jù)庫(kù)中篩選出來(lái)。內(nèi)聯(lián)表值型函數(shù)功能相當(dāng)于一個(gè)參數(shù)化的視圖。 多語(yǔ)句表值型函數(shù)Multi-statement table-valued functions多語(yǔ)句表值型函數(shù)可以看作標(biāo)量型和內(nèi)嵌表值型函數(shù)的結(jié)合體。它的返回值是一個(gè)表，但它和標(biāo)量型函數(shù)一樣有一個(gè)用BEGI