《數(shù)據(jù)庫(kù)原理及應(yīng)用》課件第10章

第10章觸發(fā)器10.1觸發(fā)器概述10.2DML觸發(fā)器10.3DDL觸發(fā)器10.4管理觸發(fā)器10.5觸發(fā)器的應(yīng)用

【技能目標(biāo)】

理解觸發(fā)器的功能；學(xué)會(huì)創(chuàng)建和管理觸發(fā)器；學(xué)會(huì)根據(jù)實(shí)際需要設(shè)計(jì)觸發(fā)器。

【知識(shí)目標(biāo)】

掌握觸發(fā)器的概念；掌握觸發(fā)器的分類；掌握inserted表和deleted表的用法；掌握創(chuàng)建、修改和刪除觸發(fā)器的方法；掌握查看觸發(fā)器的方法；掌握禁用或啟用觸發(fā)器的方法和步驟；掌握觸發(fā)器執(zhí)行順序設(shè)置的方法；掌握觸發(fā)器的應(yīng)用。

10.1.1觸發(fā)器的概念

觸發(fā)器是一種特殊類型的存儲(chǔ)過(guò)程。一般存儲(chǔ)過(guò)程通過(guò)使用存儲(chǔ)過(guò)程名稱即可直接調(diào)用，而觸發(fā)器不需使用EXEC命令調(diào)用，而是通過(guò)事件觸發(fā)被激活的。10.1觸?發(fā)?器?概?述觸發(fā)器的主要作用是能夠?qū)崿F(xiàn)復(fù)雜的完整性約束。當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)發(fā)生改變時(shí)，觸發(fā)器會(huì)自動(dòng)被激活，從而防止對(duì)數(shù)據(jù)的不正確修改。例如，在學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫(kù)SCMS中，當(dāng)從學(xué)生表中刪除一名同學(xué)的信息時(shí)，觸發(fā)器可以自動(dòng)修改學(xué)生選課表，將該同學(xué)的選課信息全部刪除。又如，為了防止數(shù)據(jù)表結(jié)構(gòu)更改或數(shù)據(jù)表被刪除，保護(hù)已經(jīng)建好的數(shù)據(jù)表，觸發(fā)器可以在接收到以DROP和ALTER開(kāi)頭的SQL語(yǔ)句時(shí)，不進(jìn)行對(duì)數(shù)據(jù)表的操作。

10.1.2觸發(fā)器的分類

在SQLServer2005中，包括兩大類觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。DDL觸發(fā)器是SQLServer2005中新增的，以前的版本只有DML觸發(fā)器。

1.?DML觸發(fā)器

當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言(DML)事件時(shí)DML觸發(fā)器將被激活。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT、UPDATE或DELETE操作。DML觸發(fā)器不僅可以查詢其他表，還可以包含復(fù)雜的Transact-SQL語(yǔ)句。DML觸發(fā)器有助于在表或視圖中修改數(shù)據(jù)時(shí)強(qiáng)制業(yè)務(wù)規(guī)則，擴(kuò)展數(shù)據(jù)的完整性。

DML觸發(fā)器分為三類，分別是AFTER觸發(fā)器、INSTEADOF觸發(fā)器和CLR觸發(fā)器。

(1)

AFTER觸發(fā)器。當(dāng)數(shù)據(jù)修改完成之后AFTER觸發(fā)器被激活。它主要用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯(cuò)誤，可以用ROLLBACK語(yǔ)句回滾操作?？梢詾楸淼耐徊僮鞫x多個(gè)觸發(fā)器。對(duì)于AFTER觸發(fā)器，可以定義哪一個(gè)觸發(fā)器被最先激活，哪一個(gè)被最后激活，通常調(diào)用系統(tǒng)存儲(chǔ)過(guò)程sp_settriggerorder來(lái)設(shè)置。

(2)

INSTEADOF觸發(fā)器。INSTEADOF觸發(fā)器會(huì)取代原來(lái)要進(jìn)行的操作，在記錄變更之前發(fā)生，它并不去執(zhí)行原來(lái)的操作(INSERT、UPDATE、DELETE)，而去執(zhí)行觸發(fā)器本身所定義的操作。既可在表上定義INSTEADOF觸發(fā)器，也可以在視圖上定義，但對(duì)同一操作只能定義一個(gè)INSTEADOF觸發(fā)器。

(3)

CLR觸發(fā)器。CLR觸發(fā)器不僅可以是AFTER觸發(fā)器或INSTEADOF觸發(fā)器，還可以是DDL觸發(fā)器。CLR觸發(fā)器將執(zhí)行在托管代碼(在

.NETFramework中創(chuàng)建并在SQLServer中上載的程序集的成員)中編寫的方法，而不用執(zhí)行Transact-SQL存儲(chǔ)過(guò)程。

2.?DDL觸發(fā)器

DDL觸發(fā)器在響應(yīng)數(shù)據(jù)定義語(yǔ)言(DDL)語(yǔ)句時(shí)被激活。它可以用于在數(shù)據(jù)庫(kù)中執(zhí)行管理任務(wù)，例如，審核以及規(guī)范數(shù)據(jù)庫(kù)操作。DDL觸發(fā)器會(huì)為響應(yīng)多種數(shù)據(jù)定義語(yǔ)言(DDL)語(yǔ)句而激活。這些語(yǔ)句主要是以CREATE、ALTER和DROP開(kāi)頭的語(yǔ)句。10.1.3插入表和刪除表

在SQLServer2005中，為DML觸發(fā)器定義了兩個(gè)特殊的表，一個(gè)是插入(inserted)表，另一個(gè)是刪除(deleted)表。這兩個(gè)表建在數(shù)據(jù)庫(kù)服務(wù)器內(nèi)存中，是由系統(tǒng)管理的邏輯表，而不是真正存儲(chǔ)在數(shù)據(jù)庫(kù)中的物理表。對(duì)于inserted表和deleted表，用戶只有讀取的權(quán)限，沒(méi)有修改的權(quán)限，這兩個(gè)表的結(jié)構(gòu)總是與觸發(fā)器所在數(shù)據(jù)表的結(jié)構(gòu)完全一致的。inserted表和deleted表是動(dòng)態(tài)駐留在內(nèi)存中的，當(dāng)觸發(fā)器的工作完成之后，它們也將從內(nèi)存中刪除。inserted表和deleted表的功能如表10-1所示。表10-1inserted表和deleted表的功能從表10-1中可以看出，對(duì)具有觸發(fā)器的表進(jìn)行INSERT、DELETE或者UPDATE操作時(shí)，過(guò)程分別如下。

(1)

INSERT操作。對(duì)一個(gè)定義了插入類型觸發(fā)器的表來(lái)講，一旦對(duì)該表執(zhí)行了插入操作，那么就將向該表插入的所有行復(fù)制到inserted表中。inserted表就是用來(lái)存儲(chǔ)向表中插入的記錄行的。

(2)

DELETE操作。對(duì)一個(gè)定義了刪除類型觸發(fā)器的表來(lái)講，一旦對(duì)該表執(zhí)行了刪除操作，則將刪除的所有行存放至deleted表中。deleted表就是用來(lái)存儲(chǔ)表中刪除的記錄行的。這樣做的目的是，一旦觸發(fā)器遇到了強(qiáng)迫它中止的語(yǔ)句被執(zhí)行時(shí)，刪除的那些行可以從deleted表中得以恢復(fù)。

(3)

UPDATE操作。更新操作分兩步完成，先將要更新的舊行刪除，然后向表中插入新的記錄。對(duì)一個(gè)定義了更新類型觸發(fā)器的表來(lái)講，執(zhí)行更新操作時(shí)，將刪除后的行轉(zhuǎn)移到deleted表中，插入到表中的新行被復(fù)制到inserted表中。

10.2.1創(chuàng)建DML觸發(fā)器

在SQLServer2005中可以使用CREATETRIGGER語(yǔ)句或使用SQLServerManagementStudio創(chuàng)建DML觸發(fā)器。10.2DML觸發(fā)器

1.使用CREATETRIGGER語(yǔ)句創(chuàng)建DML觸發(fā)器使用CREATETRIGGER語(yǔ)句創(chuàng)建觸發(fā)器時(shí)需要指定定義觸發(fā)器的基表、觸發(fā)器執(zhí)行的時(shí)間和觸發(fā)器被激活后執(zhí)行的所有指令。其語(yǔ)法格式如下：

CREATETRIGGERtrigger_name

ONtable_name|view_name

[WITHENCRYPTION]

{FOR|AFTER|INSTEADOF}

{[INSERT][,][UPDATE][,][DELETE]}

AS

[IFUPDATE(column_name)…n]

[{AND|OR}UPDATE(column_name)…n]

sql_statement參數(shù)說(shuō)明如下：

●

trigger_name：觸發(fā)器的名稱。該名稱必須遵循標(biāo)識(shí)符命名規(guī)則。

●

table_name|view_name：觸發(fā)器表名或視圖名。視圖只能被INSTEADOF觸發(fā)器

引用。

●

WITHENCRYPTION：對(duì)CREATETRIGGER語(yǔ)句的文本進(jìn)行加密。不能為CLR觸發(fā)器指定WITHENCRYPTION。

●

AFTER：指定DML觸發(fā)器僅在觸發(fā)SQL語(yǔ)句中指定的所有操作都已成功執(zhí)行時(shí)才被激活。所有的引用級(jí)聯(lián)操作和約束檢查也必須在激活此觸發(fā)器之前成功完成。如果僅指定FOR關(guān)鍵字，則AFTER為默認(rèn)值。不能對(duì)視圖定義AFTER觸發(fā)器。

●

INSTEADOF：指定DML觸發(fā)器是“代替”SQL語(yǔ)句執(zhí)行的，因此其優(yōu)先級(jí)高于觸發(fā)語(yǔ)句的操作。可以為表或視圖的每個(gè)INSERT、DELETE或UPDATE語(yǔ)句定義一個(gè)INSTEADOF觸發(fā)器。不能為DDL觸發(fā)器指定INSTEADOF觸發(fā)器?！?/p>

INSERT，UPDATE，DELETE：指定數(shù)據(jù)修改語(yǔ)句，這些語(yǔ)句可在DML觸發(fā)器對(duì)此表或視圖進(jìn)行嘗試時(shí)激活該觸發(fā)器。必須至少指定一個(gè)選項(xiàng)。在觸發(fā)器定義中允許使用上述選項(xiàng)的任意順序組合。如果指定的選項(xiàng)多于一個(gè)，需要用逗號(hào)分隔。

●

IFUPDATE：指定對(duì)表中的某一列或多列內(nèi)容修改時(shí)，觸發(fā)器才起作用。它可以指定多列，列名前可以不加表名。

●

sql_statement：定義觸發(fā)器被觸發(fā)后將執(zhí)行的數(shù)據(jù)庫(kù)操作。

說(shuō)明只有下列用戶才具有創(chuàng)建觸發(fā)器的權(quán)利：表的所有者、databaseowner(db_owner)角色成員、systemadministrators(sysadmin)角色成員。

1)

INSERT觸發(fā)器

INSERT觸發(fā)器在向創(chuàng)建觸發(fā)器的表進(jìn)行插入操作時(shí)被激活。對(duì)于AFTER觸發(fā)器，當(dāng)插入語(yǔ)句正確完成后，觸發(fā)器執(zhí)行；對(duì)于INSTEADOF觸發(fā)器，當(dāng)檢測(cè)到插入操作滿足插入條件后，觸發(fā)器操作“代替”SQL語(yǔ)句執(zhí)行。

【例10-1】創(chuàng)建一個(gè)觸發(fā)器，當(dāng)用戶試圖向student表中添加數(shù)據(jù)時(shí)，該觸發(fā)器向客戶端顯示一條消息。

分析：要求創(chuàng)建的觸發(fā)器是在用戶做INSERT操作時(shí)發(fā)揮作用，所以應(yīng)該創(chuàng)建的觸發(fā)器屬于INSERT類型觸發(fā)器。觸發(fā)器執(zhí)行時(shí)主要操作是向用戶顯示信息，可以用PRINT語(yǔ)句完成。下面創(chuàng)建兩個(gè)INSERT觸發(fā)器，一個(gè)為AFTER類型觸發(fā)器，另一個(gè)為INSTEADOF類型觸發(fā)器，比較兩個(gè)觸發(fā)器的區(qū)別。

(1)創(chuàng)建AFTER觸發(fā)器。在查詢編輯器窗口中輸入以下代碼：

CREATETRIGGERtr_insertFor --觸發(fā)器的名字為tr_insertFor

ONstudent --觸發(fā)器的基表是student

FORINSERT --觸發(fā)器為AFTER類型的INSERT觸發(fā)器

AS

PRINT'向?qū)W生表中插入了一條記錄！' --觸發(fā)器主要操作執(zhí)行代碼，tr_insertFor觸發(fā)器創(chuàng)建成功。

為了測(cè)試觸發(fā)器是否有效，向student表插入一條數(shù)據(jù)，語(yǔ)句如下：

INSERTINTOstudent(sno,sname,classno)

VALUES('200903102105','王萍萍','09031021')

執(zhí)行結(jié)果如圖10-1所示。

“消息”窗格中顯示了“向?qū)W生表中插入了一條記錄！”信息，說(shuō)明插入操作激活tr_insertFor觸發(fā)器，輸出了該字符串。查看student表，如圖10-2所示，剛才的插入操作已成功完成。

圖10-1AFTER觸發(fā)器示例

圖10-2查看王萍萍信息

結(jié)論：對(duì)于AFTER觸發(fā)器而言，要求引起觸發(fā)器激活的語(yǔ)句正確完成后該觸發(fā)器被激活。

(2)創(chuàng)建INSTEADOF觸發(fā)器。為了驗(yàn)證INSTEADOF觸發(fā)器，將前面建立的AFTER觸發(fā)器刪除。在查詢編輯器窗口中輸入以下代碼：

CREATETRIGGERtr_insertInstead

ONstudent

INSTEADOFINSERT

AS

PRINT'向?qū)W生表中插入了一條記錄！'

執(zhí)行代碼，tr_insertInstead觸發(fā)器創(chuàng)建成功。

為了測(cè)試觸發(fā)器是否有效，向student表插入一條數(shù)據(jù)，語(yǔ)句如下：

INSERTINTOstudent(sno,sname,classno)

VALUES('200903102107','李莉','09031021')

圖10-3INSTEADOF觸發(fā)器示例

“消息”窗格中顯示了“向?qū)W生表中插入了一條記錄！”字符串，說(shuō)明插入操作激活了tr_insertInstead觸發(fā)器，輸出了該字符串。查看student表，如圖10-4所示，并沒(méi)有李莉同學(xué)的相關(guān)數(shù)據(jù)。

圖10-4驗(yàn)證INSTEADOF觸發(fā)器

2)

DELETE觸發(fā)器

DELETE觸發(fā)器在對(duì)創(chuàng)建觸發(fā)器的表進(jìn)行刪除操作時(shí)被激活。

【例10-2】建立一個(gè)觸發(fā)器，當(dāng)從班級(jí)表中刪除某一班級(jí)時(shí)，學(xué)生表中對(duì)應(yīng)班級(jí)的所有學(xué)生信息也被刪除。

分析：刪除班級(jí)信息將其班級(jí)中的學(xué)生信息也刪除，這就是表的級(jí)聯(lián)更新。前面提到，觸發(fā)器工作時(shí)有兩個(gè)特殊的表，其中deleted表的功能就是存放被刪除的舊記錄。我們可以通過(guò)查看deleted表，得到用戶刪除的班級(jí)號(hào)，利用連接操作將student表中相應(yīng)班級(jí)的學(xué)生記錄刪除。在查詢編輯器窗口中輸入以下代碼：

CREATETRIGGERtr_deleteClass

ONclass

FORDELETE

AS

DELETEstudent

FROMstudent,deleted

WHEREstudent.classno=deleted.classno

執(zhí)行代碼，tr_deleteClass觸發(fā)器創(chuàng)建成功。

測(cè)試觸發(fā)器是否有效，將class表中的一條記錄刪除，如圖10-5所示。

圖10-5刪除09011012班信息

在刪除語(yǔ)句前后都加上了對(duì)student表的查詢，主要是為了對(duì)比觸發(fā)器激活前后表中數(shù)據(jù)的變化?？梢钥闯觯Y(jié)果中刪除了09011012班級(jí)記錄，相應(yīng)的學(xué)生記錄也被刪除了。

【例10-3】用戶可以使用DELETE觸發(fā)器，將從成績(jī)表中刪除的行存入一個(gè)命名為scoreBackup的表中。

分析：因?yàn)閿?shù)據(jù)庫(kù)中信息的刪除是不可恢復(fù)的，所以在刪除數(shù)據(jù)時(shí)可以對(duì)重要數(shù)據(jù)做備份。使用DELETE觸發(fā)器，在刪除數(shù)據(jù)的同時(shí)將數(shù)據(jù)備份到其他表中。

在查詢編輯器窗口中輸入以下代碼：

CREATETABLEscoreBackup--創(chuàng)建存放備份數(shù)據(jù)的表scoreBackup

(snochar(12)NOTNULL,cnochar(5)NOTNULL,resultintNULL)

GO

CREATETRIGGERtr_scoreBackup

ONsc

FORDELETE

AS

INSERTINTOscoreBackup

SELECT*FROMdeleted

執(zhí)行代碼，tr_scoreBackup觸發(fā)器創(chuàng)建成功。

為了測(cè)試觸發(fā)器是否有效，刪除sc表中所有課程號(hào)為03105的選課信息，如圖10-6所示。

在刪除語(yǔ)句后面利用SELECT查詢顯示scoreBackup表中的數(shù)據(jù)，可以看到sc表中刪除的滿足條件的記錄都存放在scoreBackup表中以備使用。

圖10-6查看scoreBackup表

3)

UPDATE觸發(fā)器

當(dāng)表做更新操作時(shí)，將激活UPDATE觸發(fā)器。UPDATE型觸發(fā)器有兩種類型：通常意義上的UPDATE型觸發(fā)器和用于檢查列改變的UPDATE型觸發(fā)器，這主要是因?yàn)楦虏僮骺梢陨婕皵?shù)據(jù)項(xiàng)。

(1)通常意義上的UPDATE型觸發(fā)器。使用通常意義上的UPDATE型觸發(fā)器，更新操作分兩步，先將需更新的內(nèi)容從表中刪除掉，然后插入新值。因此，更新型觸發(fā)器同時(shí)涉及deleted表和inserted表。

【例10-4】在sc表上創(chuàng)建UPDATE觸發(fā)器，當(dāng)對(duì)sc表做更新操作時(shí)為用戶給出提示信息。

CREATETRIGGERtr_update

ONsc

FORUPDATE

AS

PRINT'SC表中的數(shù)據(jù)發(fā)生了變化！'

執(zhí)行代碼，tr_update觸發(fā)器創(chuàng)建成功。

測(cè)試觸發(fā)器是否有效，將sc表中學(xué)號(hào)為200903102103的學(xué)生選修的課程號(hào)為03203的課程成績(jī)修改為65，如圖10-7所示。

圖10-7修改學(xué)生成績(jī)

執(zhí)行update語(yǔ)句后，激活了tr_update，在“消息”窗格中顯示“SC表中的數(shù)據(jù)發(fā)生了變化！”，并且sc表中數(shù)據(jù)做了更新操作。

(2)檢查列改變的UPDATE型觸發(fā)器。在有些更新中，需要判斷由指定一列或多列字段的更新激活觸發(fā)器，這時(shí)就要用到用于檢查列改變的更新型觸發(fā)器。它與通常意義上的觸發(fā)器不同之處主要表現(xiàn)在它包括以下保留字：

[IFUPDATE(column_name)…n]

[{AND|OR}UPDATE(column_name)…n]

【例10-5】用戶可以設(shè)定在sc表中只有成績(jī)列result被更新時(shí)，激活觸發(fā)器。

CREATETRIGGERtr_updateColumn

ONsc

FORUPDATE

AS

IFUPDATE(result)

PRINT'sc表中的成績(jī)列做了更新操作！'

執(zhí)行代碼，tr_updateColumn觸發(fā)器創(chuàng)建成功。

為了驗(yàn)證檢查列改變的更新型觸發(fā)器，將前面建立的UPDATE觸發(fā)器刪除或禁用。在查詢編輯器窗口中輸入以下代碼：

UPDATEsc

SETcno='03201'

WHEREsno='200903102103'

執(zhí)行結(jié)果如圖10-8所示。

圖10-8驗(yàn)證tr_updateColumn觸發(fā)器

結(jié)論：對(duì)sc表做了更新操作，并且更新操作成功執(zhí)行了，但這次更新tr_updateColumn觸發(fā)器沒(méi)有被激活，因?yàn)樵撚|發(fā)器的激活條件是更新sc表的result列，而本次更新操作更新的是cno列，不能激活觸發(fā)器。

2.使用SQLServerManagementStudio創(chuàng)建DML觸發(fā)器

【例10-6】為SCMS數(shù)據(jù)庫(kù)中的teacher表創(chuàng)建INSERT觸發(fā)器。

具體操作步驟如下：

(1)在“對(duì)象資源管理器”窗口中展開(kāi)“數(shù)據(jù)庫(kù)|SCMS|表”節(jié)點(diǎn)。

(2)選擇并展開(kāi)teacher節(jié)點(diǎn)，選擇“觸發(fā)器”節(jié)點(diǎn)。

(3)單擊右鍵，在彈出的快捷菜單中選擇“新建觸發(fā)器”選項(xiàng)，如圖10-9所示。

(4)在打開(kāi)的查詢編輯器窗口中編輯觸發(fā)器創(chuàng)建語(yǔ)句。

(5)單擊“執(zhí)行”按鈕。

圖10-9創(chuàng)建觸發(fā)器10.2.2修改DML觸發(fā)器

使用SQLServerManagementStudio或ALTERTRIGGER語(yǔ)句可以修改一個(gè)已經(jīng)存在的觸發(fā)器。SQLServer2005可以在保留現(xiàn)有觸發(fā)器名稱的同時(shí)，修改觸發(fā)器的觸發(fā)動(dòng)作和執(zhí)行內(nèi)容。

1.使用SQLServerManagementStudio修改DML觸發(fā)器

【例10-7】修改在例10-5中創(chuàng)建的觸發(fā)器tr_updateColumn，保證對(duì)sc表的成績(jī)列更新是在0到100之間。具體操作步驟如下：

(1)在“對(duì)象資源管理器”窗口中展開(kāi)“數(shù)據(jù)庫(kù)|SCMS|表”節(jié)點(diǎn)。

(2)選擇并展開(kāi)sc節(jié)點(diǎn)，選擇并展開(kāi)“觸發(fā)器”節(jié)點(diǎn)。

(3)選擇要修改的觸發(fā)器tr_updateColumn，單擊右鍵，選擇“修改”選項(xiàng)，如圖10-10所示。

(4)打開(kāi)查詢編輯器窗口，對(duì)代碼做修改，如圖10-11所示。單擊“執(zhí)行”按鈕，完成觸發(fā)器的修改。圖10-10修改觸發(fā)器

圖10-11修改觸發(fā)器定義代碼

2.使用ALTERTRIGGER語(yǔ)句修改DML觸發(fā)器

使用ALTERTRIGGER語(yǔ)句修改DML觸發(fā)器的語(yǔ)法格式如下：

ALTERTRIGGERtrigger_name

ONtable_name|view_name

[WITHENCRYPTION]

{FOR|AFTER|INSTEADOF}

{[INSERT][,][UPDATE][,][DELETE]}

AS

[IFUPDATE(column_name)…n]

[{AND|OR}UPDATE(column_name)…n]

sql_statement

其參數(shù)含義與創(chuàng)建觸發(fā)器語(yǔ)句相同。

以下是修改tr_updateColumn觸發(fā)器的代碼：

ALTERTRIGGERtr_updateColumn

ONsc

FORUPDATE

AS

IFUPDATE(result)

BEGIN

DECLARE@scoreint

SELECT@score=resultFROMinserted --從inserted表中找到修改后的成績(jī)

IF@score<0OR@score>100 --判斷成績(jī)是否在1到100之間

BEGIN

ROLLBACK --回滾修改操作

PRINT'成績(jī)必須在1到100之間！'

END

END10.2.3刪除DML觸發(fā)器

刪除一個(gè)觸發(fā)器時(shí)，該觸發(fā)器所關(guān)聯(lián)的表和數(shù)據(jù)不會(huì)受到任何影響。刪除觸發(fā)器所在的表時(shí)，SQLServer將會(huì)自動(dòng)刪除與該表相關(guān)的觸發(fā)器。刪除DML觸發(fā)器可以使用SQLServerManagementStudio，也可以使用DROPTRIGGER語(yǔ)句。

1.使用SQLServerManagementStudio刪除DML觸發(fā)器

【例10-8】刪除sc表的tr_updateColumn觸發(fā)器。

具體操作步驟如下：

(1)在“對(duì)象資源管理器”窗口中展開(kāi)“數(shù)據(jù)庫(kù)|SCMS|表”節(jié)點(diǎn)。

(2)選擇并展開(kāi)sc節(jié)點(diǎn)，選擇并展開(kāi)“觸發(fā)器”節(jié)點(diǎn)。

(3)選擇要?jiǎng)h除的觸發(fā)器tr_updateColumn，單擊右鍵，選擇“刪除”選項(xiàng)。

(4)打開(kāi)“刪除對(duì)象”對(duì)話框，單擊“確定”按鈕，完成tr_updateColumn觸發(fā)器的刪除。

2.使用DROPTRIGGER語(yǔ)句刪除DML觸發(fā)器

使用DROPTRIGGER語(yǔ)句刪除DML觸發(fā)器的語(yǔ)法格式如下：

DROPTRIGGERtrigger_name[,...n]

以下是刪除sc表的tr_updateColumn觸發(fā)器的語(yǔ)句：

DROPTRIGGERtr_updateColumn

10.3.1DDL觸發(fā)器概述

DDL觸發(fā)器像DML觸發(fā)器一樣，在響應(yīng)事件時(shí)執(zhí)行觸發(fā)器相應(yīng)語(yǔ)句。但與DML觸發(fā)器不同的是，它們并不響應(yīng)對(duì)表或視圖的UPDATE、INSERT或DELETE操作，它們主要響應(yīng)數(shù)據(jù)定義語(yǔ)句(DDL)。這些語(yǔ)句包括CREATE、ALTER、DROP、GRANT、DENY及REVOKE等。執(zhí)行DDL語(yǔ)句操作的系統(tǒng)存儲(chǔ)過(guò)程也可以激活DDL觸發(fā)器。10.3DDL觸發(fā)器

1.?DDL觸發(fā)器的作用

DDL觸發(fā)器的作用表現(xiàn)在以下幾點(diǎn)：

(1)防止對(duì)數(shù)據(jù)庫(kù)架構(gòu)進(jìn)行某些更改。

(2)響應(yīng)數(shù)據(jù)庫(kù)架構(gòu)中的更改。

(3)記錄數(shù)據(jù)庫(kù)架構(gòu)中的更改或事件。

2.?DDL觸發(fā)器的作用域

DDL觸發(fā)器的作用域包括數(shù)據(jù)庫(kù)范圍和服務(wù)器范圍。

(1)數(shù)據(jù)庫(kù)范圍。數(shù)據(jù)庫(kù)范圍內(nèi)的DDL觸發(fā)器都作為對(duì)象存儲(chǔ)在創(chuàng)建它們的數(shù)據(jù)庫(kù)中。

(2)服務(wù)器范圍。服務(wù)器范圍內(nèi)的DDL觸發(fā)器作為對(duì)象存儲(chǔ)在master數(shù)據(jù)庫(kù)中。

例如，當(dāng)數(shù)據(jù)庫(kù)中發(fā)生CREATE_TABLE事件時(shí)，都會(huì)激活為響應(yīng)CREATE_TABLE事件創(chuàng)建的數(shù)據(jù)庫(kù)范圍的DDL觸發(fā)器。每當(dāng)服務(wù)器上發(fā)生CREATE_INDEX事件時(shí)，都會(huì)激活為響應(yīng)CREATE_INDEX事件創(chuàng)建的服務(wù)器范圍的DDL觸發(fā)器。

3.與DML觸發(fā)器的不同之處

DDL觸發(fā)器與DML觸發(fā)器的不同之處表現(xiàn)在以下幾點(diǎn)：

(1)

DML觸發(fā)器響應(yīng)INSERT、UPDATE和DELETE語(yǔ)句操作。

(2)

DDL觸發(fā)器響應(yīng)CREATE、ALTER、DROP和其他DDL語(yǔ)句操作。

(3)

只有在完成Transact-SQL語(yǔ)句后才運(yùn)行DDL觸發(fā)器。DDL觸發(fā)器無(wú)法作為INSTEADOF觸發(fā)器使用。

(4)

DDL觸發(fā)器不會(huì)創(chuàng)建inserted表和deleted表。但是可以使用EVENTDATA函數(shù)捕獲有關(guān)信息。10.3.2創(chuàng)建DDL觸發(fā)器

DDL觸發(fā)器只能使用CREATETRIGGER語(yǔ)句來(lái)創(chuàng)建。語(yǔ)法格式如下：

CREATETRIGGERtrigger_name

ON{ALLSERVER|DATABASE}

[WITHENCRYPTION]

{FOR|AFTER}{event_type}[,...n]

AS

sql_statement

參數(shù)說(shuō)明如下：●

trigger_name：觸發(fā)器的名稱。

●

ALLSERVER：將DDL觸發(fā)器的作用域應(yīng)用于當(dāng)前服務(wù)器。如果指定了此參數(shù)，則只要當(dāng)前服務(wù)器中的任何位置上出現(xiàn)event_type，就會(huì)激活該觸發(fā)器。

●

DATABASE：將DDL觸發(fā)器的作用域應(yīng)用于當(dāng)前數(shù)據(jù)庫(kù)。如果指定了此參數(shù)，則只要當(dāng)前數(shù)據(jù)庫(kù)中出現(xiàn)event_type，就會(huì)激活該觸發(fā)器?！?/p>

event_type：用于激活DDL觸發(fā)器的DDL事件。每個(gè)事件都對(duì)應(yīng)一個(gè)Transact-SQL語(yǔ)句。DDL事件名稱是由SQL語(yǔ)句中的關(guān)鍵字以及在這些關(guān)鍵字之間所加的下劃線(_)構(gòu)成的。例如，刪除表事件為DROP_TABLE，修改表事件為ALTER_TABLE，修改索引事件為ALTER_INDEX，刪除索引事件為DROP_INDEX。

●

sql_statement：當(dāng)觸發(fā)器被觸發(fā)后執(zhí)行的語(yǔ)句。

【例10-9】創(chuàng)建觸發(fā)器，禁止用戶刪除SCMS學(xué)生數(shù)據(jù)庫(kù)中的表。

CREATETRIGGERtr_denyDropTable

ONDATABASE

FORDROP_TABLE

AS

PRINT'禁止刪除數(shù)據(jù)表！'

ROLLBACK

BEGINTRAN

GO執(zhí)行代碼，tr_denyDropTable觸發(fā)器創(chuàng)建成功。

測(cè)試觸發(fā)器是否有效，將SCMS數(shù)據(jù)庫(kù)中的scoreBackup表刪除，執(zhí)行結(jié)果如圖10-12所示。

圖10-12刪除scoreBackup表

圖10-13刪除WZGL中的price_table表

結(jié)論：tr_denyDropTable觸發(fā)器是一個(gè)數(shù)據(jù)庫(kù)范圍的觸發(fā)器，與前面的DML觸發(fā)器不同，該觸發(fā)器是由用戶的DROPTABLE操作激活的。該觸發(fā)器的作用域是SCMS數(shù)據(jù)庫(kù)，在SCMS數(shù)據(jù)庫(kù)中刪除數(shù)據(jù)表時(shí)激活。在WZGL數(shù)據(jù)庫(kù)中進(jìn)行表的刪除時(shí)不會(huì)激活此觸發(fā)器。

在“對(duì)象資源管理器”窗口中，可在“數(shù)據(jù)庫(kù)|SCMS|可編程性|數(shù)據(jù)庫(kù)觸發(fā)器”節(jié)點(diǎn)處查看SCMS數(shù)據(jù)庫(kù)中創(chuàng)建的數(shù)據(jù)庫(kù)觸發(fā)器。

【例10-10】創(chuàng)建一個(gè)觸發(fā)器，禁止用戶在當(dāng)前服務(wù)器中創(chuàng)建、修改或刪除數(shù)據(jù)庫(kù)。

CREATETRIGGERtr_denyOperate

ONALLSERVER

FORCREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE

AS

PRINT'不能在當(dāng)前服務(wù)器中新建、修改和刪除數(shù)據(jù)庫(kù)！'

ROLLBACK

BEGINTRAN

GO

執(zhí)行代碼，tr_denyOperate觸發(fā)器創(chuàng)建成功。

測(cè)試觸發(fā)器是否有效，新建MyDB數(shù)據(jù)庫(kù)，執(zhí)行結(jié)果如圖10-14所示。

圖10-14創(chuàng)建MyDB數(shù)據(jù)庫(kù)

結(jié)論：tr_denyOperate觸發(fā)器是服務(wù)器范圍觸發(fā)器，該觸發(fā)器可由創(chuàng)建數(shù)據(jù)庫(kù)、修改數(shù)據(jù)庫(kù)或刪除數(shù)據(jù)庫(kù)操作激活，該觸發(fā)器的作用域是整個(gè)服務(wù)器。

在“對(duì)象資源管理器”窗口中的“服務(wù)器對(duì)象|觸發(fā)器”節(jié)點(diǎn)處查看創(chuàng)建的服務(wù)器DDL觸發(fā)器。10.3.3修改DDL觸發(fā)器

在SQLServer中可以使用ALTERTRIGGER語(yǔ)句修改DDL觸發(fā)器。DDL觸發(fā)器的修改和DML觸發(fā)器相似。

【例10-11】修改tr_denyDropTable觸發(fā)器，要求禁止用戶在SCMS數(shù)據(jù)庫(kù)中刪除或者新建表。

ALTERTRIGGERtr_denyDropTable

ONDATABASE

FORDROP_TABLE,CREATE_TABLE

AS

PRINT'不能修改數(shù)據(jù)庫(kù)的結(jié)構(gòu)?。?

ROLLBACK

BEGINTRAN

GO

執(zhí)行代碼，tr_denyDropTable觸發(fā)器修改成功。10.3.4刪除DDL觸發(fā)器

在SQLServer中可以使用SQLServerManagementStudio或DROPTRIGGER語(yǔ)句刪除DDL觸發(fā)器。

1.使用SQLServerManagementStudio刪除DDL觸發(fā)器

【例10-12】刪除例10-9創(chuàng)建的作用域?yàn)閿?shù)據(jù)庫(kù)的觸發(fā)器tr_denyDropTable。

具體操作步驟如下：

(1)在“對(duì)象資源管理器”窗口中展開(kāi)“數(shù)據(jù)庫(kù)|SCMS|可編程性”節(jié)點(diǎn)。

(2)選擇并展開(kāi)“數(shù)據(jù)庫(kù)觸發(fā)器”節(jié)點(diǎn)，其中列出了所創(chuàng)建的數(shù)據(jù)庫(kù)觸發(fā)器。

(3)選擇tr_denyDropTable，單擊右鍵，選擇“刪除”選項(xiàng)，如圖10-15所示。

(4)打開(kāi)“刪除對(duì)象”對(duì)話框，單擊“確定”按鈕即可刪除tr_denyDropTable觸發(fā)器。

【例10-13】刪除例10-10創(chuàng)建的作用域?yàn)檎麄€(gè)服務(wù)器的觸發(fā)器tr_denyOperate。

具體操作步驟如下：

(1)在“對(duì)象資源管理器”窗口中，選擇并展開(kāi)“服務(wù)器對(duì)象|觸發(fā)器”節(jié)點(diǎn)，列出了所創(chuàng)建的服務(wù)器觸發(fā)器。

(2)選擇tr_denyOperate，單擊右鍵，選擇“刪除”選項(xiàng)，如圖10-16所示。

(3)打開(kāi)“刪除對(duì)象”對(duì)話框，單擊“確定”按鈕即可刪除tr_denyOperate觸發(fā)器。

圖10-15刪除數(shù)據(jù)庫(kù)觸發(fā)器圖10-16刪除服務(wù)器觸發(fā)器

2．使用DROPTRIGGER語(yǔ)句刪除DDL觸發(fā)器

使用DROPTRIGGER語(yǔ)句刪除DDL觸發(fā)器的語(yǔ)法格式如下：

DROPTRIGGERtrigger_name[,...n]ON{DATABASE|ALLSERVER}

其參數(shù)含義與創(chuàng)建DDL觸發(fā)器相同。

刪除tr_denyOperate觸發(fā)器語(yǔ)句如下：

DROPTRIGGERtr_denyOperateONALLSERVER

10.4.1查看觸發(fā)器

調(diào)用sp_helptrigger、sp_help、sp_helptext和sp_depends可分別查看觸發(fā)器的不同信息。

1.調(diào)用sp_helptrigger顯示DML觸發(fā)器信息

調(diào)用sp_helptrigger顯示DML觸發(fā)器信息的語(yǔ)法格式如下：

sp_helptrigger'table'[,'type']10.4管?理?觸?發(fā)?器參數(shù)說(shuō)明如下：

●

table：要查看觸發(fā)器的表的名稱

●

type：要查看的觸發(fā)器類型。其值可以為INSERT、UPDATE和DELETE。該選項(xiàng)為可選項(xiàng)。

其中，sp_helptrigger不能用于DDL觸發(fā)器。

【例10-14】查看student表的觸發(fā)器信息。

sp_helptriggerstudent

執(zhí)行結(jié)果如圖10-17所示。

圖10-17調(diào)用sp_helptrigger查看觸發(fā)器

2.調(diào)用sp_help顯示觸發(fā)器信息

調(diào)用sp_help顯示觸發(fā)器信息的語(yǔ)法格式如下：

sp_helptrigger_name

【例10-15】查看tr_updateColumn觸發(fā)器的所有者和創(chuàng)建時(shí)間。

sp_helptr_updateColumn

執(zhí)行結(jié)果如圖10-18所示。

圖10-18調(diào)用sp_help查看觸發(fā)器

3.調(diào)用sp_helptext顯示觸發(fā)器的源代碼

調(diào)用sp_helptext顯示觸發(fā)器的源代碼的語(yǔ)法格式如下：

sp_helptexttrigger_name

【例10-16】查看tr_update觸發(fā)器的創(chuàng)建語(yǔ)句。

sp_helptexttr_update

執(zhí)行結(jié)果如圖10-19所示。

圖10-19調(diào)用sp_helptext查看觸發(fā)器

4.調(diào)用sp_depends顯示觸發(fā)器參考的對(duì)象信息

調(diào)用sp_depends顯示觸發(fā)器參考的對(duì)象信息的語(yǔ)法格式如下：

sp_dependstrigger_name

【例10-17】查看tr_updateColumn觸發(fā)器參考的對(duì)象信息。

sp_dependstr_updateColumn

執(zhí)行結(jié)果如圖10-20所示。

圖10-20調(diào)用sp_depends查看觸發(fā)器10.4.2禁用或啟用觸發(fā)器

默認(rèn)情況下，創(chuàng)建觸發(fā)器后會(huì)啟用觸發(fā)器。當(dāng)不需要觸發(fā)器工作時(shí)，可以刪除或禁用觸發(fā)器。禁用觸發(fā)器不會(huì)刪除該觸發(fā)器，該觸發(fā)器仍然作為對(duì)象存在于當(dāng)前數(shù)據(jù)庫(kù)中。但是，當(dāng)執(zhí)行引起觸發(fā)器執(zhí)行的相關(guān)Transact-SQL語(yǔ)句時(shí)，不會(huì)激活此觸發(fā)器；可以使用SQLServerManagementStudio禁用觸發(fā)器，也可使用DISABLETRIGGER語(yǔ)句禁用觸發(fā)器；可以使用SQLServerManagementStudio重新啟用被禁用的觸發(fā)器，也可使用ENABLETRIGGER重新啟用被禁用的觸發(fā)器。

1.禁用觸發(fā)器

1)使用SQLServerManagementStudio禁用觸發(fā)器

【例10-18】將student表中的tr_insertInstead觸發(fā)器禁用。

具體操作步驟如下：

(1)在“對(duì)象資源管理器”窗口中展開(kāi)“數(shù)據(jù)庫(kù)|SCMS|表”節(jié)點(diǎn)。

(2)選擇并展開(kāi)student節(jié)點(diǎn)，選擇并展開(kāi)“觸發(fā)器”節(jié)點(diǎn)。

(3)選擇tr_insertInstead觸發(fā)器，單擊右鍵，在彈出的快捷菜單中選擇“禁用”選項(xiàng)，如圖10-21所示。

(4)彈出“禁用觸發(fā)器”消息框，單擊“關(guān)閉”按鈕。tr_insertInstead觸發(fā)器被禁用。圖10-21禁用觸發(fā)器

2)使用DISABLETRIGGER語(yǔ)句禁用觸發(fā)器

使用DISABLETRIGGER語(yǔ)句禁用觸發(fā)器的語(yǔ)法格式如下：

DISABLETRIGGER{trigger_name[,...n]|ALL}

ON{object_name|DATABASE|ALLSERVER}

參數(shù)說(shuō)明如下：

●

trigger_name：要禁用的觸發(fā)器的名稱。

●

ALL：指示禁用在ON子句作用域中定義的所有觸發(fā)器?！?/p>

object_name：禁用的DML觸發(fā)器所屬的表或視圖的名稱。

●

DATABASE：對(duì)于DDL觸發(fā)器，禁用的觸發(fā)器范圍是數(shù)據(jù)庫(kù)范圍。

●

ALLSERVER：對(duì)于DDL觸發(fā)器，禁用的觸發(fā)器范圍是整個(gè)服務(wù)器范圍。

禁用student表中的tr_insertInstead觸發(fā)器代碼如下：

DISABLETRIGGERtr_insertInsteadonstudent

【例10-19】將創(chuàng)建在SCMS數(shù)據(jù)庫(kù)中的DDL觸發(fā)器tr_denyDropTable禁用。

DISABLETRIGGERtr_denyDropTableON

DATABASE

2.啟用觸發(fā)器

啟用觸發(fā)器并不是要重新創(chuàng)建觸發(fā)器。雖然禁用的觸發(fā)器仍以對(duì)象形式存在于當(dāng)前數(shù)據(jù)庫(kù)中，但并不激活。啟用觸發(fā)器將導(dǎo)致它在其最初編程所在的任何Transact-SQL語(yǔ)句執(zhí)行時(shí)被激活。

1)使用SQLServerManagementStudio啟用被禁用的觸發(fā)器

具體操作步驟和禁用觸發(fā)器相似。只不過(guò)當(dāng)選定觸發(fā)器并單擊右鍵后，在彈出的快捷菜單中選擇“啟用”選項(xiàng)。

2)使用ENABLETRIGGER語(yǔ)句啟用被禁用的觸發(fā)器

使用ENABLETRIGGER語(yǔ)句啟用被禁用的觸發(fā)器的語(yǔ)法格式如下：

ENABLETRIGGER{trigger_name[,...n]|ALL}

ON{object_name|DATABASE|ALLSERVER}

其參數(shù)含義與禁用觸發(fā)器語(yǔ)法相同。

【例10-20】將禁用的tr_insertInstead觸發(fā)器啟用。

ENABLETRIGGERtr_insertInsteadONstudent

【例10-21】將禁用的tr_denyDropTable觸發(fā)器啟用。

ENABLETRIGGERtr_denyDropTableON

DATABASE10.4.3設(shè)置觸發(fā)器的執(zhí)行順序

用戶可以針對(duì)一張表創(chuàng)建多個(gè)觸發(fā)器，通過(guò)調(diào)用sp_settriggerorder存儲(chǔ)過(guò)程指定第一個(gè)被激活和最后一個(gè)被激活的AFTER觸發(fā)器(DML或DDL)。其語(yǔ)法格式如下：

sp_settriggerordertrigger_name,'value','statement_type'

[,{'DATABASE'|'SERVER'|NULL}]

參數(shù)說(shuō)明如下：

●

trigger_name：要設(shè)置或更改其順序的DML或DDL觸發(fā)器的名稱。

●

value：觸發(fā)器的新順序的設(shè)置?？梢允荈irst、Last和Null值中的任意一個(gè)值。

●

statement_type：指定激活觸發(fā)器的SQL語(yǔ)句?？梢允荌NSERT、UPDATE、DELETE語(yǔ)句或用于DDL觸發(fā)器的DDL事件中列出的任何Transact-SQL語(yǔ)句事件。不能指定事件組。

●

'DATABASE'|'SERVER'|NULL：如果trigger_name是DDL觸發(fā)器，則指定trigger_name創(chuàng)建時(shí)的作用域是數(shù)據(jù)庫(kù)作用域還是服務(wù)器作用域。如果未指定或指定為NULL，則trigger_name為DML觸發(fā)器。

【例10-22】設(shè)置tr_scoreBackup第一個(gè)被執(zhí)行。

sp_settriggerorder'tr_scoreBackup',First,'DELETE'

【例10-23】在SCMS數(shù)據(jù)庫(kù)中，為了維護(hù)數(shù)據(jù)庫(kù)中數(shù)據(jù)的完整性，當(dāng)用戶從student學(xué)生表中刪除一行數(shù)據(jù)時(shí)，sc成績(jī)表中對(duì)應(yīng)學(xué)生的選課信息也被刪除。10.5觸發(fā)器的應(yīng)用

分析：根據(jù)要求可以使用約束或觸發(fā)器來(lái)實(shí)現(xiàn)，本例中選用觸發(fā)器來(lái)實(shí)現(xiàn)。為student表創(chuàng)建一個(gè)DELETE觸發(fā)器，SQLServer會(huì)為觸發(fā)器建立一個(gè)臨時(shí)表deleted。對(duì)于student表中被刪除的數(shù)據(jù)，系統(tǒng)會(huì)將其存放在deleted表中，通過(guò)查看該表得到刪除學(xué)生的學(xué)號(hào)，再將該學(xué)號(hào)對(duì)應(yīng)的sc表中的記錄刪除。下面使用AFTER觸發(fā)器和INSTEADOF觸發(fā)器完成相同的操作，以加深對(duì)AFTER觸發(fā)器和INSTEADOF觸發(fā)器的理解。

(1)使用AFTER觸發(fā)器。在查詢編輯器窗口中輸入以下代碼：

CREATETRIGGERtr_delestu

ONstudent

FORDELETE

AS

DECLARE@snochar(12),@snamechar(10)

SELECT@sno=sno,@sname=snameFROMdeleted

--被刪除的學(xué)生信息存放在deleted表中，所以從該表中讀取學(xué)生的學(xué)號(hào)、姓名

PRINT'在student表中刪除的是'+@sname+'的信息！'

--顯示刪除學(xué)生信息

DELETEFROMscWHEREsno=@sno

--將該學(xué)生的選課信息從sc表中刪除

執(zhí)行代碼，tr_delestu觸發(fā)器創(chuàng)建成功。

激活觸發(fā)器，在student表上刪除一條學(xué)生記錄，代碼如下：

DELETEFROMstudentWHEREsno='200903102103'

執(zhí)行結(jié)果如圖10-22所示。

圖10-22刪除student表中的一條記錄其中，為了測(cè)試觸發(fā)器的效果，首先查詢sc表學(xué)號(hào)為200903102103的學(xué)生的選課信息，查詢結(jié)果顯