SQL Server 觸發器實作資料異動稽核日誌 (DML Audit Log)

應用場景

資料庫層面記錄 INSERT/UPDATE/DELETE 操作，獨立於應用程式，確保完整稽核軌跡。適用於合規、安全調查、資料異動追蹤。

建立稽核表

CREATE TABLE dbo.AuditLog (
    AuditID       BIGINT IDENTITY(1,1) PRIMARY KEY,
    TableName     SYSNAME NOT NULL,
    Operation     CHAR(1) NOT NULL CHECK (Operation IN ('I','U','D')), -- Insert/Update/Delete
    RowID         VARCHAR(100), -- 主鍵值或 ROWID
    ChangedBy     SYSNAME NOT NULL DEFAULT SUSER_SNAME(),
    ChangedAt     DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
    OldValues     XML, -- 異動前資料
    NewValues     XML, -- 異動後資料
    AppName       NVARCHAR(128) DEFAULT APP_NAME(),
    ClientHost    NVARCHAR(128) DEFAULT HOST_NAME()
);

觸發器範本 (以 Orders 表為例)

CREATE TRIGGER trg_Orders_Audit
ON dbo.Orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @op CHAR(1) = CASE 
        WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN 'U'
        WHEN EXISTS(SELECT * FROM inserted) THEN 'I'
        ELSE 'D' END;
    
    -- 組合 XML (排除大型欄位如 image/varbinary(max))
    INSERT INTO dbo.AuditLog (TableName, Operation, RowID, OldValues, NewValues)
    SELECT 
        'Orders',
        @op,
        COALESCE(i.OrderID, d.OrderID),
        (SELECT * FROM deleted d2 WHERE d2.OrderID = COALESCE(i.OrderID, d.OrderID) FOR XML AUTO, TYPE),
        (SELECT * FROM inserted i2 WHERE i2.OrderID = COALESCE(i.OrderID, d.OrderID) FOR XML AUTO, TYPE)
    FROM inserted i
    FULL JOIN deleted d ON i.OrderID = d.OrderID;
END;

進階：通用觸發器產生腳本

-- 為所有使用者表自動產生觸發器
SELECT 'CREATE TRIGGER trg_' + TABLE_NAME + '_Audit
ON ' + TABLE_SCHEMA + '.' + TABLE_NAME + '
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @op CHAR(1) = CASE 
        WHEN EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) THEN ''U''
        WHEN EXISTS(SELECT * FROM inserted) THEN ''I''
        ELSE ''D'' END;
    INSERT INTO dbo.AuditLog (TableName, Operation, RowID, OldValues, NewValues)
    SELECT 
        ''' + TABLE_NAME + ''',
        @op,
        COALESCE(i.' + COLUMN_NAME + ', d.' + COLUMN_NAME + '),
        (SELECT * FROM deleted d2 WHERE d2.' + COLUMN_NAME + ' = COALESCE(i.' + COLUMN_NAME + ', d.' + COLUMN_NAME + ') FOR XML AUTO, TYPE),
        (SELECT * FROM inserted i2 WHERE i2.' + COLUMN_NAME + ' = COALESCE(i.' + COLUMN_NAME + ', d.' + COLUMN_NAME + ') FOR XML AUTO, TYPE)
    FROM inserted i
    FULL JOIN deleted d ON i.' + COLUMN_NAME + ' = d.' + COLUMN_NAME + ';
END;'
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE t.TABLE_TYPE = 'BASE TABLE' 
  AND t.TABLE_NAME <> 'AuditLog'
  AND COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') = 1
  AND c.DATA_TYPE NOT IN ('xml', 'image', 'text', 'ntext', 'varbinary', 'geometry', 'geography')
GROUP BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

效能與維護考量

• 觸發器在交易內執行，複雜邏輯會增加鎖定時間 → 保持輕量、避免跨資料庫/網路呼叫
• 大量批次異動時觸發器逐列執行效能差 → 考慮 CDC (Change Data Capture) 或 Temporal Tables
• 稽核表快速增長 → 分區表、定期歸檔、壓縮
• XML 儲存靈活但查詢較慢 → 關鍵欄位另建索引欄、或用 JSON (SQL 2016+)

現代替代方案

PHP 整合查詢稽核日誌

prepare('
    SELECT TOP 50 AuditID, TableName, Operation, RowID, ChangedBy, ChangedAt,
           OldValues.value(''(/row/@OrderID)[1]'', ''int'') AS OrderID
    FROM AuditLog
    WHERE TableName = ? AND ChangedAt >= DATEADD(day, -7, GETDATE())
    ORDER BY ChangedAt DESC
');
$stmt->execute(['Orders']);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    echo "{$row['ChangedAt']} | {$row['Operation']} | OrderID: {$row['OrderID']} | By: {$row['ChangedBy']}\n";
}

1. 上一篇文章： PHP 4 遷移至 PHP 5 完整指南：物件模型、介面、存取控制與建構子
2. 下一篇文章： PHP GD 繪製 3D 餅圖完整程式碼教學

PHP 綜合知識

ThinkPHP 3.2 驗證碼完整實作：生成、刷新與驗證全流程

ThinkPHP 內建驗證碼支援概覽 ThinkPHP 內建

PHP 實作萬年曆完整教學與程式碼解析

PHP 萬年曆核心邏輯解析 要使用 PHP 實作萬年曆功能，

PHP 陣列依序替換字串匹配項的多種技巧

情境：SQL 範本中的日期佔位符替換 假設有一段 SQL 字

《PHP邊學邊教》Apache+PHP環境配置（下篇）：MySQL 5.0 安裝配置與 phpMyAdmin 部署全圖解

MySQL 5.0 安裝向導 運行 mysql-essent