SQL Server 2005 建立 DDL 觸發器實作稽核資料庫結構變更

前言

SQL Server 2005 引入資料定義語言 (DDL) 觸發器，使資料庫管理員能在 `CREATE TABLE`、`ALTER VIEW` 等結構變更語句執行時自動攔截並記錄事件，大幅簡化結構變更稽核的實作難度。本文以 AdventureWorks 範例資料庫為例，示範建立稽核資料表與 DDL 觸發器的完整流程。

建立 DDL 稽核資料表

稽核表需至少包含一個 XML 欄位儲存事件資料，並設定主鍵。每次 DDL 事件觸發即寫入一筆記錄。範例腳本：

CREATE TABLE dbo.EventLog
(
    EventID INT PRIMARY KEY IDENTITY,
    EventInstance XML NOT NULL
);
GO

• EventID：身分識別欄位作為主鍵。
• EventInstance：XML 型別，儲存 `EVENTDATA()` 回傳的事件詳細資訊（事件類型、物件名稱、執行時間、TSQL 指令等）。

建立 DDL 觸發器

以下觸發器在資料庫層級攔截所有 DDL 事件並寫入稽核表：

CREATE TRIGGER LogEvents
ON DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    INSERT INTO dbo.EventLog (EventInstance)
    VALUES (EVENTDATA());
END;
GO

程式碼逐行解析

1. CREATE TRIGGER LogEvents：命名觸發器。
2. ON DATABASE：指定資料庫層級觸發器；若需伺服器層級改用 ON ALL SERVER。
3. AFTER DDL_DATABASE_LEVEL_EVENTS：AFTER 表示事件成功完成後才觸發（相對於 FOR 即時觸發）。DDL_DATABASE_LEVEL_EVENTS 為事件群組，涵蓋資料庫層級所有 DDL 事件；亦可指定單一事件類型（如 `CREATE_TABLE`、`ALTER_PROCEDURE`），多個事件以逗號分隔。詳細事件清單請參閱線上叢書「CREATE TRIGGER (Transact-SQL)」。
4. AS ... INSERT ... VALUES (EVENTDATA())：觸發器本體，呼叫系統函數 `EVENTDATA()` 取得 XML 格式事件資料並寫入稽核表。

驗證與測試

建立完成後，嘗試執行 `CREATE TABLE`、`ALTER VIEW` 等 DDL 指令，隨後查詢 dbo.EventLog 即可看到對應的 XML 事件記錄。進一步可撰寫 XQuery 解析 XML 欄位，產出可讀性更高的稽核報表。

延伸應用

• 搭配 DDL_SERVER_LEVEL_EVENTS 監控伺服器層級物件變更（登入、端點、資料庫建立/刪除等）。
• 結合 Service Broker 將事件非同步傳遞至中央稽核資料庫，實現多伺服器集中稽核。
• 於觸發器中加入條件判斷，僅記錄特定結構或特定使用者的變更，降低稽核資料量。

總結

DDL 觸發器配合 XML 資料型別，提供原生、低維護成本的結構變更稽核方案。透過事件群組彈性界定監控範圍，配合 `EVENTDATA()` 豐富的事件上下文資訊，滿足合規、變更管理、問題追溯等多元需求。