SQL Server 使用 DDL Trigger 稽核預存程序變更：完整實作範例

建立資料庫層級 DDL 觸發器，自動記錄預存程序的 CREATE、ALTER、DROP 操作，包含執行者、時間、事件類型與完整事件 XML，滿足合規稽核與變更追蹤需求。

1. 建立稽核資料表

CREATE TABLE AuditStoredProcedures (
    DatabaseName SYSNAME,
    ObjectName SYSNAME,
    LoginName SYSNAME,
    ChangeDate DATETIME DEFAULT GETDATE(),
    EventType SYSNAME,
    EventDataXml XML
);

2. 建立 DDL Trigger

CREATE TRIGGER dbtAuditStoredProcedures ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    DECLARE @eventdata XML = EVENTDATA();
    
    INSERT INTO AuditStoredProcedures (DatabaseName, ObjectName, LoginName, ChangeDate, EventType, EventDataXml)
    VALUES (
        @eventdata.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME'),
        @eventdata.value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
        @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME'),
        GETDATE(),
        @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME'),
        @eventdata
    );
END;

關鍵技術點

• ON DATABASE 範圍：僅限當前資料庫；若需伺服器層級改用 ON ALL SERVER。
• EVENTDATA() 函數：回傳觸發事件完整 XML，含 T-SQL 命令文本、物件定義、設定選項等豐富上下文。
• .value() XQuery 提取：安全轉型為強型別欄位，避免 XML 解析錯誤。
• 稽核表建議加入索引：CREATE INDEX IX_Audit_ChangeDate ON AuditStoredProcedures(ChangeDate);

擴充應用

• 擴展至 CREATE_TABLE、ALTER_TABLE、DROP_TABLE 等 DDL 事件。
• 整合 sys.sql_modules 對比定義異動內容（DIFF）。
• 搭配 Service Broker 或 Event Grid 異步通知監控系統。

權限需求

建立觸發器需 ALTER ANY DATABASE DDL TRIGGER 權限（通常由 db_owner 或 sysadmin 擁有）。