觸發(fā)器實際上就是一種特殊類型的存儲過程，其特殊性表現(xiàn)在：它是在執(zhí)行某些特定的T-SQL語句時自動的。
11.1 觸發(fā)器簡介
觸發(fā)器實際上就是一種特殊類型的存儲過程，它是在執(zhí)行某些特定的T-SQL語句時自動執(zhí)行的一種存儲過程。在SQL Server 2005中，根據(jù)SQL語句的不同，把觸發(fā)器分為兩類：一類是DML觸發(fā)器，一類是DLL觸發(fā)器。
11.1.1 觸發(fā)器的概念和作用
在SQL Server 2005里，可以用兩種方法來保證數(shù)據(jù)的有效性和完整性：約束（check）和觸發(fā)器（Trigger）。約束是直接設(shè)置于數(shù)據(jù)表內(nèi)，只能現(xiàn)實一些比較簡 單的功能操作，如：實現(xiàn)字段有效性和唯一性的檢查、自動填入默認(rèn)值、確保字段數(shù)據(jù)不重復(fù)（即主鍵）、確保數(shù)據(jù)表對應(yīng)的完整性（即外鍵）等功能。
觸發(fā)器是針對數(shù)據(jù)表（庫）的特殊的存儲過程，當(dāng)這個表發(fā)生了 Insert、Update或Delete操作時，會自動激活執(zhí)行的，可以處理各種復(fù)雜的操作。在SQL Server 2005中，觸發(fā)器有了更進一步的功能，在數(shù)據(jù)表（庫）發(fā)生Create、Alter和Drop操作時，也會自動激活執(zhí)行。
觸發(fā)器常用的一些功能如下：
l 完成比約束更復(fù)雜的數(shù)據(jù)約束：觸發(fā)器可以實現(xiàn)比約束更為復(fù)雜的數(shù)據(jù)約束
l 檢查所做的SQL是否允許：觸發(fā)器可以檢查SQL所做的操作是否被允許。例如：在產(chǎn)品庫存表里，如果要刪除一條產(chǎn)品記錄，在刪除記錄時，觸發(fā)器可以檢查該產(chǎn)品庫存數(shù)量是否為零，如果不為零則取消該刪除操作。
l 修改其它數(shù)據(jù)表里的數(shù)據(jù)：當(dāng)一個SQL語句對數(shù)據(jù)表進行操作的時候，觸發(fā)器可以根據(jù)該SQL語句的操作情況來對另一個數(shù)據(jù)表進行操作。例如：一個訂單取消的時候，那么觸發(fā)器可以自動修改產(chǎn)品庫存表，在訂購量的字段上減去被取消訂單的訂購數(shù)量。
l 調(diào)用更多的存儲過程：約束的本身是不能調(diào)用存儲過程的，但是觸發(fā)器本身就是一種存儲過程，而存儲過程是可以嵌套使用的，所以觸發(fā)器也可以調(diào)用一個或多過存儲過程。
l 發(fā)送SQL Mail：在SQL語句執(zhí)行完之后，觸發(fā)器可以判斷更改過的記錄是否達(dá)到一定條件，如果達(dá)到這個條件的話，觸發(fā)器可以自動調(diào)用SQL Mail來發(fā)送郵件。例如：當(dāng)一個訂單交費之后，可以物流人員發(fā)送Email，通知他盡快發(fā)貨。
l 返回自定義的錯誤信息：約束是不能返回信息的，而觸發(fā)器可以。例如插入一條重復(fù)記錄時，可以返回一個具體的友好的錯誤信息給前臺應(yīng)用程序。
l 更改原本要操作的SQL語句：觸發(fā)器可以修改原本要操作的SQL語句，例如原本的SQL語句是要刪除數(shù)據(jù)表里的記錄，但該數(shù)據(jù)表里的記錄是最要記錄，不允許刪除的，那么觸發(fā)器可以不執(zhí)行該語句。
l 防止數(shù)據(jù)表構(gòu)結(jié)更改或數(shù)據(jù)表被刪除：為了保護已經(jīng)建好的數(shù)據(jù)表，觸發(fā)器可以在接收到Drop和Alter開頭的SQL語句里，不進行對數(shù)據(jù)表的操作。
11.1.2 觸發(fā)器的種類
在SQL Server 2005中，觸發(fā)器可以分為兩大類：DML觸發(fā)器和DDL觸發(fā)器
l DML觸發(fā)器：DML觸發(fā)器是當(dāng)數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)操作語言（Data Manipulation Language）事件時執(zhí)行的存儲過程。DML觸發(fā)器又分為兩類：After觸發(fā)器和Instead Of觸發(fā)器
l DDL觸發(fā)器：DDL觸發(fā)器是在響應(yīng)數(shù)據(jù)定義語言（Data Definition Language）事件時執(zhí)行的存儲過程。DDL觸發(fā)器一般用于執(zhí)行數(shù)據(jù)庫中管理任務(wù)。如審核和規(guī)范數(shù)據(jù)庫操作、防止數(shù)據(jù)庫表結(jié)構(gòu)被修改等。
11.2 DML觸發(fā)器的分類
SQL Server 2005的DML觸發(fā)器分為兩類：
l After觸發(fā)器：這類觸發(fā)器是在記錄已經(jīng)改變完之后（after），才會被激活執(zhí)行，它主要是用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯誤，也可以用Rollback Transaction語句來回滾本次的操作。
l Instead Of觸發(fā)器：這類觸發(fā)器一般是用來取代原本的操作，在記錄變更之前發(fā)生的，它并不去執(zhí)行原來SQL語句里的操作（Insert、Update、Delete），而去執(zhí)行觸發(fā)器本身所定義的操作。
11.3 DML觸發(fā)器的工作原理
在SQL Server 2005里，為每個DML觸發(fā)器都定義了兩個特殊的表，一個是插入表，一個是刪除表。這兩個表是建在數(shù)據(jù)庫服務(wù)器的內(nèi)存中的，是由系統(tǒng)管理的邏輯表，而不是真正存儲在數(shù)據(jù)庫中的物理表。對于這兩個表，用戶只有讀取的權(quán)限，沒有修改的權(quán)限。
這兩個表的結(jié)構(gòu)與觸發(fā)器所在數(shù)據(jù)表的結(jié)構(gòu)是完全一致的，當(dāng)觸發(fā)器的工作完成之后，這兩個表也將會從內(nèi)存中刪除。
插入表里存放的是更新前的記錄：對于插入記錄操作來說，插入表里存放的是要插入的數(shù)據(jù)；對于更新記錄操作來說，插入表里存放的是要更新的記錄。
刪除表里存放的是更新后的記錄：對于更新記錄操作來說，刪除表里存放的是更新前的記錄（更新完后即被刪除）；對于刪除記錄操作來說，刪除表里存入的是被刪除的舊記錄。
下面看一下觸發(fā)器的工作原理。
11.3.1 After觸發(fā)器的工作原理
After觸發(fā)器是在記錄更變完之后才被激活執(zhí)行的。以刪除記錄為 例：當(dāng)SQL Server接收到一個要執(zhí)行刪除操作的SQL語句時，SQL Server先將要刪除的記錄存放在刪除表里，然后把數(shù)據(jù)表里的記錄刪除，再激活A(yù)fter觸發(fā)器，執(zhí)行After觸發(fā)器里的SQL語句。執(zhí)行完畢之后， 刪除內(nèi)存中的刪除表，退出整個操作。
還是舉上面的例子：在產(chǎn)品庫存表里，如果要刪除一條產(chǎn)品記錄，在刪除記錄時，觸發(fā)器可以檢查該產(chǎn)品庫存數(shù)量是否為零，如果不為零則取消刪除操作。看一下數(shù)據(jù)庫是怎么操作的：
（1）接收SQL語句，將要從產(chǎn)品庫存表里刪除的產(chǎn)品記錄取出來，放在刪除表里。
（2）從產(chǎn)品庫存表里刪除該產(chǎn)品記錄。
（3）從刪除表里讀出該產(chǎn)品的庫存數(shù)量字段，判斷是不是為零，如果為零的話，完成操作，從內(nèi)存里清除刪除表；如果不為零的話，用Rollback Transaction語句來回滾操作。
11.3.2 Instead Of觸發(fā)器的工作原理
Instead Of觸發(fā)器與After觸發(fā)器不同。After觸發(fā)器是在Insert、Update和Delete操作完成后才激活的，而Instead Of觸發(fā)器，是在這些操作進行之前就激活了，并且不再去執(zhí)行原來的SQL操作，而去運行觸發(fā)器本身的SQL語句。
11.4 設(shè)計DML觸發(fā)器的注意事項及技巧
在了解觸發(fā)器的種類和工作理由之后，現(xiàn)在可以開始動手來設(shè)計觸發(fā)器了，不過在動手之前，還有一些注意事項必須先了解一下：
11.4.1 設(shè)計觸發(fā)器的限制
在觸發(fā)器中，有一些SQL語句是不能使用的，這些語句包括：
表11.1 在DML觸發(fā)器中不能使用的語句
| 不能使用的語句 | 語句功能 |
| Alter Database | 修改數(shù)據(jù)庫 |
| Create Database | 新建數(shù)據(jù)庫 |
| Drop Database | 刪除數(shù)據(jù)庫 |
| Load Database | 導(dǎo)入數(shù)據(jù)庫 |
| Load Log | 導(dǎo)入日志 |
| Reconfigure | 更新配置選項 |
| Restore Database | 還原數(shù)據(jù)庫 |
| Restore Log | 還原數(shù)據(jù)庫日志 |
另外，在對作為觸發(fā)操作的目標(biāo)的表或視圖使用了下面的SQL語句時，不允許在DML觸發(fā)器里再使用這些語句：
表11.2 在目標(biāo)表中使用過的，DML觸發(fā)器不能再使用的語句
| 不能使用的語句 | 語句功能 |
| Create Index | 建立索引 |
| Alter Index | 修改索引 |
| Drop Index | 刪除索引 |
| DBCC Dbreindex | 重新生成索引 |
| Alter Partition Function | 通過拆分或合并邊界值更改分區(qū) |
| Drop Table | 刪除數(shù)據(jù)表 |
| Alter Table | 修改數(shù)據(jù)表結(jié)構(gòu) |
11.4.2 如何在觸發(fā)器取得字段修改前和修改后的數(shù)據(jù)
上面介紹過，SQL Server 2005在為每個觸發(fā)器都定義了兩個虛擬表，一個是插入表（inserted），一個是刪除表（deleted），現(xiàn)在把這兩個表存放的數(shù)據(jù)列表說明一下：
表11.3 插入/刪除表的功能
| 激活觸發(fā)器的動作 | Inserted表 | Deleted表 |
| Insert | 存放要插入的記錄 | |
| Update | 存放要更新的記錄 | 存放更新前的舊記錄 |
| Delete | 存放要刪除的舊記錄 |
以上面刪除庫存產(chǎn)品記錄為例，在刪除時觸發(fā)器要判斷庫存數(shù)量是否為零，那么判斷就應(yīng)該這么寫：
If (Select 庫存數(shù)量 From Deleted)>0
Begin
Print ‘庫存數(shù)量大于零時不能刪除此記錄’
Rollback Transaction
End
11.4.3 其他注意事項
l After觸發(fā)器只能用于數(shù)據(jù)表中，Instead Of觸發(fā)器可以用于數(shù)據(jù)表和視圖上，但兩種觸發(fā)器都不可以建立在臨時表上。
l 一個數(shù)據(jù)表可以有多個觸發(fā)器，但是一個觸發(fā)器只能對應(yīng)一個表。
l 在同一個數(shù)據(jù)表中，對每個操作（如Insert、Update、Delete）而言可以建立許多個After觸發(fā)器，但Instead Of觸發(fā)器針對每個操作只有建立一個。
l 如果針對某個操作即設(shè)置了After觸發(fā)器又設(shè)置了Instead Of觸發(fā)器，那么Instead of觸發(fā)器一定會激活，而After觸發(fā)器就不一定會激活了。
l Truncate Table語句雖然類似于Delete語句可以刪除記錄，但是它不能激活Delete類型的觸發(fā)器。因為Truncate Table語句是不記入日志的。
l WRITETEXT語句不能觸發(fā)Insert和Update型的觸發(fā)器。
l 不同的SQL語句，可以觸發(fā)同一個觸發(fā)器，如Insert和Update語句都可以激活同一個觸發(fā)器。
11.5 設(shè)計After觸發(fā)器
在了解觸發(fā)器及其種類、作用、工作原理之后，下面詳細(xì)講述一下要怎么去設(shè)計及建立觸發(fā)器。
11.5.1 設(shè)計簡單的After觸發(fā)器
下面用實例設(shè)計一個簡單的After Insert觸發(fā)器，這個觸發(fā)器的作用是：在插入一條記錄的時候，發(fā)出“又添加了一種產(chǎn)品”的友好提示。
（1）啟動Management Studio，登錄到指定的服務(wù)器上。
（2）在如圖11.1所示界面的【對象資源管理器】下選擇【數(shù)據(jù)庫】，定位到【Northwind】數(shù)據(jù)庫à【表】à【dbo.產(chǎn)品】，并找到【觸發(fā)器】項。
圖11.1 定位到觸發(fā)器
（3）右擊【觸發(fā)器】，在彈出的快捷菜單中選擇【新建觸發(fā)器】選項，此時會自動彈出【查詢編輯器】對話框，在【查詢編輯器】的編輯區(qū)里SQL Server已經(jīng)預(yù)寫入了一些建立觸發(fā)器相關(guān)的SQL語句，如圖11.2所示。
圖11.2 SQL Server 2005預(yù)寫的觸發(fā)器代碼
（4）修改【查詢編輯器】里的代碼，將從“CREATE”開始到“GO”結(jié)束的代碼改為以下代碼：
CREATE TRIGGER 產(chǎn)品_Insert
ON 產(chǎn)品
AFTER INSERT
AS
BEGIN
print '又添加了一種產(chǎn)品'
END
GO
如果有興趣的話，也可以去修改一下如圖11.2中綠色部分的版權(quán)信息。
（5）單擊工具欄中的【分析】按鈕
圖11.3 檢查語法
（6）語法檢查無誤后，單擊【執(zhí)行】按鈕，生成觸發(fā)器。
（7）關(guān)掉查詢編輯器對話框，刷新一下觸發(fā)器對話框，可以看到剛才建立的【產(chǎn)品_Insert】觸發(fā)器，如圖11.4所示。
圖11.4 建好的觸發(fā)器
建立After Update觸發(fā)器、After Delete觸發(fā)器和建立After Insert觸發(fā)器的步驟一致，不同的地方是把上面的SQL語句中的AFTER INSERT分別改為AFTER UPDATE和AFTER DELETE即可，如下所示，有興趣的讀者可以自行測試。
CREATE TRIGGER 產(chǎn)品_Update
ON 產(chǎn)品
AFTER UPDATE
AS
BEGIN
print '有一種產(chǎn)品更改了'
END
GO
CREATE TRIGGER 產(chǎn)品_Delete
ON 產(chǎn)品
AFTER DELETE
AS
BEGIN
print '又刪除了一種產(chǎn)品'
END
GO
11.5.2 測試觸發(fā)器功能
建好After Insert觸發(fā)器之后，現(xiàn)在來測試一下觸發(fā)器是怎么樣被激活的。
（1）在Management Studio里新建一個查詢，在彈出的【查詢編輯器】對話框里輸入以下代碼：
INSERT INTO 產(chǎn)品(產(chǎn)品名稱) VALUES ('大蘋果')
（2）單擊【執(zhí)行】按鈕，可以看到【消息】對話框里顯示出一句提示：“又添加了一種產(chǎn)品”，如圖11.5所示，這說明，After Insert觸發(fā)器被激活，并運行成功了。
圖11.5 查看觸發(fā)器的運行結(jié)果
而如果在【查詢編輯器】里執(zhí)行的不是一個Insert語句，而是一個Delete語句的話，After Insert觸發(fā)器將不會被激活。如在【查詢編輯器】輸入以下語句：
DELETE FROM 產(chǎn)品 WHERE (產(chǎn)品名稱= '大蘋果')
單擊【執(zhí)行】按鈕，在【消息】對話框里只顯示了一句“(1行受影 響)”的提示，而沒有“又添加了一種產(chǎn)品”的提示，如圖11.6所示。這是因為Delete語句是不能激活A(yù)fter Insert觸發(fā)器，所以After Insert觸發(fā)器里的“print ‘又添加了一種產(chǎn)品’”語句并沒有執(zhí)行。
圖11.6 執(zhí)行刪除語句不會激活A(yù)fter Insert觸發(fā)器
11.5.3 建立觸發(fā)器的SQL語句
回顧一下，在Management Studio新建一個觸發(fā)器的時候，它在查詢分析對話框給預(yù)設(shè)了一些SQL代碼，這些代碼其實上就是建立觸發(fā)器的語法提示。現(xiàn)在來看一下完整的觸發(fā)器語法代碼：
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
END
GO
用中文改了一下，以上代碼就一目了然了：
CREATE TRIGGER 觸發(fā)器名
ON 數(shù)據(jù)表名或視圖名
AFTER INSERT或DELETE或UPDATE
AS
BEGIN
--這里是要運行的SQL語句
END
GO
現(xiàn)在再對上面的代碼進行進一步的說明：
l CREATE TRIGGER 觸發(fā)器名：這一句聲明SQL語句是用來建立一個觸發(fā)器。其中觸發(fā)器名在所在的數(shù)據(jù)庫里必須是唯一的。由于觸發(fā)器是建立中數(shù)據(jù)表或視圖中的，所以有很多人都 以為只要是在不同的數(shù)據(jù)表中，觸發(fā)器的名稱就可以相同，其實觸發(fā)器的全名（Server.Database.Owner.TriggerName）是必須 唯一的，這與觸發(fā)器在哪個數(shù)據(jù)表或視圖無關(guān)。
l ON 數(shù)據(jù)表名或視圖名：這是指定觸發(fā)器所在的數(shù)據(jù)表或視圖，但是請注意，只有Instead Of觸發(fā)器才能建立在視圖上。并且，有設(shè)置為With Check Option的視圖也不允許建立Instead Of觸發(fā)器。
l AFTER INSERT或 DELETE UPDATE：這是指定觸發(fā)器的類型，是After Insert觸發(fā)器，還是After Delete觸發(fā)器，或者是After Update觸發(fā)器。其中After可以用For來代取，它們的意思都是一樣的，代表只有在數(shù)據(jù)表的操作都已正確完成后才會激活的觸發(fā)器。INSERT、 DELETE和UPDATE至少要指定一個，當(dāng)然也可以指定多個，若指定多個時，必須用逗號來分開。其順序可以任意擺放。
l With Encryption：With Encryption是用來加密觸發(fā)器的，放在“On 數(shù)據(jù)表名或視圖名”的后面，“For”的前面。如果使用了這句話，該觸發(fā)器將會被加密，任何人都看不到觸發(fā)器的內(nèi)容了。
例一：以下是一個包含提醒電子郵件的觸發(fā)器例子，如果訂單表里記錄有改動的的話（無論增加訂單還是修改、刪除訂單），則給物流人員張三發(fā)送電子郵件：
CREATE TRIGGER 訂單_Insert
ON 訂單
AFTER INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail '張三',
'訂單有更改，請查詢確定'
GO
例二：在訂單明細(xì)表里，折扣字段不能大于0.6，如果插入記錄時，折扣大于0.6的話，回滾操作。
CREATE TRIGGER 訂單明細(xì)_Insert
ON 訂單明細(xì)
AFTER INSERT
AS
BEGIN
if (Select 折扣 from inserted)>0.6
begin
print '折扣不能大于0.6'
Rollback Transaction
end
END
GO
在示例二中運用了兩個方法，一個是前面說過的，在Inserted表里查詢某個字段，還有一個是用Rollback Transaction來回滾操作。如果用下面的SQL語句來進行Insert操作的話，插入記錄將會不成功。
INSERT INTO 訂單明細(xì)(訂單ID,產(chǎn)品ID,單價,數(shù)量,折扣)
VALUES (11077,1,18,1,0.7)
運行結(jié)果如圖11.7所示：
圖11.7 插入記錄不符合觸發(fā)器里的約束，則回滾操作
11.6 設(shè)置After觸發(fā)器的激活順序
對于同一個操作，如Insert、Update或Delete來說，可以建立多個After Insert觸發(fā)器，在11.5.1節(jié)中，已經(jīng)建立了一個名為“產(chǎn)品_Insert”的觸發(fā)器，現(xiàn)在再建立一個After Insert觸發(fā)器，作用也是輸出一句有好提示，提示內(nèi)容為：“再一次告訴你，你又添加了一種產(chǎn)品”。
CREATE TRIGGER 產(chǎn)品_Insert1
ON 產(chǎn)品
AFTER INSERT
AS
BEGIN
print '再一次告訴你，你又添加了一種產(chǎn)品'
END
GO
重新運行一下插入產(chǎn)品的SQL語句：
INSERT INTO 產(chǎn)品(產(chǎn)品名稱)
VALUES ('大蘋果')
如圖11.8所示，運行一個Insert語句，在【消息】可以看到一共輸出了兩句話，說明激活兩個不同的觸發(fā)器。
圖11.8 一個語句激活兩個觸發(fā)器
當(dāng)同一個操作定義的觸發(fā)器越來越多的時候，觸發(fā)器被激活的次序就會 變得越來越重要了。在SQL Server 2005里，用存儲過程【sp_settriggerorder】可以為每一個操作各指定一個最先執(zhí)行的After觸發(fā)器和最后執(zhí)行的After觸發(fā)器。 sp_settriggerorder語法如下：
sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'
, [ @order = ] 'value'
, [ @stmttype = ] 'statement_type'
[ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]
翻譯成中文就是
sp_settriggerorder 觸發(fā)器名,
激活次序,
激活觸發(fā)器的動作
解釋如下：
l 觸發(fā)器名，要用單引號括起來，因為它是一個字符串。
l 激活次序可以為First、Last和None：First是指第一個要激活的觸發(fā)器；Last是指它最后一個要激活的觸發(fā)器；None是不指激活序，由程序任意觸發(fā)。
l 激活觸發(fā)器的動作可以是：Insert、Update和Delete。
上面的例子里，先激活的是【產(chǎn)品_Insert】觸發(fā)器，后激活的是【產(chǎn)品_Insert1】觸發(fā)器。如果把【產(chǎn)品_Insert1】觸發(fā)器設(shè)為First觸發(fā)器，把【產(chǎn)品_Insert】觸發(fā)器設(shè)為Last觸發(fā)器，那么結(jié)果將會完全不一樣。設(shè)置語句如下：
Exec sp_settriggerorder
'產(chǎn)品_Insert1','First','Insert'
go
Exec sp_settriggerorder
'產(chǎn)品_Insert',’Last’,'Insert'
Go
重新運行一下插入產(chǎn)品的SQL語句：
INSERT INTO 產(chǎn)品(產(chǎn)品名稱)
VALUES ('大蘋果')
運行結(jié)果如圖11.9，與圖11.8比較一下，是不是激活次序已經(jīng)發(fā)生變化了？
圖11.9 按次序激活的激活器
在設(shè)置After觸發(fā)器激活順序時，還有幾點是需要注意的：
l 每個操作最多只能設(shè)一個First觸發(fā)器和一個Last觸發(fā)器。
l 如果要取消已經(jīng)設(shè)好的First觸發(fā)器或Last觸發(fā)器，只要把它們設(shè)為None觸發(fā)器即可。
l 如果用Alter命令修改過觸發(fā)器內(nèi)容后，該觸發(fā)器會自動變成None觸發(fā)器。所以用Alter命令也可以用來取消已經(jīng)設(shè)好的First觸發(fā)器或Last觸發(fā)器。
l 只有After觸發(fā)器可以設(shè)置激活次序，Instead Of觸發(fā)器不可以設(shè)置激活次序。
l 激活觸發(fā)器的動作必須和觸發(fā)器內(nèi)部的激活動作一致。舉例說明：After Insert觸發(fā)器，只能為Insert操作設(shè)置激活次序，不能為Delete操作設(shè)置激活次序。以下的設(shè)置是錯誤的：
Exec sp_settriggerorder
'產(chǎn)品_Insert1','First',’Update’
go
11.7 觸發(fā)器的嵌套
當(dāng)一個觸發(fā)器執(zhí)行時，能夠觸活另一個觸發(fā)器，這種情況就是觸發(fā)器的嵌套。在SQL Server 2005里，觸發(fā)器能夠嵌套到32層。
如果不想對觸發(fā)器進行嵌套的話，可以通過【允許觸發(fā)器激活其他觸發(fā)器】的服務(wù)器配置選項來控制。但不管此設(shè)置是什么，都可以嵌套Instead Of觸發(fā)器。設(shè)置觸發(fā)器嵌套的選項更改方法為：
（1）打開Management Studio，在【對象資源管理】中，右擊服務(wù)器名，并選擇【屬性】選項。
（2）單擊【高級】節(jié)點。
（3）在【雜項】里設(shè)置【允許觸發(fā)器激活其他觸發(fā)器】為True或False。如圖11.10所示：
圖11.10 開啟/關(guān)閉觸發(fā)器嵌套
現(xiàn)在，在Northwind數(shù)據(jù)庫里建一個操作記錄表，用來記錄所有數(shù)據(jù)表的操作，無論是對哪個數(shù)據(jù)表進行了插入、更新或刪除，都可以把操作內(nèi)容和操作時間記錄到操作記錄表里。下面是建立操作記錄表的SQL語句：
CREATE TABLE 操作記錄表(
編號 int IDENTITY(1,1) NOT NULL,
操作表名 varchar(50) NOT NULL,
操作語句 varchar(2000) NOT NULL,
操作內(nèi)容 varchar(2000) NOT NULL,
操作時間 datetime NOT NULL
CONSTRAINT DF_操作記錄表_操作時間 DEFAULT (getdate()),
CONSTRAINT PK_操作記錄表 PRIMARY KEY CLUSTERED
(
編號 ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
為了簡便起見，在操作記錄表里，只建一個After Insert觸發(fā)器，觸發(fā)器的作用是輸入一條語句：“數(shù)據(jù)庫又有記錄變動了”。在實際應(yīng)用時，讀者可自行修改成所需的代碼。
CREATE TRIGGER 操作記錄表_Insert
ON 操作記錄表
AFTER INSERT
AS
BEGIN
print '數(shù)據(jù)庫又有記錄變動了'
END
GO
作為示例，只在類別表里建立一個After Insert觸發(fā)器，當(dāng)在類別表里插入一條記錄的時候，該觸發(fā)器向操作記錄表里插入一條記錄，而在操作記錄表里插入記錄時，將會觸發(fā)操作記錄表里的【操作記錄表_Insert】觸發(fā)器。
CREATE TRIGGER 類別_Insert
ON 類別
AFTER INSERT
AS
BEGIN
Declare
@類別名稱 nvarchar(15),
@說明 nvarchar(max)
set @類別名稱= (Select 類別名稱 from inserted)
set @說明= (Select 說明 from inserted)
INSERT INTO 操作記錄表(操作表名,操作語句,操作內(nèi)容)
VALUES ('類別表','插入記錄','類別名稱：'+@類別名稱+',說明：'+@說明)
END
GO
現(xiàn)在運行一下對類別表的插入語句
INSERT INTO 類別(類別名稱,說明)
VALUES ('書籍','各類圖書')
運行結(jié)果如圖11.11所示：
圖11.11 觸發(fā)器嵌套被激活
在【消息】對話框可以看到“數(shù)據(jù)庫又有記錄變動了”，這說明，觸發(fā)器已經(jīng)被嵌套激活了。如果把【允許觸發(fā)器激活其他觸發(fā)器】的選項設(shè)為False，再看看運行結(jié)果：
圖11.12 觸發(fā)器嵌套沒有被激活
如圖11.12所示，現(xiàn)在沒有“數(shù)據(jù)庫又有記錄變動了”的提示輸出，說明嵌套的觸發(fā)器沒有被激活。
11.8 觸發(fā)器的遞歸
觸發(fā)器的遞歸是指，一個觸發(fā)器從其內(nèi)部又一次激活該觸發(fā)器。例如一 個Insert觸發(fā)器的內(nèi)部還有一條對本數(shù)據(jù)表插入記錄的SQL語句，那么這個插入語句就有可能再一次激活這個觸發(fā)器本身。當(dāng)然，這種遞歸的觸發(fā)器內(nèi)部還 會有判斷語句，要一定的情況下才會執(zhí)行那個SQL語句，否則的話，就會變成死循環(huán)了。
上面的例子說的是直接遞歸的觸發(fā)器，還有一種是間接遞歸的觸發(fā)器， 舉例說明：當(dāng)向A表插入一條記錄時，激活了A表的Insert觸發(fā)器，A表的Insert觸發(fā)器里有一個SQL語句是對B表進行Insert操作的，而在 B表的Insert觸發(fā)器里也有一句話是對A表進行Insert操作的。這樣就是觸發(fā)器的間接遞歸。
一般情況來說，SQL Server服務(wù)器是不允許遞歸的，如果要打開觸發(fā)器遞歸的功能，同樣是將【允許觸發(fā)器激活其他觸發(fā)器】設(shè)為True，如圖11.10所示。
11.9 設(shè)計Instead Of觸發(fā)器
Instead Of觸發(fā)器與After觸發(fā)器的工作流程是不一樣的。After觸發(fā)器是在SQL Server服務(wù)器接到執(zhí)行SQL語句請求之后，先建立臨時的Inserted表和Deleted表，然后實際更改數(shù)據(jù)，最后才激活觸發(fā)器的。而 Instead Of觸發(fā)器看起來就簡單多了，在SQL Server服務(wù)器接到執(zhí)行SQL語句請求后，先建立臨時的Inserted表和Deleted表，然后就觸發(fā)了Instead Of觸發(fā)器，至于那個SQL語句是插入數(shù)據(jù)、更新數(shù)據(jù)還是刪除數(shù)據(jù)，就一概不管了，把執(zhí)行權(quán)全權(quán)交給了Instead Of觸發(fā)器，由它去完成之后的操作。
11.9.1 Instead Of觸發(fā)器的使用范圍
Instead Of觸發(fā)器可以同時在數(shù)據(jù)表和視圖中使用，通常在以下幾種情況下，建議使用Instead Of觸發(fā)器：
l 數(shù)據(jù)庫里的數(shù)據(jù)禁止修改：例如電信部門的通話記錄是不能修改的，一旦修改，則通話費用的計數(shù)將不正確。在這個時候，就可以用Instead Of觸發(fā)器來跳過Update修改記錄的SQL語句。
l 有可能要回滾修改的SQL語句：如11.5.3節(jié)中的例二，用After觸發(fā)器并不是一個最好的方法，如果用Instead Of觸發(fā)器，在判斷折扣大于0.6時，就中止了更新操作，避免在修改數(shù)據(jù)之后再回滾操作，減少服務(wù)器負(fù)擔(dān)。
l 在視圖中使用觸發(fā)器：因為After觸發(fā)器不能在視圖中使用，如果想在視圖中使用觸發(fā)器，就只能用Instead Of觸發(fā)器。
l 用自己的方式去修改數(shù)據(jù)：如不滿意SQL直接的修改數(shù)據(jù)的方式，可用Instead Of觸發(fā)器來控制數(shù)據(jù)的修改方式和流程。
11.9.2 設(shè)計簡單的Instead Of觸發(fā)器
Instead Of觸發(fā)器的語法如下：
CREATE TRIGGER 觸發(fā)器名
ON 數(shù)據(jù)表名或視圖名
Instead Of INSERT或DELETE或UPDATE
AS
BEGIN
--這里是要運行的SQL語句
END
GO
從上面可以看得出，Instead Of觸發(fā)器與After觸發(fā)器的語法幾乎一致，只是簡單地把After改為Instead Of。前面說過的11.5.3節(jié)中的例二，用After觸發(fā)器并不是一個最好的方法，如果用Instead Of觸發(fā)器，在判斷折扣大于0.6時，就中止了更新操作，避免在修改數(shù)據(jù)之后再回滾操作，減少服務(wù)器負(fù)擔(dān)?，F(xiàn)將原來的觸發(fā)器改為Instead Of觸發(fā)器：
CREATE TRIGGER 訂單明細(xì)_Insert
ON 訂單明細(xì)
Instead Of INSERT
AS
BEGIN
SET NOCOUNT ON;
declare
@訂單ID int,
@產(chǎn)品ID int,
@單價 money,
@數(shù)量 smallint,
@折扣 real
set @訂單ID = (select 訂單ID from inserted)
set @產(chǎn)品ID = (select 產(chǎn)品ID from inserted)
set @單價 = (select 單價 from inserted)
set @數(shù)量 = (select 數(shù)量 from inserted)
set @折扣 = (select 折扣 from inserted)
if (@折扣)>0.6
print '折扣不能大
11.10 查看DML觸發(fā)器
查看已經(jīng)設(shè)計好的DML觸發(fā)器有兩種方式，一種是通用Management Studio來查看，一種是利用系統(tǒng)存儲過程來查看。
11.10.1 在Management Studio中查看觸發(fā)器
在Management Studio中查看觸發(fā)器的步驟：
（1）啟動Management Studio，登錄到指定的服務(wù)器上。
（2）在如圖11.13所示界面的【對象資源管理器】下選擇【數(shù)據(jù)庫】，定位到要查看觸發(fā)器的數(shù)據(jù)表上，并找到【觸發(fā)器】項。
圖11.13 查看觸發(fā)器列表
（3）單擊【觸發(fā)器】，在右邊的【摘要】對話框里，可以看到已經(jīng)建 好的該數(shù)據(jù)表的觸發(fā)器列表。如果在點擊【觸發(fā)器】后，右邊沒有顯示【摘要】對話框，可以在單擊菜單欄上的【視圖】菜單，選擇【摘要】選項，打開【摘要】對 話框。如果在【摘要】對話框里沒有看到本應(yīng)存在的觸發(fā)器列表，可以【摘要】對話框里右擊空白處，在彈出的快捷菜單中選擇【刷新】選項，刷新對話框后即可看 到觸發(fā)器列表。
（4）雙擊要查看的觸發(fā)器名，Management Studio自動彈出一個【查詢編輯器】對話框，對話框里顯示的是該觸發(fā)器的內(nèi)容，如圖11.14所示：
圖11.14 查看觸發(fā)器內(nèi)容
11.10.2 用系統(tǒng)存儲過程查看觸發(fā)器
SQL Server 2005里已經(jīng)建好了兩個系統(tǒng)存儲過程，可以用這兩個系統(tǒng)存儲過程來查看觸發(fā)器的情況：
11.10.2.1 sp_help：
系統(tǒng)存儲過程“sp_help”可以了解如觸發(fā)器名稱、類型、創(chuàng)建時間等基本信息，其語法格式為：
sp_help ‘觸發(fā)器名’
舉例：
sp_help '產(chǎn)品_Insert'
運行結(jié)果如圖11.15所示，可以看到觸發(fā)器“產(chǎn)品_insert”的基本情況。
圖11.15 查看觸發(fā)器的基本情況
11.10.2.2 sp_helptext：
系統(tǒng)存儲過程“sp_helptext”可以查看觸發(fā)器的文本信息，其語法格式為：
sp_helptext ‘觸發(fā)器名’
舉例：
sp_helptext '產(chǎn)品_Insert'
運行結(jié)果如圖11.16所示，可以看到觸發(fā)器“產(chǎn)品_insert”的具體文本內(nèi)容。
圖11.16 查看觸發(fā)器的基本情況
于0.6'
else
INSERT INTO 訂單明細(xì)
(訂單ID,產(chǎn)品ID,單價,數(shù)量,折扣)
VALUES
(@訂單ID,@產(chǎn)品ID,@單價,@數(shù)量,@折扣)
END
GO
上面的觸發(fā)器里寫入了一句“SET NOCOUNT ON”，這一句的作用是，屏蔽在觸發(fā)器里Insert語句執(zhí)行完之后返回的所影響行數(shù)的消息。
11.11 修改DML觸發(fā)器
在Management Studio中修改觸發(fā)器之前，必須要先查看觸發(fā)器的內(nèi)容，通過11.10.1節(jié)的第（1）步到第（4）步，細(xì)心的讀者可以已經(jīng)發(fā)現(xiàn)，如圖11.14所 示，在【查詢編輯器】對話框里顯示的就是用來修改觸發(fā)器的代碼。編輯完代碼之后，單擊【執(zhí)行】按鈕運行即可。修改觸發(fā)器的語法如下：
ALTER TRIGGER 觸發(fā)器名
ON 數(shù)據(jù)表名或視圖名
AFTER INSERT或DELETE或UPDATE
AS
BEGIN
--這里是要運行的SQL語句
END
GO
如果只要修改觸發(fā)器的名稱的話，也可以使用存儲過程“sp_rename”。其語法如下：
sp_rename ‘舊觸發(fā)器名’,’新觸發(fā)器名’
值得一提的是修改觸發(fā)器名稱有可能會使某些腳本或存儲過程運行出錯。
11.12 刪除DML觸發(fā)器
在Management Studio中刪除觸發(fā)器，必須要先查到觸發(fā)器列表，通過11.10.1節(jié)的第（1）步到第（3）步，可以查看到數(shù)據(jù)表下的所有觸發(fā)器列表，右擊其中一個 觸發(fā)器，在彈出快捷菜單中選擇【刪除】選項，此時將會彈出【刪除對象】對話框，在該對話框中單擊【確定】按鈕，刪除操作完成。用以下SQL語句也對可刪除 觸發(fā)器：
Drop Trigger 觸發(fā)器名
注意：如果一個數(shù)據(jù)表被刪除，那么SQL Server會自動將與該表相關(guān)的觸發(fā)器刪除。
11.13 禁用與啟用DML觸發(fā)器
禁用觸發(fā)器與刪除觸發(fā)器不同，禁用觸發(fā)器時，仍會為數(shù)據(jù)表定義該觸發(fā)器，只是在執(zhí)行Insert、Update或Delete語句時，除非重新啟用觸發(fā)器，否則不會執(zhí)行觸發(fā)器中的操作。
在Management Studio中禁用或啟用觸發(fā)器，也必須要先查到觸發(fā)器列表，觸發(fā)器列表里，右擊其中一個觸發(fā)器，在彈出快捷菜單中選擇【禁用】選項，即可禁用該觸發(fā)器。啟用觸發(fā)器與上類似，只是在彈出快捷菜單中選擇【啟用】選項即可。
用以下Alter Table語句也禁用或啟用觸發(fā)器，其語法如下：
Alter table 數(shù)據(jù)表名
Disable或Enable trigger 觸發(fā)器名或ALL
用Disable可以禁用觸發(fā)器，用Enable可以啟用觸發(fā)器；如果要禁用或啟用所有觸發(fā)器，用“ALL”來代替觸發(fā)器名。
11.14 2005新增功能：DDL觸發(fā)器
DDL觸發(fā)器是SQL Server 2005新增的一個觸發(fā)器類型，是一種特殊的觸發(fā)器，它在響應(yīng)數(shù)據(jù)定義語言（DDL）語句時觸發(fā)。一般用于數(shù)據(jù)庫中執(zhí)行管理任務(wù)。
與DML觸發(fā)器一樣，DDL觸發(fā)器也是通過事件來激活，并執(zhí)行其中 的SQL語句的。但與DML觸發(fā)器不同，DML觸發(fā)器是響應(yīng)Insert、Update或Delete語句而激活的，DDL觸發(fā)器是響應(yīng)Create、 Alter或Drop開頭的語句而激活的。一般來說，在以下幾種情況下可以使用DDL觸發(fā)器：
l 數(shù)據(jù)庫里的庫架構(gòu)或數(shù)據(jù)表架構(gòu)很重要，不允許被修改。
l 防止數(shù)據(jù)庫或數(shù)據(jù)表被誤操作刪除。
l 在修改某個數(shù)據(jù)表結(jié)構(gòu)的同時修改另一個數(shù)據(jù)表的相應(yīng)的結(jié)構(gòu)。
l 要記錄對數(shù)據(jù)庫結(jié)構(gòu)操作的事件。
11.15 2005新增功能：設(shè)計DDL觸發(fā)器
只要注意到DDL觸發(fā)器和DML觸發(fā)器的區(qū)別，設(shè)計DDL觸發(fā)器與設(shè)計DML觸發(fā)器也很類似，下面詳細(xì)講述一下要怎么去設(shè)計一個DDL觸發(fā)器。
11.15.1 建立DDL觸發(fā)器的語句
建立DDL觸發(fā)器的語法代碼如下：
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }
用中文取代一下英文可以看得更明白：
CREATE TRIGGER 觸發(fā)器名
ON ALL SERVER或DATABASE
FOR 或AFTER
激活DDL觸發(fā)器的事件
AS
要執(zhí)行的SQL語句
其中：
l ON后面的All Server是將DDL觸發(fā)器作用到整個當(dāng)前的服務(wù)器上。如果指定了這個參數(shù)，在當(dāng)前服務(wù)器上的任何一個數(shù)據(jù)庫都能激活該觸發(fā)器。
l ON后面的Database是將DDL觸發(fā)器作用到當(dāng)前數(shù)據(jù)庫，只能在這個數(shù)據(jù)庫上激活該觸發(fā)器。
l For或After是同一個意思，指定的是After觸發(fā)器，DDL觸發(fā)器不能指定的Stead Of觸發(fā)器。
l 激活DDL觸發(fā)器的事件包括兩種，在DDL觸發(fā)器作用在當(dāng)前數(shù)據(jù)庫情況下可以使用以下事件：
| CREATE_APPLICATION_ROLE | ALTER_APPLICATION_ROLE | DROP_APPLICATION_ROLE |
| CREATE_ASSEMBLY | ALTER_ASSEMBLY | DROP_ASSEMBLY |
| ALTER_AUTHORIZATION _DATABASE | ||
| CREATE_CERTIFICATE | ALTER_CERTIFICATE | DROP_CERTIFICATE |
| CREATE_CONTRACT | DROP_CONTRACT | |
| GRANT_DATABASE | DENY_DATABASE | REVOKE_DATABASE |
| CREATE_EVENT_NOTIFICATION | DROP_EVENT_NOTIFICATION | |
| CREATE_FUNCTION | ALTER_FUNCTION | DROP_FUNCTION |
| CREATE_INDEX | ALTER_INDEX | DROP_INDEX |
| CREATE_MESSAGE_TYPE | ALTER_MESSAGE_TYPE | DROP_MESSAGE_TYPE |
| CREATE_PARTITION_FUNCTION | ALTER_PARTITION_FUNCTION | DROP_PARTITION_FUNCTION |
| CREATE_PARTITION_SCHEME | ALTER_PARTITION_SCHEME | DROP_PARTITION_SCHEME |
| CREATE_PROCEDURE | ALTER_PROCEDURE | DROP_PROCEDURE |
| CREATE_QUEUE | ALTER_QUEUE | DROP_QUEUE |
| CREATE_REMOTE_SERVICE _BINDING | ALTER_REMOTE_SERVICE _BINDING | DROP_REMOTE_SERVICE _BINDING |
| CREATE_ROLE | ALTER_ROLE | DROP_ROLE |
| CREATE_ROUTE | ALTER_ROUTE | DROP_ROUTE |
| CREATE_SCHEMA | ALTER_SCHEMA | DROP_SCHEMA |
| CREATE_SERVICE | ALTER_SERVICE | DROP_SERVICE |
| CREATE_STATISTICS | DROP_STATISTICS | UPDATE_STATISTICS |
| CREATE_SYNONYM | DROP_SYNONYM | CREATE_TABLE |
| ALTER_TABLE | DROP_TABLE | |
| CREATE_TRIGGER | ALTER_TRIGGER | DROP_TRIGGER |
| CREATE_TYPE | DROP_TYPE | |
| CREATE_USER | ALTER_USER | DROP_USER |
| CREATE_VIEW | ALTER_VIEW | DROP_VIEW |
| CREATE_XML_SCHEMA _COLLECTION | ALTER_XML_SCHEMA _COLLECTION | DROP_XML_SCHEMA _COLLECTION |
在DDL觸發(fā)器作用在當(dāng)前服務(wù)器情況下，可以使用以下事件：
| ALTER_AUTHORIZATION_SERVER | ||
| CREATE_DATABASE | ALTER_DATABASE | DROP_DATABASE |
| CREATE_ENDPOINT | DROP_ENDPOINT | |
| CREATE_LOGIN | ALTER_LOGIN | DROP_LOGIN |
| GRANT_SERVER | DENY_SERVER | REVOKE_SERVER |
例三，建立一個DDL觸發(fā)器，用于保護數(shù)據(jù)庫中的數(shù)據(jù)表不被修改，不被刪除。具體操作步驟如下：
（1）啟動Management Studio，登錄到指定的服務(wù)器上。
（2）在如圖11.1所示界面的【對象資源管理器】下選擇【數(shù)據(jù)庫】，定位到【Northwind】數(shù)據(jù)庫上。
（3）單擊【新建查詢】按鈕，在彈出的【查詢編輯器】的編輯區(qū)里輸入以下代碼：
CREATE TRIGGER 禁止對數(shù)據(jù)表操作
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT '對不起，您不能對數(shù)據(jù)表進行操作'
ROLLBACK ;
（4）單擊【執(zhí)行】按鈕，生成觸發(fā)器。
例四，建立一個DDL觸發(fā)器，用于保護當(dāng)前SQL Server服務(wù)器里所有數(shù)據(jù)庫不能被刪除。具體代碼如下：
CREATE TRIGGER 不允許刪除數(shù)據(jù)庫
ON all server
FOR DROP_DATABASE
AS
PRINT '對不起，您不能刪除數(shù)據(jù)庫'
ROLLBACK ;
GO
例五，建立一個DDL觸發(fā)器，用來記錄數(shù)據(jù)庫修改狀態(tài)。具體操作步驟如下：
（1）建立一個用于記錄數(shù)據(jù)庫修改狀態(tài)的表：
CREATE TABLE 日志記錄表(
編號 int IDENTITY(1,1) NOT NULL,
事件 varchar(5000) NULL,
所用語句 varchar(5000) NULL,
操作者 varchar(50) NULL,
發(fā)生時間 datetime NULL,
CONSTRAINT PK_日志記錄表 PRIMARY KEY CLUSTERED
(
編號 ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
（2）建立DDL觸發(fā)器：
CREATE TRIGGER 記錄日志
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @log XML
SET @log = EVENTDATA()
INSERT 日志記錄表
(事件, 所用語句,操作者, 發(fā)生時間)
VALUES
(
@log.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@log.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
CONVERT(nvarchar(100), CURRENT_USER),
GETDATE()
) ;
GO
其中Eventdata是個數(shù)據(jù)庫函數(shù)，它的作用是以XML格式返回有關(guān)服務(wù)器或數(shù)據(jù)庫事件的信息。@log.value是返回log這個XML結(jié)點的值，結(jié)點的位置是括號里的第一個參數(shù)。
11.15.2 測試觸發(fā)器功能
現(xiàn)在測試一下在上一章節(jié)中建立好的三個觸發(fā)器的功能。下面所有的測試都是在【查詢編輯器】對話框里進行的，要打開【查詢編輯器】對話框，只要單擊Management Studio里【新建查詢】按鈕即可。
測試?yán)豪潜ＷC【Northwind】數(shù)據(jù)庫里不能刪除表和修改表，在【查詢編輯器】對話框里輸入一個刪除表的SQL語句：
Drop table 操作記錄表
運行結(jié)果如圖11.17所示：
圖11.17 不允許刪除表格
測試?yán)模豪氖潜ＷC當(dāng)前服務(wù)器里的所有數(shù)據(jù)庫不能被刪除，在【查詢編輯器】對話框里輸入一個刪除數(shù)據(jù)庫的SQL語句：
Drop DataBase test
運行結(jié)果如圖11.18所示：
圖11.18 不允許刪除數(shù)據(jù)庫
測試?yán)澹豪迨怯涗泴Α綨orthwind】所進行的操作，在【查詢編輯器】對話框里輸入一條添加數(shù)據(jù)表和一條刪除數(shù)據(jù)表的SQL語句，然后再用Select語句查看【目志記錄表】數(shù)據(jù)表里所有的記錄：
CREATE TABLE 測試表(
編號int IDENTITY(1,1) NOT NULL,
測試內(nèi)容varchar(50) NOT NULL)
GO
Drop table 測試表
GO
select * from 日志記錄表
GO
運行時不要忘了，前面曾經(jīng)建立過一個不能刪除數(shù)據(jù)表的觸發(fā)器，要先把它禁用或刪除。運行結(jié)果如圖11.19所示：
圖11.19 記錄對數(shù)據(jù)庫的操作
11.16 2005新增功能：查看與修改DDL觸發(fā)器
DDL觸發(fā)器有兩種，一種是作用在當(dāng)前SQL Server服務(wù)器上的，一種是作用在當(dāng)前數(shù)據(jù)庫中的。這兩種DDL觸發(fā)器在Management Studio中所在的位置是不同的。
l 作用在當(dāng)前SQL Server服務(wù)器上的DDL觸發(fā)器所在位置是：【對象資源管理器】，選擇所在SQL Server服務(wù)器，定位到【服務(wù)器對象】à【觸發(fā)器】，在【摘要】對話框里就可以看到所有的作用在當(dāng)前SQL Server服務(wù)器上的DDL觸發(fā)器。
l 作用在當(dāng)前數(shù)據(jù)庫中的DDL觸發(fā)器所在位置是：【對象資源管理器】，選擇所在SQL Server服務(wù)器，【數(shù)據(jù)庫】，所在數(shù)據(jù)庫，定位到【可編程性】à【數(shù)據(jù)庫觸發(fā)器】，在摘要對話框里就可以看到所有的當(dāng)前數(shù)據(jù)庫中的DDL觸發(fā)器。
右擊觸發(fā)器，在彈出的快捷菜單中選擇【編寫數(shù)據(jù)庫觸發(fā)器腳本為】à【CREATE到】à【新查詢編輯器對話框】，然后在新打開的【查詢編輯器】對話框里可以看到該觸發(fā)器的內(nèi)容。
在Management Studio如果要修改DDL觸發(fā)器內(nèi)容，就只能先刪除該觸發(fā)器，再重新建立一個DDL觸發(fā)器。
雖然在Management Studio中沒有直接提供修改DDL觸發(fā)器的對話框，但在【查詢編輯器】對話框里依然可以用SQL語句來進行修改。下面給出幾個對DDL觸發(fā)器操作常用 的SQL代碼，由于對DDL觸發(fā)器的操作和對DML觸發(fā)器的操作類似，因此不再詳細(xì)說明用法。
l 創(chuàng)建DDL觸發(fā)器
CREATE TRIGGER (Transact-SQL)
l 刪除DDL觸發(fā)器
DROP TRIGGER (Transact-SQL)
l 修改DDL觸發(fā)器
ALTER TRIGGER (Transact-SQL)
l 重命名DDL觸發(fā)器
sp_rename (Transact-SQL)
l 禁用DDL觸發(fā)器
DISABLE TRIGGER (Transact-SQL)
l 啟用DDL觸發(fā)器
ENABLE TRIGGER (Transact-SQL)
l 刪除DDL觸發(fā)器
DROP TRIGGER (Transact-SQL)
11.17 觸發(fā)器的應(yīng)用技巧
觸發(fā)器的使用范圍很廣，使用的頻率也很高，觸發(fā)器的應(yīng)用技巧也層出不窮，下面介紹一些在觸發(fā)器里常用的技巧，希望可以做到拋磚引玉之功效。
11.17.1 如何知道觸發(fā)器修改了多少條記錄
需要注意的是，一種操作類型（Insert、Update或Delete）雖然可以激活多個觸發(fā)器，但是每個操作類型在一次操作時，對一個觸發(fā)器只激活一次。例如，運行一個Update語句，有可能一次更新了十條記錄，但是對于After Update這個觸發(fā)器，只激活一次，而不是十次。但是在Inserted表和Deleted表里會有十條記錄，這個時候，只要利用@@Rowcount這個系統(tǒng)變量就可以得知更新了多少條記錄。例如：
CREATE TRIGGER 訂單明細(xì)刪除_test
ON 訂單明細(xì)
AFTER DELETE
AS
BEGIN
print '您此次刪除了' + Cast(@@rowcount as varchar) + '條記錄'
END
GO
Delete FROM 訂單明細(xì) where 折扣=0.25
GO
Delete FROM 訂單明細(xì) where 訂單ID='123456789'
GO
這里先是建立了一個名為“訂單明細(xì)刪除_test”的觸發(fā)器，作用就是顯示刪除了多少條記錄。之后執(zhí)行兩個SQL語句，一個是刪除折扣為0.25的記錄，一個是刪除訂單ID號為123456789的記錄，這條記錄是不存在的。運行結(jié)果如圖11.20所示：
圖11.20 顯示刪除的記錄數(shù)
在圖11.20可以看出，用系統(tǒng)變量@@rowcount可以獲得刪除記錄的條數(shù)。另外，在圖中還可以看出，雖然第二個SQL語句刪除的記錄數(shù)為零，但是觸發(fā)器還是被激活了。因此可以知道，觸發(fā)器只與激活它的類型有關(guān)，與具體操作的記錄數(shù)無關(guān)。
11.17.2 如何知道插入記錄的自動編號是多少
在第11.7節(jié)，觸發(fā)器的嵌套里，【類別】數(shù)據(jù)表設(shè)計了一個觸發(fā) 器，當(dāng)在【類別】數(shù)據(jù)表里插入一件記錄的時候，將會在【操作記錄表】里也插入一條記錄，用來記錄具體的插入操作的，其實這個觸發(fā)器還可以寫得更好，不但可 以記錄插入操作所用的SQL語句，還可以記錄下當(dāng)時插入記錄時候，數(shù)據(jù)庫為這個記錄自動生成編號是多少，為以后的操作提供更大的便利。修改該觸發(fā)器的代碼 如下：
ALTER TRIGGER 類別_Insert
ON 類別
AFTER INSERT
AS
BEGIN
Declare
@類別名稱 nvarchar(15),
@說明 nvarchar(max)
set @類別名稱 = (Select 類別名稱 from inserted)
set @說明 = (Select 說明 from inserted)
INSERT INTO 操作記錄表 (操作表名,操作語句,操作內(nèi)容)
VALUES ('類別表','插入記錄',
'插入了ID號為'+cast(@@IDENTITY as varchar)+'的記錄：類別名稱：'
+@類別名稱+',說明：'+@說明)
END
GO
從上面的代碼可以看出，用@@IDENTITY可以獲得剛插入記錄的標(biāo)識值，在本例中是它的主鍵值。插入記錄后，在【操作記錄表】里可以詳細(xì)查看到插入的記錄的編號以及它的內(nèi)容。
11.17.3 如何知道某個字段是否被修改
在Update觸發(fā)器和Insert觸發(fā)器里，可以用“Update(字段名)”來判斷某個字段是不是被更改，返回的是一個布爾值。例如定單生成后，只能修改折扣的觸發(fā)器：
CREATE TRIGGER 只允許修改折扣
ON 訂單明細(xì)
Instead Of UPDATE
AS
BEGIN
SET NOCOUNT ON;
if update(折扣)
begin
declare
@訂單ID int,
@產(chǎn)品ID int,
@折扣 real
set @訂單ID = (select 訂單ID from inserted)
set @產(chǎn)品ID = (select 產(chǎn)品ID from inserted)
set @折扣 = (select 折扣 from inserted)
update 訂單明細(xì) set 折扣=@折扣
where 訂單ID=@訂單ID and 產(chǎn)品ID=@產(chǎn)品ID
end
else
begin
print '只能更改折扣字段'
end
END
GO
update 訂單明細(xì) set 折扣=0.2
where 訂單ID=10288 and 產(chǎn)品ID=54
Go
update 訂單明細(xì) set 訂單ID=10288
where 訂單ID=10288 and 產(chǎn)品ID=54
Go
上面的代碼，先建立了一個觸發(fā)器，只有修改了折扣字段的Update語句才會被執(zhí)行。然后寫了兩個Update的SQL語句，一個是修改了折扣字段的，一個是沒有修改折扣字段的。運行后的結(jié)果如圖11.21所示。第一個SQL語句被正確執(zhí)行，第二個SQL語句沒有被執(zhí)行。
圖11.21 用Update判斷字段是否被修改
11.17.4 如何返回錯誤信息
雖然上面介紹觸發(fā)器時，用過很多次Print來輸出自定義的信息，但是實際上，只有在用【查詢編輯器】中運行SQL語句才能看得到這些自定義的信息，而其他的前端應(yīng)用程序都不會顯示出這些自定義的信息，包括用Management Studio也一樣。
讀者可以自行測試一下，在Management Studio里打開【訂單明細(xì)】數(shù)據(jù)表，因為上面建了一個【只允許修改折扣】的觸發(fā)器，所以只要在不是折扣的字段里修改數(shù)據(jù)后，再將鼠標(biāo)聚焦到其他記錄上 時，被修改的數(shù)據(jù)馬上就會回滾到修改前的狀態(tài)，在這個過程中，幾乎是看不到什么提示的。如果想要在這個過程中看到提示的話，就要將觸發(fā)器修改一下，加上 “Raiserror”語句，具體修改代碼如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER 只允許修改折扣
ON 訂單明細(xì)
Instead Of UPDATE
AS
BEGIN
SET NOCOUNT ON;
if update(折扣)
begin
declare
@訂單ID int,
@產(chǎn)品ID int,
@折扣 real
set @訂單ID = (select 訂單ID from inserted)
set @產(chǎn)品ID = (select 產(chǎn)品ID from inserted)
set @折扣 = (select 折扣 from inserted)
update 訂單明細(xì)set 折扣=@折扣
where 訂單ID=@訂單ID and 產(chǎn)品ID=@產(chǎn)品ID
end
else
begin
print '只能更改折扣字段'
Raiserror('除了折扣字段之外的其他字段信息不能修改',16,5)
end
END
修改完觸發(fā)器之后，再去修改其他非“折扣”字段的內(nèi)容時，就會彈出錯誤提示，如圖11.22所示，Raiserror的用法可以查看SQL Server 2005的幫助。
圖11.22 顯示錯誤信息
11.18 小結(jié)
觸發(fā)器是與數(shù)據(jù)庫和數(shù)據(jù)表相結(jié)合的特殊的存儲過程，當(dāng)數(shù)據(jù)表有Insert、Update、Delete操作或數(shù)據(jù)庫有Create、Alter、Drop操作的時候，可以激活觸發(fā)器，并運行其中的T-SQL語句。
在SQL Server 2005中觸發(fā)器分為DML觸發(fā)器和DDL觸發(fā)器兩種。其中DML觸發(fā)器又分為After觸發(fā)器和Instead Of觸發(fā)器兩種。After觸發(fā)器是先修改記錄后激活的觸發(fā)器；Instead Of觸發(fā)器是“取代”觸發(fā)器。DDL觸發(fā)器根據(jù)作用范圍可以分為作用在數(shù)據(jù)庫的觸發(fā)器和作用在服務(wù)器的觸發(fā)器兩種。After觸發(fā)器只能用于數(shù)據(jù)表中，而Instead Of觸發(fā)器即可以用在數(shù)據(jù)表中，也可以用在視圖中。
使用CREATE TRIGGER語句可以創(chuàng)建觸發(fā)器，使用ALTER TRIGGER語句可以修改觸發(fā)器，使用Drop Trigger語句可以刪除觸發(fā)器。觸發(fā)器允許嵌套和遞歸，嵌套最多可以是32層。
