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

【技能目標】

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

【知識目標】

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

10.1.1觸發(fā)器的概念

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

10.1.2觸發(fā)器的分類

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

1.?DML觸發(fā)器

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

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

(1)

AFTER觸發(fā)器。當數(shù)據(jù)修改完成之后AFTER觸發(fā)器被激活。它主要用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯誤，可以用ROLLBACK語句回滾操作。可以為表的同一操作定義多個觸發(fā)器。對于AFTER觸發(fā)器，可以定義哪一個觸發(fā)器被最先激活，哪一個被最后激活，通常調用系統(tǒng)存儲過程sp_settriggerorder來設置。

(2)

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

(3)

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

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

2.?DDL觸發(fā)器

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

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

(1)

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

(2)

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

(3)

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

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

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

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

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ù)說明如下：

●

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

●

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

引用。

●

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

●

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

●

INSTEADOF：指定DML觸發(fā)器是“代替”SQL語句執(zhí)行的，因此其優(yōu)先級高于觸發(fā)語句的操作?？梢詾楸砘蛞晥D的每個INSERT、DELETE或UPDATE語句定義一個INSTEADOF觸發(fā)器。不能為DDL觸發(fā)器指定INSTEADOF觸發(fā)器?！?/p>

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

●

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

●

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

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

1)

INSERT觸發(fā)器

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

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

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

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

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

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

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

AS

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

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

INSERTINTOstudent(sno,sname,classno)

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

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

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

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

圖10-2查看王萍萍信息

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

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

CREATETRIGGERtr_insertInstead

ONstudent

INSTEADOFINSERT

AS

PRINT'向學生表中插入了一條記錄！'

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

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

INSERTINTOstudent(sno,sname,classno)

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

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

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

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

2)

DELETE觸發(fā)器

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

【例10-2】建立一個觸發(fā)器，當從班級表中刪除某一班級時，學生表中對應班級的所有學生信息也被刪除。

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

CREATETRIGGERtr_deleteClass

ONclass

FORDELETE

AS

DELETEstudent

FROMstudent,deleted

WHEREstudent.classno=deleted.classno

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

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

圖10-5刪除09011012班信息

在刪除語句前后都加上了對student表的查詢，主要是為了對比觸發(fā)器激活前后表中數(shù)據(jù)的變化。可以看出，結果中刪除了09011012班級記錄，相應的學生記錄也被刪除了。

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

分析：因為數(shù)據(jù)庫中信息的刪除是不可恢復的，所以在刪除數(shù)據(jù)時可以對重要數(shù)據(jù)做備份。使用DELETE觸發(fā)器，在刪除數(shù)據(jù)的同時將數(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)建成功。

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

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

圖10-6查看scoreBackup表

3)

UPDATE觸發(fā)器

當表做更新操作時，將激活UPDATE觸發(fā)器。UPDATE型觸發(fā)器有兩種類型：通常意義上的UPDATE型觸發(fā)器和用于檢查列改變的UPDATE型觸發(fā)器，這主要是因為更新操作可以涉及數(shù)據(jù)項。

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

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

CREATETRIGGERtr_update

ONsc

FORUPDATE

AS

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

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

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

圖10-7修改學生成績

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

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

[IFUPDATE(column_name)…n]

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

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

CREATETRIGGERtr_updateColumn

ONsc

FORUPDATE

AS

IFUPDATE(result)

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

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

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

UPDATEsc

SETcno='03201'

WHEREsno='200903102103'

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

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

結論：對sc表做了更新操作，并且更新操作成功執(zhí)行了，但這次更新tr_updateColumn觸發(fā)器沒有被激活，因為該觸發(fā)器的激活條件是更新sc表的result列，而本次更新操作更新的是cno列，不能激活觸發(fā)器。

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

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

具體操作步驟如下：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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ā)器語句相同。

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

ALTERTRIGGERtr_updateColumn

ONsc

FORUPDATE

AS

IFUPDATE(result)

BEGIN

DECLARE@scoreint

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

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

BEGIN

ROLLBACK --回滾修改操作

PRINT'成績必須在1到100之間！'

END

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

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

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

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

具體操作步驟如下：

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

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

(3)選擇要刪除的觸發(fā)器tr_updateColumn，單擊右鍵，選擇“刪除”選項。

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

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

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

DROPTRIGGERtrigger_name[,...n]

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

DROPTRIGGERtr_updateColumn

10.3.1DDL觸發(fā)器概述

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

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

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

(1)防止對數(shù)據(jù)庫架構進行某些更改。

(2)響應數(shù)據(jù)庫架構中的更改。

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

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

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

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

(2)服務器范圍。服務器范圍內的DDL觸發(fā)器作為對象存儲在master數(shù)據(jù)庫中。

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

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

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

(1)

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

(2)

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

(3)

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

(4)

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

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

CREATETRIGGERtrigger_name

ON{ALLSERVER|DATABASE}

[WITHENCRYPTION]

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

AS

sql_statement

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

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

●

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

●

DATABASE：將DDL觸發(fā)器的作用域應用于當前數(shù)據(jù)庫。如果指定了此參數(shù)，則只要當前數(shù)據(jù)庫中出現(xiàn)event_type，就會激活該觸發(fā)器。●

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

●

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

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

CREATETRIGGERtr_denyDropTable

ONDATABASE

FORDROP_TABLE

AS

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

ROLLBACK

BEGINTRAN

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

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

圖10-12刪除scoreBackup表

圖10-13刪除WZGL中的price_table表

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

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

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

CREATETRIGGERtr_denyOperate

ONALLSERVER

FORCREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE

AS

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

ROLLBACK

BEGINTRAN

GO

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

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

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

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

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

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

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

ALTERTRIGGERtr_denyDropTable

ONDATABASE

FORDROP_TABLE,CREATE_TABLE

AS

PRINT'不能修改數(shù)據(jù)庫的結構??！'

ROLLBACK

BEGINTRAN

GO

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

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

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

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

具體操作步驟如下：

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

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

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

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

【例10-13】刪除例10-10創(chuàng)建的作用域為整個服務器的觸發(fā)器tr_denyOperate。

具體操作步驟如下：

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

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

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

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

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

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

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

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

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

DROPTRIGGERtr_denyOperateONALLSERVER

10.4.1查看觸發(fā)器

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

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

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

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

●

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

●

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

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

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

sp_helptriggerstudent

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

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

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

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

sp_helptrigger_name

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

sp_helptr_updateColumn

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

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

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

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

sp_helptexttrigger_name

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

sp_helptexttr_update

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

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

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

調用sp_depends顯示觸發(fā)器參考的對象信息的語法格式如下：

sp_dependstrigger_name

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

sp_dependstr_updateColumn

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

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

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

1.禁用觸發(fā)器

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

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

具體操作步驟如下：

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

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

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

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

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

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

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

ON{object_name|DATABASE|ALLSERVER}

參數(shù)說明如下：

●

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

●

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

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

●

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

●

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

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

DISABLETRIGGERtr_insertInsteadonstudent

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

DISABLETRIGGERtr_denyDropTableON

DATABASE

2.啟用觸發(fā)器

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

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

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

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

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

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

ON{object_name|DATABASE|ALLSERVER}

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

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

ENABLETRIGGERtr_insertInsteadONstudent

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

ENABLETRIGGERtr_denyDropTableON

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

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

sp_settriggerordertrigger_name,'value','statement_type'

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

參數(shù)說明如下：

●

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

●

value：觸發(fā)器的新順序的設置。可以是First、Last和Null值中的任意一個值。

●

statement_type：指定激活觸發(fā)器的SQL語句。可以是INSERT、UPDATE、DELETE語句或用于DDL觸發(fā)器的DDL事件中列出的任何Transact-SQL語句事件。不能指定事件組。

●

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

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

sp_settriggerorder'tr_scoreBackup',First,'DELETE'

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

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

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

CREATETRIGGERtr_delestu

ONstudent

FORDELETE

AS

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

SELECT@sno=sno,@sname=snameFROMdeleted

--被刪除的學生信息存放在deleted表中，所以從該表中讀取學生的學號、姓名

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

--顯示刪除學生信息

DELETEFROMscWHEREsno=@sno

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

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

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

DELETEFROMstudentWHEREsno='200903102103'

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

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