SQL Server 觸發器完整語法與參數詳解：DML、DDL、Logon 三大類型

觸發器 是針對特定事件自動執行的特殊儲存程序。本文依官方文件整理 DML、DDL、Logon 三類觸發器的完整 CREATE TRIGGER 語法、參數說明與關鍵限制，供開發與審計參考。

一、DML Trigger (資料操作觸發器)

CREATE TRIGGER [schema_name.]trigger_name
ON { table | view }
[ WITH  [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME  }

 ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]
 ::= assembly_name.class_name.method_name

關鍵參數說明

魔術資料表：inserted / deleted

• 結構與觸發表相同，儲存變更前/後的列映像
• INSERT：inserted 有新列，deleted 空
• DELETE：deleted 有舊列，inserted 空
• UPDATE：deleted 舊值，inserted 新值
• 相容層級 ≥ 80 時，INSTEAD OF 觸發器可透過聯接 inserted 更新 text/ntext/image 欄位

二、DDL Trigger (結構定義觸發器)

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH  [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME  [ ; ] }

 ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]

範圍與事件

• ON DATABASE：目前資料庫範圍，捕獲 CREATE_TABLE, ALTER_PROCEDURE, DROP_VIEW 等
• ON ALL SERVER：實例範圍，捕獲 CREATE_DATABASE, ALTER_LOGIN, CREATE_LOGIN 等
• event_group：預定義事件群組，如 DDL_TABLE_EVENTS (含 CREATE/ALTER/DROP TABLE)、DDL_PROCEDURE_EVENTS 等
• 事件資訊透過 EVENTDATA() XML 函數取得 (見文章 72345)

三、Logon Trigger (登入觸發器)

CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH  [ ,...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME  [ ; ] }

 ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]

應用場景

• 限制特定登入的並發連線數
• 記錄登入稽核 (IP、時間、應用程式)
• 阻擋非授權 IP/時段登入
• 注意：Logon Trigger 失敗會導致登入失敗，邏輯必須極度健壯，建議用 TRY...CATCH 吞掉錯誤

四、CLR 觸發器 (EXTERNAL NAME)

• 組件必須已在資料庫註冊 (CREATE ASSEMBLY)
• 方法簽名：public static void MethodName()，無參數、無回傳值
• 類別名稱若含命名空間需用 [Namespace.ClassName] 或 "Namespace.ClassName" 括起
• 不支援巢狀類別

五、管理與除錯指令

-- 檢視觸發器定義
EXEC sp_helptext 'trigger_name';
SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('trigger_name');

-- 啟用/停用
ENABLE TRIGGER trigger_name ON TABLE;  -- 或 DATABASE / ALL SERVER
DISABLE TRIGGER trigger_name ON TABLE;

-- 刪除
DROP TRIGGER [schema_name.]trigger_name;  -- DML
DROP TRIGGER trigger_name ON DATABASE;    -- DDL Database
DROP TRIGGER trigger_name ON ALL SERVER;  -- DDL Server / Logon

-- 查看觸發器中繼資料
SELECT * FROM sys.triggers WHERE name = 'trigger_name';  -- DML
SELECT * FROM sys.server_triggers WHERE name = 'trigger_name'; -- DDL Server / Logon

六、最佳實務提醒

1. 避免巢狀觸發：sp_configure 'nested triggers', 0 或觸發器內 IF TRIGGER_NESTLEVEL() > 1 RETURN
2. 避免遞歸觸發：sp_configure 'recursive triggers', 0 或資料庫屬性關閉
3. 保持輕量：觸發器執行時間直接影響 DDL/DML 回應延遲，複雜邏輯改非同步 (Service Broker / 佇列表 + Agent 作業)
4. 權限最小化：使用 EXECUTE AS CALLER 或明確低權限帳號
5. 文件化：擴充屬性記錄用途、作者、建立日期、相依表

1. 上一篇文章： SQL Server 刪除資料庫使用者受阻：「主體擁有結構描述無法刪除」完整解決方案
2. 下一篇文章： SQL Server 批次授權儲存程序與函數：動態腳本自動化 GRANT EXECUTE/SELECT 權限

關於SqlServer

SQL Server 與 Oracle 鎖機制與死鎖處理的異同深度比較

鎖的核心目的 平台鎖的核心目標 Oracle保護物件不被並

SSMS 2008 無法檢視資料庫、錯誤 916 解決方案

問題現象 使用 SQL Server Management

Windows Server 2012 開啟防火牆 80 連接埠圖解教學

適用版本 Windows Server 2012 / 201

SQL Server 2012 FIRST_VALUE 函數實戰：分組首值、排序影響與 LAST_VALUE 對比

FIRST_VALUE 回傳視窗中第一列的指定運算式值，受