14第十四章觸發器(轉載)

標籤：

14第十四章觸發器
DML --> AFTER / FOR   UPDATE , INSERT , DELETE  -- 用來串聯刪除         --> INSTEAD OF  在 時間之前觸發，相當於 befINSERTED, DELETED 兩張表要好好利用。在建立 DML 觸發程序時，不能使用下列語句：
CREATE / ALTER /DROP DATABASELOAD DATABASE / LOAD LOG / RECONFIGURERESTORE DATABASE  / RESTORE LOG
自動交易處理模式下，還是在隱式或顯示交易處理模式下，只要在 觸發器中發出 BEGIN TRANSACTION 語句，實際上就開始了一個嵌套事務，當觸發器中使用 ROLLBACK TRANSACTION 語句復原嵌套事務時，觸發器本身發出的所有的 BEGIN TRANSACTION 語句復原嵌套事務時，觸發器本身發出的額所有 BEGIN TRANSACTION 語句豆漿被忽略， ROLLBACK 將復原到最外部的 BEGIN TRANSACTION 。而在 這 最外部的 之前的 事務都已經提交的就不會收到影響，而這個 復原操作也會終止 批處理中 對 該語句後面語句的執行。因此，若要在 觸發器中進行部分復原，應當使用 SAVE TRANSACTION 語句設定一個事務儲存點，這樣就不會復原到 外部的 事務中去了。注意：  在 觸發器中 書寫 COMMIT TRANSACTION 的 語句，如果之前有 BEGIN TRANSACTION 語句，會被認為是僅提交該嵌套事務，如果 在 commit 之後仍然有 ROLLBACK TRANSACTION 那麼仍然會復原到最外部的 事務。
if (update(name)) 用來判斷 更新的是哪列， COLUMNS_UPDATED() 測試多個列， 但這個列 是 按位元組  加起來算的，這個函數返回一個或多個從左至右排序的位元組。 P346 是一個很經典的應用。
指定 FIRST 觸發器 和 LAST 觸發器FIRST 和 LAST 觸發器之間的執行並沒有先後順序：
sp_settriggerorder @triggername =‘ud_trig/ins_trig/del_trig‘, @order = ‘first/last‘, @stmttyp = ‘update / insert / delete‘;
由於 INSTEAD OF 觸發器一直在對基礎資料表進行更新前激發，因此不能講 INSTEAD OF 觸發器指定為 第一或 最後 一個觸發器如果使用了 ALTER TRIGGER 語句 更改了 First 或 Last 觸發器，則會刪除它們的順序值，必須使用 sp_settriggerorder 來重新設定。可以通過 OBJECTPROPERTY()函數的ExecIsFirstDeleteTrigger , ExecIsFirstInsertTrigger,ExecIsFirstUPdate....等屬性來確定觸發器時 First 觸發器，還是LAST 觸發器。
嵌套和遞迴觸發器
無論是 DML 觸發程序還是DDL 觸發程序，如果出現了一個觸發器執行啟動另一個觸發器的操作都屬於嵌套觸發器。32層可以通過nested triggers 伺服器配置選項來空值是否可以嵌套AFTER 觸發器。 INSTEAD OF 觸發器嵌套不受此選項影響。參考下面的語句：sp_configure ‘nested triggers‘,1 -- 設定 為 1 允許 after 觸發器嵌套GORECONFIGURE; --使用新環境值EXEC sp_configure ‘nested triggers‘; --查看 nested triggers 選項設定GO遞迴 P349  有個經典例子 由於有 update() 函數檢測，作為遞迴終止條件。遞迴分為 直接遞迴， 如 應用程式更新 T3 表，從而觸發了 觸發器 Trig3 , Trig3 再次更新表T3，從而再次出發了觸發器Trig3間接遞迴。 即中間經過另外的表中轉還是觸發了第一張表的觸發器：應用程式更新了 表 T1， 從而觸發了觸發器Trig1 , Trig1 更新了表T2，從而出發了觸發器 Trig2.Trig2轉而更新了 表T1 ， 從而再次觸發了 Trig1.注意： 只有在設定 RECURSIVE_TRIGGERS 資料庫選項為 ON 的情況下，才允許以遞迴方式調用AFTER 觸發器：ALTER DATABASE AdventureWorks SET RECURSIVE_TRIGGERS ON;

Instead of 觸發器：-- instead of insertCREATE TRIGGER Bef_Ins ON dbo.test11 INSTEAD OF INSERTASIF (exists(select * from dbo.Test11 where name = (select name from inserted)))print ‘exists already!!!‘elseinsert into dbo.test11select name,gender from inserted
-- instead of update 同理
instead of 給 insert 跟 update 都必須為不可為空的列指定值，但是在觸發器中需要忽略掉這些值。
3. 在INSTEAD OF 觸發器中使用 TEXT, NTEXT 和 IMAGE 資料資料修改可能會涉及 text/ ntext /image 列。 在基表中， 儲存在 text/ ntext 或 image 列中 的 值是文本指標，它只想儲存資料的 頁  P353
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N‘[dbo].[INS_TEST]‘))DROP TRIGGER [dbo].[INS_TEST]GO
CREATE TRIGGER ins_StuON dbo.StudentsAFTER INSERTASSELECT * FROM INSERTED
insert into dbo.Studentsvalues (4,‘Frank‘,88.88)

CREATE TRIGGER del_Stu ON dbo.StudentsFOR DELETEASSELECT * FROM DELETED
DELETE FROM dbo.Students WHERE StudentName = ‘Frank‘



CREATE TRIGGER update_Stu ON dbo.StudentsFOR UPDATEASSELECT * FROM INSERTEDSELECT * FROM DELETED

insert into dbo.StudentsVALUES(4,‘Frank‘,88.88)update dbo.Students set ClassID=5 where StudentName=‘Frank‘DELETE FROM dbo.Students WHERE StudentName = ‘Frank‘

------------- DDL 觸發程序 ----------------------------DDL 觸發程序是為相應一個或多個特定的資料定義語言 (Data Definition Language)語句的激發。並且 DDL 觸發程序只能在 SQL 陳述式完成之後才運行，無法作為 INSTEAD OF 觸發器。sys.server_triggers 目錄檢視查詢服務器範圍內的 DDL 觸發程序的資訊。P354
修改，刪除和禁用觸發器DROP TRIGGER MyTrigger
ALTER TRIGGER MyTRIGGERON PriTableAFTER DELETE AS    DELETE FROM DetailTable    WHERE OrderID in (SELECT OrderID from Deleted);--禁用 觸發器  方法一Disable trigger dbo.PriTrigger ON dbo.PriTable; -- DDL 觸發程序的話不能包含架構名--禁用 觸發器  方法二
ALTER TABLE dbo.PriTable    DISABLE TRIGGER PriTrigger;--重新啟用 觸發器 方法一：ENABLE TRIGGER dbo.PriTrigger ON dbo.PriTable--啟用觸發器：ALTER TABLE dbo.PriTable        ENABLE TRIGGER PriTrigger;
注意要刪除一個 DDL 觸發程序，需要制定觸發器的範圍範圍，否則將預設為要刪除DML 觸發程序。DROP TRIGGER MyTriggerON DATABASE;GO
DROP TRIGGER mYoTHERtRIGGERon all server;
禁用啟用也同樣要指定範圍範圍：DISABLE TRIGGER SafetyON DATABASE;
ENABLE TRIGGER Safety ON DATABASE;要修改一個 DDL 觸發程序，也應當使用 ALTER TRIGGER 語句。 例如：
ALTER TRIGGER SafetyON DATABASE FOR CREATE_TABLEAS    PRINT N‘CREATE TABLE 出錯‘;    SELECT EVENTDATA().VALUE(‘(/EVENT_INSTANCE/TSQLCommand/cOMMANDtEXT)[1]‘,‘nvarchar(max)‘);ROLLBACK;
原文連結本文由豆約翰部落格備份專家遠程一鍵發布

14第十四章觸發器(轉載)