搜尋本站文章

2017-06-17

[SQL Server]:全部停用 (DISABLE) 資料庫內的 DML 觸發程序(Trigger)


因故需要 全部停用(DISABLE):資料庫內的 DML 觸發程序(Trigger)
停用範圍,包含:AFTER 觸發程序、INSTEAD OF 觸發程序

效益是優化效能,經由停用 DML 觸發程序(Trigger),讓新增、修改與刪除(INSERT、UPDATE、DELETE)資料的速度更快。

舉例來講,可應用於:測試用資料庫、複寫(Replication)機制訂閱者端(Subscriber)的資料庫等。

可以使用以下 T-SQL 陳述式達成

-- 全部停用:資料庫內的 DML 觸發程序(DML Trigger)
USE UserDB
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
GO


本範例是全部停用:資料庫內的 DML 觸發程序(Trigger),
包含:AFTER 觸發程序、INSTEAD OF 觸發程序




DML 觸發程序

當使用者試圖透過資料操作語言 (DML) 事件來修改資料時,便會執行 DML 觸發程序。
DML 事件包括資料表或檢視的 INSERT、UPDATE 或 DELETE 陳述式。

當引發任何有效的事件時,都會引發這些觸發程序,不論是否有任何資料表資料列受到影響




全部停用資料庫內的 DML 觸發程序(Trigger)

示範如下

USE AdventureWorks2014
GO

-- 01_查詢:資料庫內的 DML 觸發程序(DML Trigger)的相關狀態
SELECT s.name N'結構描述',o.name N'資料表', t.name N'觸發程序名稱', t.is_disabled  N'已停用(1)', 
 tEV.type_desc '引發觸發程序的每個事件', is_instead_of_trigger N'INSTEAD OF 觸發程序(1)', 
 t.type_desc N'物件類型的描述',  parent_class_desc N'觸發程序父類別的描述'
FROM sys.triggers t INNER JOIN sys.trigger_events tEV ON t.object_id = tEV.object_id
 INNER JOIN sys.objects o ON t.parent_id = o.object_id
 INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE parent_class_desc = 'OBJECT_OR_COLUMN' AND o.type='U'
ORDER BY s.name
GO

-- 全部停用:資料庫內的 DML 觸發程序(DML Trigger)
USE AdventureWorks2014
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
GO

-- 全部啟用:資料庫內的 DML 觸發程序(DML Trigger)
USE AdventureWorks2014
GO
EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"
GO

-- 001_查詢資料庫內的觸發程序



-- 002_全部停用:資料庫內的 DML 觸發程序(DML Trigger)



-- 003_查詢資料庫內的觸發程序_全部停用



-- 004_全部啟用:資料庫內的 DML 觸發程序(DML Trigger)



-- 005_全部啟用:資料庫內的 DML 觸發程序(DML Trigger)



-- 006_查詢資料庫內的觸發程序_全部啟用





SSMS 停用單一觸發程序

-- 010_SSMS_觸發程序已啟用




-- 012_可以停用觸發程序



-- 013_已停用此觸發程序



-- 014_可以啟用此觸發程序



-- 015_已啟用此觸發程序





參考文件

[SQL Server][Replication]:複寫使用者正在插入至 NOT FOR REPLICATION 識別欄位時...遭遇錯誤
http://sharedderrick.blogspot.tw/2017/06/sql-serverreplication-not-for.html

DISABLE TRIGGER (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms189748.aspx

停用(Disable)與啟用(Enable)「觸發程序(Trigger)」
http://sharedderrick.blogspot.tw/2010/09/disableenabletrigger.html

停用複寫的檢查條件約束
https://msdn.microsoft.com/zh-tw/library/ms190235.aspx

唯一條件約束與檢查條件約束
https://msdn.microsoft.com/zh-tw/library/ms187550.aspx#Check