德瑞克：SQL Server 學習筆記: [SQL Server]：全部停用 (DISABLE) 資料庫內的 DML 觸發程序(Trigger)

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_查詢資料庫內的觸發程序