DDL 觸發器 vs DML 觸發器
| 特性 | DML 觸發器 | DDL 觸發器 |
|---|
| 觸發事件 | INSERT/UPDATE/DELETE | CREATE/ALTER/DROP 等 DDL |
| 作用域 | 表/視圖 | DATABASE / ALL SERVER |
| 事件數據 | inserted/deleted 表 | EVENTDATA() 返回 XML |
| 事務控制 | 可回滾 DML | 可回滾 DDL（隱式事務） |
核心對象：EVENTDATA() XML 結構
CREATE_TABLE
2024-01-15T10:30:00.123
52
sa
TestDB
dbo
MyTable
TABLE
...
CREATE TABLE MyTable (ID INT, Name VARCHAR(100))
實戰範例：禁止核心對象變更
CREATE TRIGGER TR_DDL_ProtectCoreObjects
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS
BEGIN
DECLARE @EventData XML = EVENTDATA();
DECLARE @Cmd NVARCHAR(MAX) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');
DECLARE @ObjName SYSNAME = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME');
DECLARE @ObjType SYSNAME = @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME');
DECLARE @Login SYSNAME = @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME');
-- 白名單機制：允許特定用戶/角色/對象前綴
IF @Login IN ('deploy_user', 'dbo') RETURN; -- 部署賬號放行
IF @ObjName LIKE 'tmp_%' OR @ObjName LIKE 'stg_%' RETURN; -- 臨時/階段表放行
-- 記錄審計
INSERT INTO dbo.DDL_AuditLog (EventTime, LoginName, EventType, ObjectName, ObjectType, CommandText)
VALUES (GETDATE(), @Login, @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME'), @ObjName, @ObjType, @Cmd);
-- 回滾並報錯
RAISERROR('禁止未授權的 DDL 操作：%s %s', 16, 1, @ObjType, @ObjName);
ROLLBACK;
END;
進階應用場景
- 命名規範強制： 解析
ObjectName，不符合規範（如無前綴、大小寫）直接回滾。
- 架構治理： 禁止在
dbo 以外架構建表、禁止 DROP INDEX 等高危操作。
- 自動文檔生成： 觸發器寫入變更日誌，驅動文檔同步流程。
- 跨資料庫/實例審計：
ON ALL SERVER 範圍捕獲實例級 DDL（如 CREATE DATABASE、ALTER LOGIN）。
性能與維護提示
- 觸發器邏輯極簡，避免複雜查詢/外部調用阻塞 DDL。
- 使用
TRY...CATCH 包裹審計寫入，防止審計失敗導致合法 DDL 回滾。
- 定期歸檔
DDL_AuditLog，避免日誌表膨脹。
- 結合
Database Audit Specification (SQL 2008+) 實現更細粒度、低開銷的審計。
