SQL Server 觸發器實戰：自動記錄 DML 操作日誌到事件檢視器與文本文件

前言：審計追蹤的輕量級實現

DBA 需向不同角色提供數據變更記錄：會計看 Excel、業務看報表、安全部門查事件日誌。本文示範在 SQL Server 中使用 DML Trigger 捕獲 INSERT/UPDATE/DELETE，自動寫入 Windows 事件日誌 與 文本文件，無需修改應用代碼。

環境準備

USE Northwind;
GO
CREATE TABLE dbo.TableForTrigger (
    TrackID   INT IDENTITY(1,1) PRIMARY KEY,
    LastName  VARCHAR(25),
    FirstName VARCHAR(25)
);
GO

步驟一：註冊自定義事件訊息

將訊息加入 master..sysmessages，@with_log = 'TRUE' 確保寫入事件日誌。

EXEC sp_addmessage 50005, 10, '%s', @with_log = 'TRUE';
GO

步驟二：建立 INSERT 觸發器（寫事件日誌）

CREATE TRIGGER trg_TableForTrigger_Ins
ON dbo.TableForTrigger
FOR INSERT
AS
BEGIN
    DECLARE @Msg VARCHAR(8000);
    -- 組裝：操作|表名|時間|欄位值
    SELECT @Msg = 'INSERTED | TableForTrigger | ' 
        + CONVERT(VARCHAR(20), GETDATE(), 120) + ' | '
        + CONVERT(VARCHAR(5), TrackID) + ', ' + LastName + ', ' + FirstName
    FROM inserted;  -- 虛擬表，僅含本次新增行
    
    RAISERROR(50005, 10, 1, @Msg) WITH LOG;  -- 寫入 Application Event Log
END;
GO

測試

INSERT INTO dbo.TableForTrigger (LastName, FirstName) VALUES ('Doe', 'John');

打開「事件檢視器 → Windows 日誌 → Application」，來源 MSSQLSERVER，事件 ID 50005，即可見到完整記錄。

步驟三：進階 —— 寫入文本文件（需 xp_cmdshell）

前置條件：

• EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;（需 sysadmin）。
• 執行觸發器的用戶對目標文件夾有寫入權限（建議由中間層服務帳號統一寫入，避免 C/S 架構權限分散）。

ALTER TRIGGER trg_TableForTrigger_Ins
ON dbo.TableForTrigger
FOR INSERT
AS
BEGIN
    DECLARE @Msg VARCHAR(1000), @Cmd VARCHAR(2000);
    
    SELECT @Msg = 'INSERTED | TableForTrigger | ' 
        + CONVERT(VARCHAR(20), GETDATE(), 120) + ' | '
        + CONVERT(VARCHAR(5), TrackID) + ', ' + LastName + ', ' + FirstName
    FROM inserted;
    
    RAISERROR(50005, 10, 1, @Msg) WITH LOG;  -- 仍保留事件日誌
    
    SET @Cmd = 'echo ' + @Msg + ' >> C:\LogTest.log';
    EXEC master..xp_cmdshell @Cmd, NO_OUTPUT;
END;
GO

步驟四：處理多行 INSERT（游標逐行寫入）

批次 INSERT ... SELECT 會一次觸發，inserted 含多行。需游標拆解：

ALTER TRIGGER trg_TableForTrigger_Ins
ON dbo.TableForTrigger
FOR INSERT
AS
BEGIN
    DECLARE @Msg VARCHAR(1000), @Cmd VARCHAR(2000);
    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT 'INSERTED | TableForTrigger | ' 
            + CONVERT(VARCHAR(20), GETDATE(), 120) + ' | '
            + CONVERT(VARCHAR(5), TrackID) + ', ' + LastName + ', ' + FirstName
        FROM inserted;
    
    OPEN cur;
    FETCH NEXT FROM cur INTO @Msg;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        RAISERROR(50005, 10, 1, @Msg) WITH LOG;
        SET @Cmd = 'echo ' + @Msg + ' >> C:\LogTest.log';
        EXEC master..xp_cmdshell @Cmd, NO_OUTPUT;
        FETCH NEXT FROM cur INTO @Msg;
    END;
    CLOSE cur; DEALLOCATE cur;
END;
GO

測試多行

INSERT INTO dbo.TableForTrigger (LastName, FirstName)
SELECT LastName, FirstName FROM Employees;  -- Northwind 範例表

打開 C:\LogTest.log，每行一筆記錄，含時間戳與欄位值。

UPDATE / DELETE 觸發器模板

-- UPDATE：比對 inserted / deleted 虛擬表
CREATE TRIGGER trg_TableForTrigger_Upd
ON dbo.TableForTrigger
FOR UPDATE
AS
BEGIN
    DECLARE @Msg VARCHAR(1000);
    SELECT @Msg = 'UPDATED | TableForTrigger | ' 
        + CONVERT(VARCHAR(20), GETDATE(), 120) + ' | '
        + 'OLD: ' + CONVERT(VARCHAR(5), d.TrackID) + ', ' + d.LastName + ', ' + d.FirstName
        + ' -> NEW: ' + CONVERT(VARCHAR(5), i.TrackID) + ', ' + i.LastName + ', ' + i.FirstName
    FROM deleted d JOIN inserted i ON d.TrackID = i.TrackID;
    RAISERROR(50005, 10, 1, @Msg) WITH LOG;
END;

-- DELETE：只讀 deleted
CREATE TRIGGER trg_TableForTrigger_Del
ON dbo.TableForTrigger
FOR DELETE
AS
BEGIN
    DECLARE @Msg VARCHAR(1000);
    SELECT @Msg = 'DELETED | TableForTrigger | ' 
        + CONVERT(VARCHAR(20), GETDATE(), 120) + ' | '
        + CONVERT(VARCHAR(5), TrackID) + ', ' + LastName + ', ' + FirstName
    FROM deleted;
    RAISERROR(50005, 10, 1, @Msg) WITH LOG;
END;

效能與安全考量

• 高頻寫入表慎用：每秒 5000+ 事務時，xp_cmdshell 進程創建開銷巨大，建議改用 Service Broker 異步寫入或 CDC (Change Data Capture)。
• xp_cmdshell 在交易外執行，寫文件失敗不會回滾主事務。
• 入侵者繞過應用層直連資料庫修改，事件日誌仍會記錄（因觸發器在引擎層），但文本文件可能被竄改（需檔案系統審計）。
• 最佳實踐：事件日誌加入 EventID 流水號，定期對帳文本文件行數，發現差異即告警。

替代方案對比

結語

Trigger 寫日誌是「快速交付」的經典技巧，但高頻、高併發、高合規場景請轉向 CDC、Temporal Tables 或 Extended Events。務必評估 xp_cmdshell 風險，生產環境建議關閉，改用中間層服務帳號集中寫入審計存儲。

1. 上一篇文章： 平面文件導入 Oracle 實戰：SQL*Loader、Informatica ETL 與 Access 過渡方案
2. 下一篇文章： ASP 中存取 Oracle 大字段（CLOB/BLOB）完整解決方案

Oracle 資料庫基礎

Oracle 多欄橫向輸出技巧：單列資料轉多欄顯示實戰

需求場景：垂直資料橫向展開 常見報表需求：將單欄垂直資料（如

Oracle 10g 回收站機制揭秘：奇怪表名 BIN$ 來源與徹底清理指南

現象識別 查詢 USER_TABLES 發現大量奇怪表名：B

.NET 應用開發實戰：使用 ODP.NET 連接 Oracle 資料庫完整指南

為什麼選擇 ODP.NET？ .NET 應用連接 Oracl

Oracle EBS 多環境識別技巧：任務欄文字與介面顏色雙重區分

痛點 專案同時存在 4 套環境（開發、測試、UAT、生產），