SQL Server 觸發器2

標籤：blog   http   io   使用   ar   strong   for   資料   sp   

觸發器可以做很多事情，但也會帶來很多問題。使用它的技巧在於在適當的時候使用，而不要在不適當的時候使用它們。

　　觸發器的一些常見用途如下：

• 彈性參照完整性：實現很多DRI不能實現的操作(例如，跨資料庫或伺服器的參照完整性以及很多複雜的關聯類型)。
• 建立神級跟蹤：這意味寫出的記錄不僅跟蹤大多數當前的資料，還包括對每個記錄進行實際修改的曆史資料。隨著SQL Server2008中的更改資料跟蹤功能的出現，建立審計跟蹤不再那麼流行，但以前使用的就是觸發器。
• 執行與CHECK約束類似的功能，但是跨表，跨資料庫甚至是跨伺服器使用。
• 用自己的語句代替使用者的動作陳述式。

一、觸發器的概念

　　觸發器是一種特殊類型的預存程序，對特定事件作出相應。觸發器有兩種類型：資料定義語言 (Data Definition Language)(DDL)觸發器和資料操縱語言(DML)觸發器。

　　DDL觸發器在使用者以某些方式(CREATE、ALTER、DROP或相似的語句)對資料庫結構進行修改時啟用作出響應。一般來說，只會在對資料庫結構的改變或曆史進行極為嚴格的審計時才會用到DDL觸發器。

　　DML觸發器是一些附加在特定表或視圖上的程式碼片段。與需要顯式調用代碼的預存程序不同，只要有附加觸發器的時間在表中發生，觸發器中的代碼就會自動運行。實際上也不能顯式地調用觸發器-唯一的做法是在指定的表中執行所需的操作。

　　除了不能夠顯式地調用觸發器，還可在預存程序中發現另外兩個觸發器所沒有的內容：參數和返回碼。

　　可將觸發器附加到什麼事件呢？因為在SQL中可以使用3類巨集指令查詢，所以就有3種類型的觸發器，另外加上混合搭配這些時間並對時間定時啟用的混合觸發器類型。

• INSERT觸發器
• DELETE觸發器
• UPDATE觸發器
• 以後任意類型的混合

　　注意：

　　值得注意的是，有時即使執行的動作是前面這些類型中的一種，觸發器也不會啟用。問題在於進行的操作是否在記錄的活動中。例如，DELETE語句是一個正常的記錄活動，它會啟用任何刪除觸發器，而TRUNCATE TABLE也有刪除行的作用，但只是把表使用的空間釋放而已-沒有記錄單個行刪除操作，所以沒有啟用任何觸發器。大量操作預設情況下不啟用觸發器，需要顯式告知大量操作啟用觸發器。

　　建立觸發器的文法：

　　CREATE TRIGGER <trigger name>　　ON [ <schema name>. ]<table or view name>　　[WITH ENCRYPTION | EXECUTE AS <CALLER | SELF | <user> > ]　　{{{ FOR | AFTER} < [DELETE][,][INSERT][,][UPDATE] > } | INSTEAD OF }[WITH APPEND][NOT FOR REPLICATION]　　AS　　< <sql statements> | EXTERNAL NAME <assembly method specifier> >

　　ON子句用來之處觸發器將要附加的表，以及在何時何種情況下啟用這個觸發器。

　　1、ON子句

　　這部分只是對建立觸發器所針對的對象進行命名。記住，如果觸發器的類型是AFTER觸發器(使用FOR或AFTER來聲明觸發器)，那麼ON子句的目標就必須是一個表-AFTER觸發器不支援視圖。

　　2、WITH ENCRYPTION選項

　　加密觸發器。如果添加了這個選項，則可以確保沒有人能夠查看你的代碼(甚至是你自己)。和視圖與預存程序一樣，使用WITH ENCRYPTION選項需要記住的是，每次在觸發器上使用ALTER語句時都必須重新應用該選項，如果使用ALTER STATEMENT語句但不包含WITH ENCRYPTION選項，那麼觸發器就不再被加密。

　　3、FOR|AFTER子句與INSTEAD OF子句

　　除了要確定啟用觸發器(INSERT、UPDATE、DELETE)的查詢類型以外，還要對觸發器的啟用時間做出選擇。雖然人們經常考慮使用FOR觸發器，但是也可以使用INSTEAD OF觸發器。對著兩個觸發器的選擇將會影響到是在修改資料之前還是之後進入觸發器。FOR和AFTER的意義是一樣的。

　　FOR|AFTER

　　FOR(或者AFTER)子句表明了期望觸發器在何種動作類型下啟用。當有INSERT、UPDATE或DELETE或三者混合操作時，都可以啟用觸發器。

　　FOR INSERT,DELETE　　--或者是：　　FOR UPDATE,INSERT　　--或者是：　　FOR DELETE

　　1、INSERT觸發器

　　當有人向表中插入新的一行時，被標記為FOR INSERT的觸發器的代碼就會執行。對於插入的每一行來說，SQL Server會建立一個新行的副本並把該副本插入到一個特殊的表中，該表只在觸發器的範圍記憶體在,該表被稱為Inserted表。特別需要注意的是，Inserted表只在觸發器啟用時存在。在觸發器開啟之前或完成之後，都要認為該表示不存在的。

　　2、DELETE觸發器

　　它和INSERT觸發器的工作方式相同，只是Inserted表示空的(畢竟是進行刪除而非插入，所以對於Inserted表示沒有記錄)。相反，每個被刪除的記錄的副本將會插入到另一個表中，該表稱為Deleted表，和Inserted表類似，該表只存在於觸發器啟用的時間內。

　　3、UPDATE觸發器

　　除了有一點改變以外，UPDATE觸發器和前面的觸發器是很類似的。對錶中現有的記錄進行修改時，都會啟用被聲明FOR UPDATE的觸發器的代碼。唯一的改變是沒有UPDATE表。SQL Server認為每一行好像刪除了現有記錄，並插入了全新的記錄。聲明為FOR UPDATE的觸發器並不是只包含一個表，而是兩個特殊的表，稱為Inserted表和Deleted表。當然，這兩個表的行數是完全相同。

　　4、WITH APPEND選項

　　WITH APPEND選項並不常用，老實講，用到它的可能性很小；WITH APPEND選項只能應用於6.5相容模式中。

　　如果已經聲明了一個稱為trgCheck的觸發器在更新和插入時強制執行資料完整性，那麼就不能建立另一個觸發器來進行串聯更新。一旦建立了更新(或插入、刪除)觸發器，那麼就不能建立另一個同一動作類型的觸發器。為解決這個問題，WITH APPEND子句顯式地告訴SQL Server，即使在表上已經有了這種類型的觸發器，還可以添加一個新的觸發器。當有合適的觸發動作(INSERT、UPDATE、DELETE)發生時，會同時啟用兩個觸發器。

　　5、NOT FOR REPLICATION選項

　　如果添加了該選項，會稍微地改變關於何時啟用觸發器的規則。在適當的位置使用這個選項，無論與複製相關的任務何時修改表，都不會啟用觸發器。通常，當修改了原始表，並且不會再進行修改的時候會啟用觸發器(進行內務處理或級聯等操作)。

　　6、AS子句

　　和在預存程序中的使用完全相同，這正是觸發器的實質所在。AS關鍵字告訴SQL Server，代碼將要啟動。

二、使用觸發器實施資料完整性規則

　　雖然觸發器不會成為首要的選擇，但是觸發器也同樣可以執行和CHECK約束甚至是DEFAULT約束一樣的功能。使用觸發器還是CHECK約束？答案是：看情況而定。如果CHECK約束可以完成，那麼可能CHECK約束是更受青睞的選擇。但是，有時會出現CHECK約束不能完成任務的情況，或是CHECK過程中的某些固有內容使其顯得不如觸發器更為可取。

　　想要使用觸發器而非CHECK約束的例子包括：

• 商務規則需要引用單個表中的資料。
• 商務規則需要檢查更新的變化。
• 需要一個定製的錯誤訊息。

　　1、處理來自於其他表的需求

　　CHECK約束快速而且有效，但是他們不是萬能的。可能當你需要跨表驗證時，它最大的缺點就會暴露出來。

　　為了示範一次跨資料表條件約束，本處建立兩個表用於測試：

　　

　　此處外鍵列是ProductId。此處我們要測試的是，當產品表的PruductNumber(庫存，單詞不懂寫)小於等於0的時候，不允許再添加1產品的訂單。

　　下面建立一個觸發器如下：

　　CREATE TRIGGER ProductNumCheck　　ON [Order]　　FOR INSERT　　AS　　DECLARE @i int　　SELECT @i = ProductId FROM Inserted        --Inserted表示最後插入的記錄的表　　IF(SELECT ProductNumber FROM Product 　　WHERE ProductId = 　　(SELECT ProductId FROM Inserted)) <=0　　PRINT @i　　BEGIN    　　PRINT ‘庫存不足，禁止購買!‘    　　ROLLBACK TRANSACTION    --復原，避免插入　　END

　　現在我們來添加一個產品：

　　INSERT INTO [Order] VALUES(3,2,GETDATE())

　　顯示訊息如下：

　　

　　我們看到，當Product的庫存不足時，將不允許添加訂單。

　　2、使用觸發器檢查更新的變化

　　有時，你可能並不關心過去的值和現在的值，只是想知道變化的量。雖然沒有任何列或表給出這些資訊，但是可以在觸發器中使用Inserted表和Deleted表進行計算。、

　　例如，剛才的產品表，假設在下訂單時會修改產品的庫存，我們不允許一次UPDATE Product超過10個。

　　CREATE TRIGGER ProductNumUpdate　　ON Product　　FOR UPDATE　　AS　　IF EXISTS(SELECT * FROM Inserted AS i INNER JOIN Deleted as d ON i.ProductId = d.ProductId WHERE i.ProductNumber - d.ProductNumber > 10)　　BEGIN    　　PRINT ‘超過10個，不允許更新‘;    　　ROLLBACK TRANSACTION    --復原，避免插入　　END

　　添加超過10條的時候

　　UPDATE Product SET ProductNumber = ProductNumber + 11 WHERE ProductId = 1

　　顯示結果如下：

　　

 　　添加少於10條的時候

　　UPDATE Product SET ProductNumber = ProductNumber + 1 WHERE ProductId = 1

　　顯示結果如下：

　　

　　3、將觸發器用於自訂錯誤訊息

　　當想要對傳給使用者或用戶端應用程式的錯誤訊息或錯誤號碼進行控制時，使用觸發器是很方便的。

　　例如，如果使用CHECK約束，只能得到標準的547錯誤，並且沒有詳盡的解釋。通常，對於想知道具體錯誤的使用者來說，這是無用的資訊-缺失，用戶端應用程式經常因為沒有足夠的資訊而不能代表使用者做出只能和有協助的響應。

　　簡而言之，當已經具備了資料完整性，但是沒有足夠的資訊進行處理的時候，可以建立觸發器。

　　注意：

　　儘管傳遞自訂錯誤碼很有用，但SQL Server中對自訂錯誤訊息的需求還是相對較少。為什麼不傳遞自訂錯誤訊息呢？原因在於某些使用者認為自訂錯誤訊息之上有一個應用程式層，並且可能需要更多有關錯誤的上下文資訊，因此特定於SQL Server的文本就無法充分發揮作用。而這時如果使用特定的錯誤碼，對於應用程式則有很大協助，有助於確定確切發生的事件以及應用正確的用戶端錯誤處理代碼。

三、觸發器的常見用途

 　　1、觸發器可以嵌套

　　嵌套的觸發器是指那些不是由發出語句直接啟用的，而是由另一個觸發器發出的語句啟用的觸發器。

　　這實際上會引起一連串的事件，一個觸發器啟用另一個觸發器，而另一個觸發器又啟用其他觸發器。

　　觸發器可以啟用的深度取決於以下幾個因素：

• 　　嵌套的觸發器是否已在系統中開啟(這是系統級的而不是資料庫級的選項；可以使用sp_configure來設定，預設為開啟的)。
• 　　是否有嵌套的深度不超過32層。
• 　　觸發器是否已經被啟用。觸發器預設為每個觸發器事務只能被啟用一次。一旦被啟用,則觸發器會忽略其他任何調用，將這些調用作為相同觸發器動作的一部分。一旦執行一條全新的語句，處理過程就會重新開始。

　　注意，如果在嵌套鏈中的任何地方進行了ROLLBACK操作，那麼整條鏈都會復原。換句話說，整個觸發器鏈就像一個事務一樣。

 　　2、觸發器可以遞迴

　　什麼是遞迴觸發器？如果某觸發器所做的事情最終啟用了自身，那麼該觸發器就是遞迴的。可以直接觸發(通過設定了觸發器的表進行巨集指令查詢來完成)，也可以間接觸發(通過嵌套過程)。

 　　遞迴觸發器比較少見，預設情況下，遞迴觸發器是關閉的。遞迴是資料庫級的選項，可以使用sp_dboption系統預存程序來設定。

　　遞迴觸發器的風險在於可能會陷入某種非預設的迴圈之中。這樣便需要確保在必要的時候可以通過遞迴檢查的形式來停止這一過程。

　　3、觸發器不能防止體繫結構的修改

　　觸發器有助於更容易地修改體繫結構。事實上，通常在開發週期的早期使用觸發器實施參照完整性，而在後期，也就是要進入生產環境時將其改為DRI。

　　4、可以在不刪除的情況下關閉觸發器

　　有時，像CHECK約束一樣，你想要關閉完整性功能以便於執行一些違反約束但是有效動作(最常見的就是匯入資料)。

　　可以使用ALTER語句來關閉觸發器，文法如下：

　　ALTER TABLE <table name>    <ENABLE|DISABLE> TRIGGER <ALL|<trigger name>>

　　如果關閉觸發器是為了匯入資料，那麼建議踢出所有使用者並進入單一使用者模式。dbo-only模式，或同時進入兩種模式。這樣一來，當關閉觸發器時，就能確保萬無一失。

　　5、觸發器的啟用順序

　　對於任何給定的表(只有AFTER觸發器才可以指定啟用順序)，給定的視圖(只有INSTEAD OF觸發器才可以指定啟用順序)。可以選擇一個觸發器優先啟用(FIRST唯一一個)。同樣，可以選擇一個觸發器最後啟用(LAST,只能選一個)。其他所有的觸發器之間沒有什麼優先啟用順序，也就是說，除了能保證FIRST第一個觸發和LAST最後啟用之外，不能保證NONE觸發器的順序。

　　FIRST和LAST觸發器的建立和其他任何觸發器的建立相同，在已經建立觸發器之後使用預存程序sp_settriggerorder來聲明啟用順序。

　　sp_settriggerorder文法如下：

    sp_settriggerorder[@triggername =] ‘<trigger name>‘,    [@order =] ‘{FIRST|LAST|NONE}‘,    [@stmttype =] ‘{INSERT|UPDATE|DELETE}‘    [, [@namespace =] {‘DATABASE‘|‘SERVER‘|NULL}]

　　這裡對於任何特殊操作(INSERT、UPDATE、DELETE)來說，只能有唯一的FIRST觸發器。同樣，對於任何特殊操作來說，也只能有唯一的LAST觸發器。其他觸發器的數量可以看做是NONE-也就是說，沒有特殊啟用順序的觸發器的數量是沒有限制的。

　　

　　為什麼要控制啟用順序

　　1、出於邏輯原因而控制啟用順序

　　為什麼要在啟用一個觸發器之前去啟用另一個觸發器。最常見的理由是第一個觸發器是後面觸發器的基礎或前面的觸發器使後面的觸發器有效。

　　2、處於效能原因而控制啟用順序

　　在效能方面，FIRST觸發器是唯一起關鍵作用的觸發器，如果有多個觸發器，但是其中只有一個觸發器可能會產生復原，那麼就需要考慮將這個觸發器標記為FIRST觸發器，這能令外復原的操作更少。

四、效能考慮

　　1、觸發器的被動型

　　這裡的意思是指觸發器發生在事務之後。當啟用觸發器時，整個查詢已經運行並且事務也已經被記錄到日誌中(但未提交，只是記錄到啟用觸發器的語句點)。這意味著如果觸發器需要復原，那麼必須撤銷已經做的所有工作。這和約束是不同，約束是主動的，約束是發生在語句真正執行前。這意味著約束會檢測可能失敗的操作，並且在進程的前期就予以阻止。所以約束通常運行得快一些-在更為複雜的查詢中速度更快。注意，只有在發生復原時，約束明顯更快。

 　　如果正在處理少量復原，而且受影響的語句的複雜性較低，執行之間較短，那麼觸發器和約束之間沒有太大的區別。但是在無法預知復原的數量的時候，堅持使用約束的效率更好。

　　2、觸發器與啟用的進程之間不存在並發問題

　　如果啟用語句不是顯示事務的一部分，那麼該語句仍然是其自身的但語句事務的一部分。無論何種情況，觸發器內部發出的ROLLBACK TRAN仍然會復原整個事務。

　　這種同屬一個事務的另一個結果是觸發器繼承了他們所屬事務上已開啟的鎖。這意味著不需要做任何特殊的處理來避免碰到事務中其他語句建立的鎖。在事務的範圍內可以自由訪問，並且可以探索資料庫基於事務中先前的語句所作的修改。

　　3、使用IF UPDATE()和COLUMNS_UPDATE()

　　在UPDATE觸發器中，可以通過檢查感興趣的列是否已被修改來限制在觸發器中執行的代碼總量。為了實現這一點，可以使用UPDATE()或COLUMN_UPDATE()函數。

　　1、UPDATE()函數

　　UPDATE()函數只在觸發器的範圍內適用。它唯一的目的是提供一個布爾值，來說明特殊列是否已經更新。使用這個函數可以決定一個特定的代碼塊是否需要運行-例如該代碼只在特定列更新時才運行。

　　建一張表如下：

　　

　　建立觸發器如下：

　　CREATE TRIGGER UPDATECHECK　　ON tb_Money　　FOR UPDATE　　AS　　IF UPDATE(MyMoney)    --如果更新了MyMoney才觸發　　BEGIN    　　PRINT(‘我的錢改變了!‘);　　END

　　執行語句：

　　UPDATE tb_Money SET MyMoney = ‘101‘ WHERE Id = 1　　--改變了MyMoney啟用了觸發器

　　輸出如下：

　　

　　留意到，改變了MyMoney列，啟用了觸發器。

　　執行語句：

　　UPDATE tb_Money SET Name = ‘張飛‘ WHERE Id = 1

　　顯示結果如下：

　　

　　2、COLUMNS_UPDATE()函數

　　這個函數和UPDATE()的運行方式不同，但目的相同。COLUMNS_UPDATE()函數可以一次檢查多列。為了實現這一點，該函數使用了位元遮罩，位元遮罩將varbinary資料的一個或多個位元組中的單個位與表中的單個列相關聯。

　　

　　對於的情況，資料的單個位元組說明了第2，第3，以及第6列已經更新，而其他列沒有更新。

　　對於超過8列的情況，SQL Server就會在右邊添加另一個位元組並且繼續計數。

　　

　　對於，這次是跟心了第2，第9以及第14列。

　　這些資訊怎麼使用呢？

• 　　|　　表示 或
• 　　&　  表示 與
• 　　^　  表示 異或

　　樣本：

　　COLUMN_UPDATE()>0　　檢查是否有列被更新。

　　COLUMN_UPDATE()^21=0　　檢查是否更新了所有指定列(1、3、5)。

　　還是剛才那張表：

　　

　　建立觸發器如下：

　　CREATE TRIGGER UPDATECHECK2　　ON tb_Money　　FOR UPDATE　　AS　　IF COLUMNS_UPDATED()&7 = 3    --如果同時更新了Name,MyMoney才觸發　　BEGIN    　　PRINT(‘我的錢和姓名改變了!‘);　　END

　　執行語句以及說明如下：

　　UPDATE tb_Money SET Name = ‘張飛‘ WHERE Id = 1　　UPDATE tb_Money SET Name = ‘趙雲‘, MyMoney = 102 WHERE Id = 1    --此行會啟用觸發器　　--計算過程如下　　--Id    Name    tb_Money　　--1        1        1    7(全部更新為7)　　--0        1        1    Name和tb_Money同時更新為(與3=3)

　　5、盡量別在觸發器中復原

　　如果在觸發器中使用很多的ROLLBACK TRAN語句，那麼請確保在執行啟用觸發器的語句前預先進行錯誤檢查。SQL Server在這種情況下，是被動的，但你可以主動。時間檢查錯誤，而不是等待復原。

　　因為復原的代價是昂貴的。

五、刪除觸發器

　　刪除觸發器和普通刪除操作略有不同，和表一樣，其問題在於觸發器的名稱被限定在模式層級。這意味著一個觸發器可以有兩個名稱相同的對象，只要方式觸發器的對象與觸發器另一個同名的對象位於不同的模式中。重申一次，觸發器是以其所處的模式命名的，而不是以觸發器所關聯的對象命名。

　　刪除觸發器的文法如下：

　　DROP TRIGGER [<schema>.]<trigger name>

　　除了模式問題之外，刪除觸發器就和刪除其他對象一樣簡單了。

SQL Server 觸發器2