德瑞克：SQL Server 學習筆記: 停用(Disable)與啟用(Enable)「觸發程序(Trigger)」

2010-09-06

停用(Disable)與啟用(Enable)「觸發程序(Trigger)」

「觸發程序(Trigger)」是一種在資料庫伺服器發生事件時，會自動執行的特殊預存程序。

「觸發程序」在 SQL Server 2005 版本上，可以分成為：
DDL 「觸發程序」。
DML 「觸發程序」。

其中，DML 「觸發程序」又可以在細分為 After 與 Instead of 「觸發程序」。

依預設，在建立觸發程序時，它是在啟用狀態。

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

DDL 觸發程序則是為了回應各種資料定義語言 (DDL) 事件而執行的。
這些事件主要是對應到 Transact-SQL CREATE、ALTER 和 DROP 陳述式，以及執行類似 DDL 作業的特定系統預存程序。

登入觸發程序會引發來回應使用者工作階段建立時所引發的 LOGON 事件。

有時會有需求要停用或是再度啟用「觸發程序」，可以使用 DISABLE TRIGGER 與 ENABLE TRIGGER 的 T-SQL 陳述式來完成。

請參考以下的範例程式碼：
以伺服器層級為例：

-- EX1. DDL 「觸發程序」：伺服器層級
--01 建立伺服器層級的 DDL 觸發程序
USE master
GO
IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'trSrv01')
DROP TRIGGER [trSrv01] ON ALL SERVER
GO
CREATE TRIGGER trSrv01
 ON ALL SERVER
 FOR DDL_LOGIN_EVENTS
AS
 SELECT N' 執行伺服器層級的 DDL 觸發程序'
GO

--02 查詢伺服器層級的 DDL 觸發程序的相關狀態
SELECT st.name N'觸發程序',  ste.type_desc N'事件描述', ste.event_group_type_desc N'事件群組的描述',  st.type_desc N'物件類型', st.is_disabled N'是否已停用', st.create_date N'建立日期', st.modify_date N'修改日期'
FROM sys.server_triggers st INNER JOIN sys.server_trigger_events ste
 ON st.object_id=ste.object_id

--03 停用此伺服器層級的 DDL 觸發程序
DISABLE TRIGGER trSrv01
ON ALL SERVER
GO

--04 啟用此伺服器層級的 DDL 觸發程序
ENABLE TRIGGER trSrv01
ON ALL SERVER
GO

--05 刪除此伺服器層級的 DDL 觸發程序
IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'trSrv01')
DROP TRIGGER [trSrv01] ON ALL SERVER
GO

以資料庫層級為例：

-- EX2. DDL 「觸發程序」：資料庫層級
--01 建立資料庫層級的 DDL 觸發程序

USE Northwind -- 先切換到指定的資料庫
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'trDB01')DROP TRIGGER [trDB01] ON DATABASE
GO
CREATE TRIGGER trDB01
 ON DATABASE
 FOR CREATE_TABLE
AS
  SELECT N' 執行資料庫層級的 DDL 觸發程序'
GO

--02 查詢資料庫層級的 DDL 觸發程序的相關狀態
SELECT st.name N'觸發程序',  ste.type_desc N'事件描述', ste.event_group_type_desc N'事件群組的描述', 
 st.type_desc N'物件類型', st.is_disabled N'是否已停用', st.create_date N'建立日期', st.modify_date N'修改日期'
FROM sys.triggers st INNER JOIN sys.trigger_events ste
 ON st.object_id=ste.object_id

--03 停用此資料庫層級的 DDL 觸發程序
DISABLE TRIGGER trDB01
ON DATABASE
GO

--04 啟用此資料庫層級的 DDL 觸發程序
ENABLE TRIGGER trDB01
ON DATABASE
GO

--05 刪除此資料庫層級的 DDL 觸發程序
IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'trDB01')DROP TRIGGER [trDB01] ON DATABASE
GO

以 DML 觸發程序為例：

-- EX3. DML 「觸發程序」
--01 建立 DML 「觸發程序」
USE Northwind
GO
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trOrders01]'))
DROP TRIGGER [dbo].[trOrders01]
GO
CREATE TRIGGER trOrders01
 ON Orders
 AFTER INSERT
AS
  SELECT N' 執行DML 觸發程序'
GO

--02 查詢 DML 觸發程序的相關狀態
SELECT OBJECT_NAME(t.parent_id) N'資料表', t.name N'觸發程序名稱', parent_class_desc N'觸發程序父類別的描述', 
 t.type_desc N'物件類型的描述',  tEV.type_desc '引發觸發程序的每個事件', is_instead_of_trigger N'是否為 INSTEAD OF 觸發程序',
 t.is_disabled  N'是否已停用'
FROM sys.triggers t INNER JOIN sys.trigger_events tEV
ON t.object_id = tEV.object_id

--03 停用此DML 觸發程序
DISABLE TRIGGER trOrders01
 ON Orders
GO

--04 啟用此資料庫層級的 DDL 觸發程序
ENABLE TRIGGER trOrders01
 ON Orders
GO

--05 刪除此資料庫層級的 DDL 觸發程序
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trOrders01]'))
DROP TRIGGER [dbo].[trOrders01]
GO