SQL Server 2005 DDL 觸發器：防範結構異動、稽核追蹤、合規管控實戰

SQL Server 2005 引入 DDL 觸發器，可在資料庫層級或伺服器層級攔截 CREATE/ALTER/DROP 等結構變更，實現防誤刪、稽核記錄、合規強制等需求，超越傳統 DML 觸發器範疇。

核心概念

• 作用範圍：ON DATABASE（單一資料庫）或 ON ALL SERVER（整個實例）。
• 事件群組：可針對單一事件（如 CREATE_TABLE）或群組（如 DDL_DATABASE_LEVEL_EVENTS）。
• 事件資料：EVENTDATA() 回傳 XML，含事件類型、物件名稱、TSQL 指令、登入用戶、時間等完整上下文。

實戰範例：禁止資料庫內建立/修改/刪除資料表與預存程序

-- 建立測試環境
CREATE DATABASE DDLTrTest;
GO
USE DDLTrTest;
GO
CREATE TABLE MyTable (ID INT, Name VARCHAR(100));
INSERT INTO MyTable VALUES (1,'A'),(2,'B');
GO
CREATE PROC usp_QueryMyTable AS SELECT * FROM MyTable;
GO

-- 定義 DDL 觸發器
CREATE TRIGGER TRG_Block_DDL_Table_Proc
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
    CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    -- 記錄嘗試指令
    DECLARE @Cmd NVARCHAR(MAX) = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');
    INSERT INTO DDL_AuditLog (EventTime, LoginName, EventType, CommandText)
    VALUES (GETDATE(), SYSTEM_USER, EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME'), @Cmd);

    -- 阻止並回滾
    PRINT '禁止建立/修改/刪除資料表與預存程序！';
    ROLLBACK;
END;
GO

測試效果

ALTER TABLE MyTable ADD X INT;
-- 結果：
-- (1 row(s) affected)
-- 禁止建立/修改/刪除資料表與預存程序！
-- Msg 3609, Level 16, State 2, Line 1
-- 交易在觸發器中結束。批次已中止。

DROP TABLE MyTable;
-- 同樣被阻止並回滾

啟用/停用觸發器

DISABLE TRIGGER TRG_Block_DDL_Table_Proc ON DATABASE;
ENABLE TRIGGER TRG_Block_DDL_Table_Proc ON DATABASE;

伺服器層級範例：防刪除資料庫

CREATE TRIGGER TRG_Block_Drop_DB
ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    PRINT '禁止刪除資料庫！';
    ROLLBACK;
END;

進階應用場景

效能與限制提醒

• DDL 觸發器同步執行，複雜邏輯會拉長 DDL 指令回應時間，稽核寫入建議極簡單（INSERT 單表）。
• 避免在觸發器中執行跨資料庫、網路呼叫、長時間運算。
• EVENTDATA() 僅在觸發器內有效，不可外傳。
• 伺服器層級觸發器需 CONTROL SERVER 權限建立。

DDL 觸發器是資料庫治理的利器，善用它將「事後追查」轉為「事前阻擋與自動留痕」。