在現代企業的資訊系統中，資料庫扮演著核心角色，儲存著業務運作的關鍵資訊。隨著資料隱私法規的日益嚴格，例如台灣的個人資料保護法、歐盟的 GDPR（General Data Protection Regulation），以及各產業特定的合規要求，企業必須建立完善的資料異動追蹤機制。無論是為了滿足法規稽核、追查系統問題、調查資安事件，還是進行業務分析，完整的資料變更歷史記錄都是不可或缺的基礎設施。
資料庫觸發器（Database Trigger）作為一種強大的資料庫物件，提供了實現自動化審計追蹤的理想解決方案。觸發器會在特定的資料庫事件發生時自動執行預先定義的 SQL 程式碼，這種機制讓審計記錄的產生完全自動化，不需要在應用程式層面進行額外的程式設計，也不會因為開發人員的疏忽而遺漏重要的異動記錄。相較於在應用程式中手動插入審計記錄，觸發器具有更高的可靠性與一致性，因為它直接在資料庫層面運作，無法被輕易繞過。
然而，觸發器的設計與實作並非簡單的工作，需要深入理解資料庫的運作機制、審計需求的特性，以及效能最佳化的技巧。設計不當的觸發器可能會嚴重影響資料庫效能，產生大量無用的審計記錄，或是遺漏關鍵的變更資訊。本文將從實務角度出發，完整介紹如何設計與實作企業級的資料庫審計觸發器系統。我們將以應付帳款管理系統為例，逐步建立處理不同資料操作類型（INSERT、UPDATE、DELETE）的觸發器，並探討如何設計精細化的審計機制，只記錄實際發生變化的欄位，以提升系統效能與審計記錄的可用性。
資料庫觸發器的核心概念與審計應用
資料庫觸發器是一種特殊的預存程序（Stored Procedure），它與一般的預存程序最大的差異在於觸發器不需要手動呼叫，而是由資料庫管理系統在特定事件發生時自動執行。這種自動化的特性使得觸發器特別適合用於實現資料一致性檢查、自動更新衍生欄位、維護參照完整性，以及最重要的審計追蹤功能。
觸發器的運作機制建立在事件驅動的概念之上。當使用者或應用程式對資料庫執行資料操縱語言（Data Manipulation Language，DML）操作時，例如 INSERT（新增）、UPDATE（更新）或 DELETE（刪除），資料庫管理系統會檢查是否有針對該操作建立的觸發器。如果存在相應的觸發器，系統會在指定的時機執行觸發器中定義的 SQL 程式碼。
觸發器的執行時機可以是操作發生之前（BEFORE）或之後（AFTER）。BEFORE 觸發器在資料實際寫入資料庫之前執行，常用於資料驗證或自動修改即將寫入的資料。AFTER 觸發器則在資料成功寫入後執行，適合用於審計記錄、同步更新其他表格等不需要修改當前操作資料的場景。在審計應用中，我們通常使用 AFTER 觸發器，確保只有成功完成的操作才會被記錄，避免記錄最終未成功執行的操作嘗試。
在觸發器的執行過程中，資料庫系統提供了特殊的虛擬表格來存取觸發事件相關的資料。在 MySQL 中，這些虛擬表格透過 OLD 與 NEW 關鍵字來存取。NEW 代表即將被插入或更新後的新資料，可在 INSERT 與 UPDATE 觸發器中使用。OLD 則代表即將被刪除或更新前的舊資料，可在 DELETE 與 UPDATE 觸發器中使用。透過這兩個虛擬表格，觸發器可以存取變更前後的完整資料狀態，這對於實現詳細的審計追蹤至關重要。
以下圖表展示了觸發器在資料庫審計系統中的完整運作流程：
@startuml
!define PLANTUML_FORMAT svg
!theme _none_
skinparam dpi auto
skinparam shadowing false
skinparam linetype ortho
skinparam roundcorner 5
skinparam defaultFontName "Microsoft JhengHei UI"
skinparam defaultFontSize 14
skinparam minClassWidth 100
actor "使用者或\n應用程式" as User
participant "資料庫\n管理系統" as DBMS
database "業務表格\n(payable)" as BizTable
participant "觸發器\n執行引擎" as Trigger
database "審計表格\n(payable_audit)" as AuditTable
User -> DBMS : 執行 DML 操作\n(INSERT/UPDATE/DELETE)
activate DBMS
DBMS -> BizTable : 執行資料變更
activate BizTable
BizTable --> DBMS : 變更成功
deactivate BizTable
DBMS -> Trigger : 自動啟動\nAFTER 觸發器
activate Trigger
note right of Trigger
觸發器可存取:
• NEW (新資料)
• OLD (舊資料)
• USER() (當前使用者)
• NOW() (當前時間)
end note
Trigger -> AuditTable : 插入審計記錄
activate AuditTable
note right of AuditTable
審計記錄包含:
• 操作時間
• 執行使用者
• 操作類型
• 變更內容
• 新舊值對比
end note
AuditTable --> Trigger : 記錄成功
deactivate AuditTable
Trigger --> DBMS : 觸發器執行完成
deactivate Trigger
DBMS --> User : 操作完成
deactivate DBMS
@enduml在設計審計系統時，我們需要仔細考慮要記錄哪些資訊。一個完整的審計記錄應該能夠回答以下關鍵問題：誰在什麼時間對哪筆資料做了什麼操作，以及變更的具體內容是什麼。這些資訊對於追蹤問題、進行安全調查與滿足法規要求都至關重要。此外，審計記錄的格式也需要仔細設計，既要包含足夠的詳細資訊以供後續分析使用，又要保持良好的可讀性以便人工審查。
建立審計追蹤的資料庫架構
在實作觸發器之前，我們需要先建立完善的資料庫架構，包含業務表格與審計表格。業務表格儲存實際的營運資料，而審計表格則負責記錄所有對業務表格的異動操作。審計表格的設計必須能夠支援各種查詢需求，包括按時間範圍查詢、按使用者查詢、按操作類型查詢，以及追蹤特定記錄的完整變更歷史。
我們以應付帳款管理系統為例來說明審計系統的架構設計。應付帳款表格儲存公司對供應商的付款資訊，包含供應商名稱、應付金額、服務項目、到期日期與付款狀態等欄位。這類財務資料通常具有高度的敏感性，需要詳細的異動追蹤以滿足會計準則與稽核要求。審計表格則需要能夠完整記錄每一次的資料變更，包含變更時間、執行變更的使用者、變更類型，以及變更前後的資料內容。
在設計審計表格時，我們採用靈活的欄位設計，同時包含結構化的欄位（如操作時間、使用者、操作類型）與非結構化的欄位（如變更描述、舊值、新值）。結構化欄位便於建立索引與執行高效查詢，而非結構化欄位則提供詳細的變更內容記錄。我們使用 TEXT 資料型態來儲存 JSON 格式的資料變更記錄，這種方式既保持了彈性，又便於後續使用 JSON 函式進行解析與查詢。
-- ============================================================
-- 建立業務表格：應付帳款管理
-- ============================================================
-- 此表格儲存公司對供應商的應付帳款資訊
-- 這是我們需要進行完整審計追蹤的核心業務表格
CREATE TABLE payable (
-- 主鍵：應付帳款的唯一識別碼
-- 使用 AUTO_INCREMENT 自動產生遞增的 ID
payable_id INT PRIMARY KEY AUTO_INCREMENT,
-- 供應商公司名稱
-- 設定為 NOT NULL 確保每筆記錄都有明確的供應商資訊
-- VARCHAR(100) 提供足夠的長度儲存公司名稱
company VARCHAR(100) NOT NULL,
-- 應付金額
-- DECIMAL(10, 2) 確保金額計算的精確性
-- 最大支援 99,999,999.99 的金額
amount DECIMAL(10, 2) NOT NULL,
-- 服務項目或採購內容說明
-- 允許 NULL 以因應某些情況下可能沒有詳細說明
service VARCHAR(200),
-- 付款到期日期
-- 用於追蹤付款時效與逾期情況
due_date DATE,
-- 付款狀態
-- 可能的值：pending（待付款）、approved（已核准）、paid（已付款）、cancelled（已取消）
-- 預設值設定為 'pending' 表示新建立的應付帳款處於待處理狀態
status VARCHAR(20) DEFAULT 'pending',
-- 記錄建立時間
-- 使用 CURRENT_TIMESTAMP 自動記錄資料建立時的時間戳記
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- 記錄最後更新時間
-- ON UPDATE CURRENT_TIMESTAMP 確保每次更新時自動更新此欄位
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 建立索引以提升查詢效能
-- 這些索引支援常見的查詢模式
INDEX idx_company (company),
INDEX idx_due_date (due_date),
INDEX idx_status (status),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='應付帳款主表，儲存公司對供應商的應付款項資訊';
-- ============================================================
-- 建立審計表格：應付帳款異動記錄
-- ============================================================
-- 此表格透過觸發器自動記錄所有對 payable 表格的異動操作
-- 提供完整的審計追蹤能力以滿足合規與稽核需求
CREATE TABLE payable_audit (
-- 主鍵：審計記錄的唯一識別碼
audit_id INT PRIMARY KEY AUTO_INCREMENT,
-- 異動發生的精確時間
-- 使用 DATETIME 而非 TIMESTAMP 以避免 2038 年問題
-- NOT NULL 確保每筆記錄都有明確的時間戳記
audit_datetime DATETIME NOT NULL,
-- 執行異動操作的資料庫使用者
-- 記錄格式為 'username@hostname'
-- 這個資訊對於追蹤責任歸屬至關重要
audit_user VARCHAR(100) NOT NULL,
-- 異動操作的類型
-- 可能的值：INSERT（新增）、UPDATE（更新）、DELETE（刪除）
audit_action VARCHAR(20) NOT NULL,
-- 異動內容的人類可讀描述
-- 提供簡潔的摘要資訊，便於快速瀏覽審計記錄
audit_change TEXT NOT NULL,
-- 變更前的完整資料（JSON 格式）
-- 用於 UPDATE 與 DELETE 操作
-- 儲存被修改或刪除前的完整資料狀態
old_values TEXT,
-- 變更後的完整資料（JSON 格式）
-- 用於 INSERT 與 UPDATE 操作
-- 儲存新增或修改後的完整資料狀態
new_values TEXT,
-- 被異動的應付帳款記錄 ID
-- 用於關聯到原始的業務記錄
-- 允許 NULL 以因應某些特殊情況（如批次刪除）
payable_id INT,
-- 額外的中繼資料欄位（選用）
-- 可用於記錄應用程式版本、IP 位址等資訊
metadata JSON,
-- 建立索引以支援各種查詢模式
-- 這些索引對於審計報表的效能至關重要
-- 支援按時間範圍查詢
INDEX idx_audit_datetime (audit_datetime),
-- 支援按使用者查詢
INDEX idx_audit_user (audit_user),
-- 支援按操作類型查詢
INDEX idx_audit_action (audit_action),
-- 支援查詢特定記錄的完整變更歷史
INDEX idx_payable_id (payable_id),
-- 複合索引支援常見的組合查詢
INDEX idx_payable_datetime (payable_id, audit_datetime),
INDEX idx_user_datetime (audit_user, audit_datetime),
INDEX idx_action_datetime (audit_action, audit_datetime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='應付帳款審計記錄表，自動記錄所有對 payable 表格的異動操作';
-- ============================================================
-- 建立審計表格的分區（選用，適用於大量資料場景）
-- ============================================================
-- 按月份分區可以提升查詢效能並簡化舊資料的歸檔作業
-- 這個設定需要在建立表格時指定，這裡僅作為參考範例
-- ALTER TABLE payable_audit
-- PARTITION BY RANGE (YEAR(audit_datetime) * 100 + MONTH(audit_datetime)) (
-- PARTITION p202401 VALUES LESS THAN (202402),
-- PARTITION p202402 VALUES LESS THAN (202403),
-- PARTITION p202403 VALUES LESS THAN (202404),
-- PARTITION p202404 VALUES LESS THAN (202405),
-- PARTITION pmax VALUES LESS THAN MAXVALUE
-- );
在實務應用中，審計表格的資料量可能會隨著時間快速增長，特別是在高頻率異動的系統中。因此，我們需要考慮資料保留政策與歸檔策略。一般而言，可以設定保留最近一到兩年的詳細審計記錄在線上資料庫中，而較舊的記錄則可以歸檔到冷儲存系統或進行彙總後保留。分區表格是管理大量審計資料的有效技術，它允許我們按時間範圍將資料分散到不同的物理分區中，既能提升查詢效能，也能簡化資料維護作業。
實作 INSERT 操作的審計觸發器
INSERT 觸發器負責記錄新資料的建立操作。當使用者或應用程式向業務表格插入新記錄時，觸發器會自動在審計表格中建立對應的審計記錄。INSERT 觸發器相對簡單，因為我們只需要記錄新增的資料內容，不需要處理舊資料的比對。
在設計 INSERT 觸發器時，我們需要決定要記錄哪些資訊。除了基本的時間戳記、使用者與操作類型外，我們還需要記錄新增資料的完整內容。為了提供良好的可讀性，我們同時儲存人類可讀的文字描述與結構化的 JSON 資料。文字描述便於快速瀏覽與人工審查，而 JSON 資料則提供完整的資料內容，便於程式化分析與資料恢復。
觸發器中使用的 USER() 函式會回傳當前連線到資料庫的使用者名稱，格式為 ‘username@hostname’。這個資訊對於追蹤操作來源非常重要。NOW() 函式則回傳當前的日期時間，提供精確的時間戳記。在觸發器中，我們可以透過 NEW 關鍵字存取即將被插入的新資料，NEW.column_name 語法可以取得特定欄位的值。
-- ============================================================
-- 建立 INSERT 審計觸發器
-- ============================================================
-- 此觸發器會在 payable 表格成功插入新資料後自動執行
-- 記錄新增操作的完整資訊到審計表格中
-- 變更 SQL 陳述式的結束符號
-- 這是必要的，因為觸發器本體中包含多個 SQL 陳述式
-- 需要使用分號作為內部陳述式的分隔符號
DELIMITER //
CREATE TRIGGER tr_payable_insert_audit
-- 指定觸發時機：AFTER INSERT
-- AFTER 確保只有成功插入的資料才會被記錄
-- 如果插入操作失敗（例如違反約束條件），觸發器不會執行
AFTER INSERT ON payable
-- FOR EACH ROW 表示對每一筆插入的資料都執行觸發器
-- 即使是批次插入多筆資料，也會為每筆資料分別執行
FOR EACH ROW
BEGIN
-- ============================================================
-- 宣告區塊：定義觸發器內部使用的變數
-- ============================================================
-- 用於儲存新資料的 JSON 格式字串
DECLARE v_new_values TEXT;
-- 用於儲存人類可讀的變更描述
DECLARE v_change_description TEXT;
-- ============================================================
-- 建構新增資料的 JSON 格式記錄
-- ============================================================
-- 使用 CONCAT 函式組合 JSON 字串
-- 這種手動建構 JSON 的方式在 MySQL 5.7 之前的版本中很常見
-- MySQL 5.7+ 可以使用 JSON_OBJECT 函式更簡潔地建構 JSON
SET v_new_values = CONCAT(
'{',
'"payable_id": ', NEW.payable_id, ', ',
'"company": "', REPLACE(NEW.company, '"', '\\"'), '", ', -- 跳脫雙引號避免 JSON 格式錯誤
'"amount": ', NEW.amount, ', ',
'"service": "', IFNULL(REPLACE(NEW.service, '"', '\\"'), ''), '", ',
'"due_date": "', IFNULL(NEW.due_date, ''), '", ',
'"status": "', NEW.status, '", ',
'"created_at": "', NEW.created_at, '"',
'}'
);
-- ============================================================
-- 建構人類可讀的變更描述
-- ============================================================
-- 這段描述會出現在審計報表中，提供快速瀏覽的資訊
SET v_change_description = CONCAT(
'新增應付帳款記錄，ID: ', NEW.payable_id, '，',
'供應商: ', NEW.company, '，',
'金額: NT$', FORMAT(NEW.amount, 2), '，',
'服務項目: ', IFNULL(NEW.service, '（無說明）'), '，',
'到期日: ', IFNULL(NEW.due_date, '（未設定）'), '，',
'狀態: ', NEW.status
);
-- ============================================================
-- 插入審計記錄到審計表格
-- ============================================================
INSERT INTO payable_audit (
audit_datetime, -- 異動時間
audit_user, -- 執行異動的使用者
audit_action, -- 操作類型
audit_change, -- 變更描述
old_values, -- 舊值（INSERT 操作為 NULL）
new_values, -- 新值（JSON 格式）
payable_id -- 關聯的記錄 ID
)
VALUES (
NOW(), -- 使用當前時間
USER(), -- 使用當前資料庫使用者
'INSERT', -- 操作類型固定為 INSERT
v_change_description, -- 使用前面建構的描述
NULL, -- INSERT 操作沒有舊值
v_new_values, -- 使用前面建構的 JSON 資料
NEW.payable_id -- 新插入資料的 ID
);
-- ============================================================
-- 觸發器執行完成
-- ============================================================
-- 如果審計記錄插入成功，觸發器正常結束
-- 如果插入失敗（例如審計表格有問題），整個交易會回滾
-- 這確保了業務資料與審計資料的一致性
END //
-- 恢復預設的 SQL 陳述式結束符號
DELIMITER ;
-- ============================================================
-- 觸發器建立完成後的驗證
-- ============================================================
-- 可以使用以下指令查看觸發器是否成功建立
-- SHOW TRIGGERS LIKE 'payable';
-- SHOW CREATE TRIGGER tr_payable_insert_audit;
在實作觸發器時，我們需要特別注意錯誤處理。如果觸發器執行過程中發生錯誤（例如審計表格不存在、磁碟空間不足），整個交易會回滾，包括業務資料的插入操作也會被取消。這種機制確保了業務資料與審計資料的一致性，但也意味著觸發器必須具有高可靠性。在生產環境中，我們需要定期監控觸發器的執行狀態，確保審計表格有足夠的儲存空間，並且相關的索引保持健康狀態。
此外，我們在建構 JSON 字串時需要特別處理特殊字元，例如雙引號需要使用反斜線跳脫，避免破壞 JSON 格式。在 MySQL 5.7 及更新版本中，可以使用內建的 JSON 函式（如 JSON_OBJECT、JSON_ARRAY）來更安全地建構 JSON 資料，這些函式會自動處理特殊字元的跳脫。
實作 DELETE 操作的審計觸發器
DELETE 觸發器負責記錄資料的刪除操作。當資料被刪除時，我們需要完整保存被刪除資料的內容，以便日後可能需要查看或恢復資料。DELETE 觸發器透過 OLD 關鍵字存取即將被刪除的資料，這些資料在觸發器執行時仍然可以存取，但在觸發器執行完畢後就會從業務表格中永久移除。
在某些應用場景中，DELETE 觸發器不僅用於審計記錄，還可以用於實作軟刪除（Soft Delete）機制。軟刪除是指資料並不真正從資料庫中移除，而是標記為已刪除狀態，這樣可以更容易地恢復誤刪的資料。然而，在本文的範例中，我們採用真正的硬刪除（Hard Delete），並在審計表格中保存完整的刪除資料記錄。
DELETE 觸發器的實作邏輯與 INSERT 觸發器類似，主要差異在於我們記錄的是 OLD 資料而非 NEW 資料。同樣地，我們建構 JSON 格式的資料記錄與人類可讀的文字描述，提供完整的審計追蹤能力。
-- ============================================================
-- 建立 DELETE 審計觸發器
-- ============================================================
-- 此觸發器會在 payable 表格成功刪除資料後自動執行
-- 記錄刪除操作的完整資訊，包含被刪除資料的完整內容
DELIMITER //
CREATE TRIGGER tr_payable_delete_audit
-- 指定觸發時機：AFTER DELETE
-- 在資料成功刪除後執行觸發器
AFTER DELETE ON payable
FOR EACH ROW
BEGIN
-- ============================================================
-- 宣告變數
-- ============================================================
-- 用於儲存被刪除資料的 JSON 格式字串
DECLARE v_old_values TEXT;
-- 用於儲存人類可讀的變更描述
DECLARE v_change_description TEXT;
-- ============================================================
-- 建構被刪除資料的 JSON 格式記錄
-- ============================================================
-- 使用 OLD 關鍵字存取即將被刪除的資料
-- 這些資料在觸發器執行時仍然可以存取
SET v_old_values = CONCAT(
'{',
'"payable_id": ', OLD.payable_id, ', ',
'"company": "', REPLACE(OLD.company, '"', '\\"'), '", ',
'"amount": ', OLD.amount, ', ',
'"service": "', IFNULL(REPLACE(OLD.service, '"', '\\"'), ''), '", ',
'"due_date": "', IFNULL(OLD.due_date, ''), '", ',
'"status": "', OLD.status, '", ',
'"created_at": "', OLD.created_at, '", ',
'"updated_at": "', OLD.updated_at, '"',
'}'
);
-- ============================================================
-- 建構刪除操作的描述
-- ============================================================
-- 記錄關鍵資訊以便快速識別被刪除的記錄
SET v_change_description = CONCAT(
'刪除應付帳款記錄，ID: ', OLD.payable_id, '，',
'供應商: ', OLD.company, '，',
'金額: NT$', FORMAT(OLD.amount, 2), '，',
'服務項目: ', IFNULL(OLD.service, '（無說明）'), '，',
'原狀態: ', OLD.status
);
-- ============================================================
-- 插入審計記錄
-- ============================================================
INSERT INTO payable_audit (
audit_datetime,
audit_user,
audit_action,
audit_change,
old_values, -- 儲存被刪除的完整資料
new_values, -- DELETE 操作沒有新值
payable_id
)
VALUES (
NOW(),
USER(),
'DELETE', -- 操作類型固定為 DELETE
v_change_description,
v_old_values, -- 使用前面建構的 JSON 資料
NULL, -- DELETE 操作沒有新值
OLD.payable_id
);
-- ============================================================
-- 進階功能：發送刪除通知（選用）
-- ============================================================
-- 在實務應用中，可以在這裡加入額外的邏輯
-- 例如：如果刪除的金額超過一定門檻，發送通知給管理者
-- 或是記錄到額外的警示表格中
-- IF OLD.amount > 100000 THEN
-- INSERT INTO alert_log (alert_type, alert_message, alert_datetime)
-- VALUES ('HIGH_VALUE_DELETE', v_change_description, NOW());
-- END IF;
END //
DELIMITER ;
-- ============================================================
-- 使用說明與測試建議
-- ============================================================
-- 測試 DELETE 觸發器時，建議先在測試環境中執行
-- 可以先插入測試資料，然後刪除並檢查審計記錄
--
-- 測試範例：
-- INSERT INTO payable (company, amount, service)
-- VALUES ('測試公司', 10000, '測試服務');
--
-- SET @test_id = LAST_INSERT_ID();
--
-- DELETE FROM payable WHERE payable_id = @test_id;
--
-- SELECT * FROM payable_audit WHERE payable_id = @test_id ORDER BY audit_datetime;
DELETE 觸發器在實務應用中有一些特殊的考量。首先，當執行批次刪除（例如 DELETE FROM payable WHERE status = ‘cancelled’）時，觸發器會對每一筆被刪除的資料分別執行，這可能會產生大量的審計記錄。如果系統需要頻繁執行大規模的資料清理作業，我們需要評估觸發器對效能的影響，必要時可以考慮暫時停用觸發器，在應用程式層面記錄批次刪除操作，或是使用非同步的審計機制。
其次，某些業務場景可能需要實作級聯刪除（Cascade Delete）的審計記錄。例如，當刪除一個客戶時，相關的訂單與交易記錄也會被刪除。在這種情況下，我們需要在每個相關表格上都建立 DELETE 觸發器，確保所有被刪除的資料都有完整的審計記錄。審計表格的設計也需要能夠清楚表達這種關聯關係，例如透過額外的欄位記錄父記錄的資訊。
實作 UPDATE 操作的基礎審計觸發器
UPDATE 觸發器是三種類型中最複雜的，因為它需要同時處理資料的舊值與新值。UPDATE 觸發器可以存取 OLD 與 NEW 兩個虛擬表格，分別代表更新前與更新後的資料。透過比較這兩組資料，我們可以精確記錄哪些欄位發生了變化，以及變化的具體內容。
在設計 UPDATE 觸發器時，我們有兩種主要的策略。第一種是簡單策略，無論哪些欄位被更新，都記錄所有欄位的新舊值。這種方式實作簡單，但可能會產生大量冗餘的審計記錄，特別是當更新操作只修改少數幾個欄位時。第二種是精細策略，只記錄實際發生變化的欄位。這種方式可以大幅減少審計記錄的大小，提升審計報表的可讀性，但實作較為複雜。
我們先實作基礎的 UPDATE 觸發器，記錄所有欄位的新舊值。這種觸發器適合欄位數量較少的表格，或是每次更新通常會修改多個欄位的場景。在後續章節中，我們會展示如何實作更精細的觸發器。
-- ============================================================
-- 建立基礎 UPDATE 審計觸發器
-- ============================================================
-- 此觸發器記錄所有欄位的更新前後值
-- 適用於欄位數量較少或需要完整記錄的場景
DELIMITER //
CREATE TRIGGER tr_payable_update_audit_basic
-- 指定觸發時機：AFTER UPDATE
-- 在資料成功更新後執行觸發器
AFTER UPDATE ON payable
FOR EACH ROW
BEGIN
-- ============================================================
-- 宣告變數
-- ============================================================
DECLARE v_old_values TEXT;
DECLARE v_new_values TEXT;
DECLARE v_change_description TEXT;
-- ============================================================
-- 建構更新前的資料（舊值）
-- ============================================================
-- 使用 OLD 關鍵字存取更新前的資料
SET v_old_values = CONCAT(
'{',
'"payable_id": ', OLD.payable_id, ', ',
'"company": "', REPLACE(OLD.company, '"', '\\"'), '", ',
'"amount": ', OLD.amount, ', ',
'"service": "', IFNULL(REPLACE(OLD.service, '"', '\\"'), ''), '", ',
'"due_date": "', IFNULL(OLD.due_date, ''), '", ',
'"status": "', OLD.status, '", ',
'"updated_at": "', OLD.updated_at, '"',
'}'
);
-- ============================================================
-- 建構更新後的資料（新值）
-- ============================================================
-- 使用 NEW 關鍵字存取更新後的資料
SET v_new_values = CONCAT(
'{',
'"payable_id": ', NEW.payable_id, ', ',
'"company": "', REPLACE(NEW.company, '"', '\\"'), '", ',
'"amount": ', NEW.amount, ', ',
'"service": "', IFNULL(REPLACE(NEW.service, '"', '\\"'), ''), '", ',
'"due_date": "', IFNULL(NEW.due_date, ''), '", ',
'"status": "', NEW.status, '", ',
'"updated_at": "', NEW.updated_at, '"',
'}'
);
-- ============================================================
-- 建構更新操作的描述
-- ============================================================
-- 提供簡要的描述，詳細的變更資訊儲存在 JSON 欄位中
SET v_change_description = CONCAT(
'更新應付帳款記錄，ID: ', OLD.payable_id, '，',
'供應商: ', NEW.company
);
-- ============================================================
-- 插入審計記錄
-- ============================================================
INSERT INTO payable_audit (
audit_datetime,
audit_user,
audit_action,
audit_change,
old_values, -- 更新前的完整資料
new_values, -- 更新後的完整資料
payable_id
)
VALUES (
NOW(),
USER(),
'UPDATE',
v_change_description,
v_old_values,
v_new_values,
OLD.payable_id -- UPDATE 操作中 OLD.payable_id 與 NEW.payable_id 相同
);
END //
DELIMITER ;
基礎 UPDATE 觸發器的優點是實作簡單且不易出錯，它確保了所有欄位的變更都被完整記錄。然而，這種方式也有明顯的缺點。當資料表格有許多欄位時，即使只更新一個欄位，審計記錄也會包含所有欄位的新舊值，這會佔用大量的儲存空間。此外，在審查審計記錄時，需要仔細比對新舊值才能找出實際變化的欄位，降低了審計報表的可讀性。
在實務應用中，基礎 UPDATE 觸發器適合以下場景：表格欄位數量較少（例如 10 個以下）、每次更新通常會修改多個欄位、或是法規要求必須記錄完整的資料快照（即使某些欄位沒有變化）。對於其他場景，建議使用下一節介紹的精細化 UPDATE 觸發器。
實作精細化的 UPDATE 審計觸發器
精細化的 UPDATE 觸發器只記錄實際發生變化的欄位，這種設計可以大幅減少審計記錄的大小，提升審計報表的可讀性與查詢效能。實作精細化觸發器需要逐一比較每個欄位的新舊值，只有當欄位值確實發生變化時才記錄該欄位的變更資訊。
在比較欄位值時，我們需要特別處理 NULL 值的情況。在 SQL 中，NULL 與任何值（包括 NULL 本身）比較的結果都是 NULL，而不是 TRUE 或 FALSE。因此，我們不能簡單地使用 OLD.column != NEW.column 來判斷欄位是否變化，而需要同時檢查 NULL 值的特殊情況。正確的比較邏輯應該考慮三種情況：兩個值都不是 NULL 但不相等、舊值是 NULL 而新值不是、舊值不是 NULL 而新值是。
精細化觸發器會建構一個包含所有變更欄位資訊的文字描述，這個描述使用易於閱讀的格式，例如「金額從 10000 變更為 12000；狀態從『待付款』變更為『已核准』」。這種格式讓審計人員可以快速瀏覽變更內容，而不需要解析複雜的 JSON 資料。此外，觸發器會檢查是否有任何欄位發生變化，只有在確實有變更時才插入審計記錄，避免記錄無意義的更新操作（例如使用相同的值再次更新記錄）。
-- ============================================================
-- 建立精細化 UPDATE 審計觸發器
-- ============================================================
-- 此觸發器只記錄實際發生變化的欄位
-- 提供更精確與易讀的審計記錄
DELIMITER //
-- 如果已存在舊的 UPDATE 觸發器，先刪除
DROP TRIGGER IF EXISTS tr_payable_update_audit_basic //
DROP TRIGGER IF EXISTS tr_payable_update_audit //
CREATE TRIGGER tr_payable_update_audit
AFTER UPDATE ON payable
FOR EACH ROW
BEGIN
-- ============================================================
-- 宣告變數
-- ============================================================
-- 用於累積變更描述的文字
DECLARE v_change_msg TEXT DEFAULT '';
-- 用於儲存變更欄位的 JSON 物件
DECLARE v_changed_fields TEXT DEFAULT '{';
-- 標記是否有任何欄位發生變化
DECLARE v_has_change BOOLEAN DEFAULT FALSE;
-- 用於計數變更的欄位數量
DECLARE v_field_count INT DEFAULT 0;
-- ============================================================
-- 檢查 company 欄位是否變更
-- ============================================================
-- 使用完整的 NULL 值處理邏輯
-- 檢查三種情況：值不同、從 NULL 變為有值、從有值變為 NULL
IF (OLD.company IS NOT NULL AND NEW.company IS NOT NULL AND OLD.company != NEW.company) OR
(OLD.company IS NULL AND NEW.company IS NOT NULL) OR
(OLD.company IS NOT NULL AND NEW.company IS NULL) THEN
-- 累積人類可讀的變更描述
SET v_change_msg = CONCAT(
v_change_msg,
'供應商從「', IFNULL(OLD.company, 'NULL'),
'」變更為「', IFNULL(NEW.company, 'NULL'), '」；'
);
-- 累積 JSON 格式的變更記錄
IF v_field_count > 0 THEN
SET v_changed_fields = CONCAT(v_changed_fields, ', ');
END IF;
SET v_changed_fields = CONCAT(
v_changed_fields,
'"company": {"old": "', IFNULL(REPLACE(OLD.company, '"', '\\"'), 'null'),
'", "new": "', IFNULL(REPLACE(NEW.company, '"', '\\"'), 'null'), '"}'
);
SET v_has_change = TRUE;
SET v_field_count = v_field_count + 1;
END IF;
-- ============================================================
-- 檢查 amount 欄位是否變更
-- ============================================================
-- 金額欄位使用數值比較
IF (OLD.amount IS NOT NULL AND NEW.amount IS NOT NULL AND OLD.amount != NEW.amount) OR
(OLD.amount IS NULL AND NEW.amount IS NOT NULL) OR
(OLD.amount IS NOT NULL AND NEW.amount IS NULL) THEN
SET v_change_msg = CONCAT(
v_change_msg,
'金額從 NT$', IFNULL(FORMAT(OLD.amount, 2), 'NULL'),
' 變更為 NT$', IFNULL(FORMAT(NEW.amount, 2), 'NULL'), '；'
);
IF v_field_count > 0 THEN
SET v_changed_fields = CONCAT(v_changed_fields, ', ');
END IF;
SET v_changed_fields = CONCAT(
v_changed_fields,
'"amount": {"old": ', IFNULL(OLD.amount, 'null'),
', "new": ', IFNULL(NEW.amount, 'null'), '}'
);
SET v_has_change = TRUE;
SET v_field_count = v_field_count + 1;
END IF;
-- ============================================================
-- 檢查 service 欄位是否變更
-- ============================================================
IF (OLD.service IS NOT NULL AND NEW.service IS NOT NULL AND OLD.service != NEW.service) OR
(OLD.service IS NULL AND NEW.service IS NOT NULL) OR
(OLD.service IS NOT NULL AND NEW.service IS NULL) THEN
SET v_change_msg = CONCAT(
v_change_msg,
'服務項目從「', IFNULL(OLD.service, 'NULL'),
'」變更為「', IFNULL(NEW.service, 'NULL'), '」；'
);
IF v_field_count > 0 THEN
SET v_changed_fields = CONCAT(v_changed_fields, ', ');
END IF;
SET v_changed_fields = CONCAT(
v_changed_fields,
'"service": {"old": "', IFNULL(REPLACE(OLD.service, '"', '\\"'), 'null'),
'", "new": "', IFNULL(REPLACE(NEW.service, '"', '\\"'), 'null'), '"}'
);
SET v_has_change = TRUE;
SET v_field_count = v_field_count + 1;
END IF;
-- ============================================================
-- 檢查 due_date 欄位是否變更
-- ============================================================
IF (OLD.due_date IS NOT NULL AND NEW.due_date IS NOT NULL AND OLD.due_date != NEW.due_date) OR
(OLD.due_date IS NULL AND NEW.due_date IS NOT NULL) OR
(OLD.due_date IS NOT NULL AND NEW.due_date IS NULL) THEN
SET v_change_msg = CONCAT(
v_change_msg,
'到期日從 ', IFNULL(DATE_FORMAT(OLD.due_date, '%Y-%m-%d'), 'NULL'),
' 變更為 ', IFNULL(DATE_FORMAT(NEW.due_date, '%Y-%m-%d'), 'NULL'), '；'
);
IF v_field_count > 0 THEN
SET v_changed_fields = CONCAT(v_changed_fields, ', ');
END IF;
SET v_changed_fields = CONCAT(
v_changed_fields,
'"due_date": {"old": "', IFNULL(OLD.due_date, 'null'),
'", "new": "', IFNULL(NEW.due_date, 'null'), '"}'
);
SET v_has_change = TRUE;
SET v_field_count = v_field_count + 1;
END IF;
-- ============================================================
-- 檢查 status 欄位是否變更
-- ============================================================
-- 狀態變更通常是業務流程中的重要事件
IF (OLD.status IS NOT NULL AND NEW.status IS NOT NULL AND OLD.status != NEW.status) OR
(OLD.status IS NULL AND NEW.status IS NOT NULL) OR
(OLD.status IS NOT NULL AND NEW.status IS NULL) THEN
SET v_change_msg = CONCAT(
v_change_msg,
'狀態從「', IFNULL(OLD.status, 'NULL'),
'」變更為「', IFNULL(NEW.status, 'NULL'), '」；'
);
IF v_field_count > 0 THEN
SET v_changed_fields = CONCAT(v_changed_fields, ', ');
END IF;
SET v_changed_fields = CONCAT(
v_changed_fields,
'"status": {"old": "', IFNULL(OLD.status, 'null'),
'", "new": "', IFNULL(NEW.status, 'null'), '"}'
);
SET v_has_change = TRUE;
SET v_field_count = v_field_count + 1;
END IF;
-- ============================================================
-- 完成 JSON 物件的建構
-- ============================================================
SET v_changed_fields = CONCAT(v_changed_fields, '}');
-- ============================================================
-- 只有在有實際變更時才記錄審計資訊
-- ============================================================
-- 這個檢查避免記錄無意義的更新操作
-- 例如使用相同的值再次更新記錄
IF v_has_change THEN
INSERT INTO payable_audit (
audit_datetime,
audit_user,
audit_action,
audit_change,
old_values, -- 儲存變更欄位的舊值（JSON 格式）
new_values, -- 儲存變更欄位的新值（JSON 格式）
payable_id
)
VALUES (
NOW(),
USER(),
'UPDATE',
-- 組合完整的變更描述
CONCAT(
'更新應付帳款 ID: ', OLD.payable_id,
'（供應商: ', NEW.company, '）。',
v_change_msg
),
v_changed_fields, -- 使用精簡的 JSON 記錄，只包含變更的欄位
v_changed_fields, -- 在精細化版本中，舊值與新值都在同一個 JSON 中
OLD.payable_id
);
END IF;
-- ============================================================
-- 進階功能：特定欄位變更的額外處理（選用）
-- ============================================================
-- 在實務應用中，某些關鍵欄位的變更可能需要特別處理
-- 例如：狀態變更為「已付款」時，可能需要觸發其他業務邏輯
-- IF OLD.status != 'paid' AND NEW.status = 'paid' THEN
-- -- 記錄到付款歷史表格
-- -- 或發送通知給相關人員
-- END IF;
END //
DELIMITER ;
精細化 UPDATE 觸發器雖然實作較為複雜，但在實務應用中具有明顯的優勢。首先，審計記錄的大小顯著減少，特別是對於欄位數量較多的表格。其次，審計報表的可讀性大幅提升，使用者可以快速瀏覽變更摘要，而不需要比對完整的資料快照。第三，查詢效能得到改善，因為審計記錄更小且更易於索引。
在維護精細化觸發器時，需要注意當業務表格增加新欄位時，觸發器也需要相應更新。這可能會增加系統維護的複雜度。一種緩解這個問題的方法是使用動態 SQL 或資訊架構（Information Schema）來自動偵測表格結構的變化，但這會進一步增加觸發器的複雜度。因此，在選擇基礎版本或精細化版本時，需要根據具體的業務需求、資料特性與維護能力來決定。
觸發器的測試與驗證策略
建立觸發器後，完整的測試是確保審計系統正常運作的關鍵步驟。測試應該涵蓋各種正常與異常的使用場景，包含單筆操作、批次操作、NULL 值處理、特殊字元處理，以及併發操作等。透過系統化的測試，我們可以及早發現潛在的問題，並驗證審計記錄的準確性與完整性。
測試策略應該包含單元測試與整合測試兩個層面。單元測試針對每個觸發器分別測試，確保在各種輸入條件下都能產生正確的審計記錄。整合測試則模擬真實的業務流程，驗證多個操作組合時審計系統的整體表現。此外，我們還需要進行效能測試，評估觸發器對資料庫操作效能的影響，特別是在高並發與大量資料的場景下。
-- ============================================================
-- 觸發器測試套件
-- ============================================================
-- 此測試套件涵蓋各種使用場景，確保觸發器正常運作
-- ============================================================
-- 測試 1: INSERT 觸發器的基本功能
-- ============================================================
-- 插入一筆新的應付帳款記錄
INSERT INTO payable (company, amount, service, due_date, status)
VALUES ('天星彩繪設計有限公司', 45000.00, '辦公室牆面重新粉刷與設計', '2024-03-15', 'pending');
-- 取得剛插入的記錄 ID
SET @test_payable_id = LAST_INSERT_ID();
-- 驗證審計記錄是否正確建立
SELECT
audit_id,
audit_datetime,
audit_user,
audit_action,
audit_change,
new_values
FROM payable_audit
WHERE payable_id = @test_payable_id
AND audit_action = 'INSERT'
ORDER BY audit_datetime DESC
LIMIT 1;
-- 預期結果：
-- - audit_action 應該是 'INSERT'
-- - new_values 應該包含完整的新增資料
-- - old_values 應該是 NULL
-- - audit_change 應該包含易讀的描述
-- ============================================================
-- 測試 2: UPDATE 觸發器的基本功能
-- ============================================================
-- 更新金額與狀態
UPDATE payable
SET amount = 48000.00,
status = 'approved',
service = '辦公室牆面重新粉刷、設計與防水處理'
WHERE payable_id = @test_payable_id;
-- 驗證審計記錄
SELECT
audit_id,
audit_datetime,
audit_user,
audit_action,
audit_change
FROM payable_audit
WHERE payable_id = @test_payable_id
AND audit_action = 'UPDATE'
ORDER BY audit_datetime DESC
LIMIT 1;
-- 預期結果：
-- - audit_change 應該明確指出金額、狀態與服務項目的變更
-- - 如果使用精細化觸發器，應該只記錄變更的欄位
-- ============================================================
-- 測試 3: UPDATE 觸發器的 NULL 值處理
-- ============================================================
-- 測試將欄位設定為 NULL
UPDATE payable
SET service = NULL,
due_date = NULL
WHERE payable_id = @test_payable_id;
-- 驗證審計記錄
SELECT
audit_change
FROM payable_audit
WHERE payable_id = @test_payable_id
AND audit_action = 'UPDATE'
ORDER BY audit_datetime DESC
LIMIT 1;
-- 預期結果：
-- - 應該正確記錄從有值變更為 NULL
-- ============================================================
-- 測試 4: UPDATE 觸發器的無變更情況
-- ============================================================
-- 使用相同的值再次更新（沒有實際變更）
UPDATE payable
SET amount = 48000.00,
status = 'approved'
WHERE payable_id = @test_payable_id;
-- 檢查是否產生新的審計記錄
SELECT COUNT(*) AS audit_count
FROM payable_audit
WHERE payable_id = @test_payable_id
AND audit_action = 'UPDATE';
-- 預期結果：
-- - 如果使用精細化觸發器，不應該產生新的審計記錄
-- - 如果使用基礎觸發器，會產生記錄但新舊值相同
-- ============================================================
-- 測試 5: DELETE 觸發器的功能
-- ============================================================
-- 刪除測試記錄
DELETE FROM payable
WHERE payable_id = @test_payable_id;
-- 驗證審計記錄
SELECT
audit_id,
audit_datetime,
audit_user,
audit_action,
audit_change,
old_values
FROM payable_audit
WHERE payable_id = @test_payable_id
AND audit_action = 'DELETE'
ORDER BY audit_datetime DESC
LIMIT 1;
-- 預期結果：
-- - audit_action 應該是 'DELETE'
-- - old_values 應該包含被刪除的完整資料
-- - new_values 應該是 NULL
-- ============================================================
-- 測試 6: 檢視完整的審計歷史
-- ============================================================
-- 查看測試記錄的完整生命週期
SELECT
audit_datetime AS 時間,
audit_user AS 使用者,
audit_action AS 操作,
audit_change AS 變更內容
FROM payable_audit
WHERE payable_id = @test_payable_id
ORDER BY audit_datetime;
-- 預期結果：
-- - 應該看到完整的操作序列：INSERT -> UPDATE(多次) -> DELETE
-- - 每筆記錄的時間戳記應該遞增
-- - 變更內容應該清楚且易讀
-- ============================================================
-- 測試 7: 批次操作測試
-- ============================================================
-- 批次插入多筆測試資料
INSERT INTO payable (company, amount, service, status)
VALUES
('測試公司 A', 10000, '測試服務 A', 'pending'),
('測試公司 B', 20000, '測試服務 B', 'pending'),
('測試公司 C', 30000, '測試服務 C', 'pending');
-- 取得插入的記錄 ID 範圍
SET @batch_start_id = LAST_INSERT_ID();
SET @batch_end_id = @batch_start_id + 2;
-- 批次更新
UPDATE payable
SET status = 'approved'
WHERE payable_id BETWEEN @batch_start_id AND @batch_end_id;
-- 驗證每筆記錄都有對應的審計記錄
SELECT
payable_id,
COUNT(*) AS audit_records
FROM payable_audit
WHERE payable_id BETWEEN @batch_start_id AND @batch_end_id
GROUP BY payable_id
ORDER BY payable_id;
-- 預期結果：
-- - 每個 payable_id 應該有 2 筆審計記錄（1 INSERT + 1 UPDATE）
-- 批次刪除測試資料
DELETE FROM payable
WHERE payable_id BETWEEN @batch_start_id AND @batch_end_id;
-- 清理測試資料的審計記錄（選用）
-- DELETE FROM payable_audit
-- WHERE payable_id BETWEEN @batch_start_id AND @batch_end_id;
-- ============================================================
-- 測試 8: 特殊字元處理測試
-- ============================================================
-- 測試包含特殊字元的資料（引號、斜線等）
INSERT INTO payable (company, amount, service, status)
VALUES (
'O''Brien & Sons "Painting" Co.',
5000,
'測試特殊字元：引號"雙引號" 單引號''反斜線\ 與其他符號',
'pending'
);
SET @special_char_id = LAST_INSERT_ID();
-- 檢查審計記錄的 JSON 格式是否正確
SELECT new_values
FROM payable_audit
WHERE payable_id = @special_char_id
AND audit_action = 'INSERT';
-- 預期結果：
-- - JSON 格式應該正確（特殊字元應該被適當跳脫）
-- - 可以使用 JSON 函式解析資料
-- 清理特殊字元測試資料
DELETE FROM payable WHERE payable_id = @special_char_id;
-- ============================================================
-- 測試 9: 併發操作測試（進階）
-- ============================================================
-- 此測試需要多個連線同時執行
-- 在實務環境中，建議使用測試工具或腳本進行併發測試
-- 連線 1:
-- START TRANSACTION;
-- UPDATE payable SET amount = 50000 WHERE payable_id = X;
-- (等待一段時間)
-- COMMIT;
-- 連線 2:
-- START TRANSACTION;
-- UPDATE payable SET status = 'paid' WHERE payable_id = X;
-- (等待一段時間)
-- COMMIT;
-- 驗證審計記錄的順序是否正確
-- 兩個併發的更新應該產生兩筆獨立的審計記錄
-- ============================================================
-- 測試總結與驗證清單
-- ============================================================
/*
測試清單：
✓ INSERT 觸發器產生正確的審計記錄
✓ UPDATE 觸發器記錄欄位變更
✓ DELETE 觸發器保存被刪除的資料
✓ NULL 值的正確處理
✓ 無實際變更的 UPDATE 不產生記錄（精細化版本）
✓ 批次操作每筆記錄都有審計記錄
✓ 特殊字元的正確跳脫與儲存
✓ JSON 格式的正確性
✓ 審計記錄的時間順序正確
✓ 使用者資訊的正確記錄
*/
在進行測試時，我們還需要特別注意效能方面的考量。觸發器會在每次資料操作時執行，如果觸發器的邏輯過於複雜或是審計表格的索引設計不當，可能會顯著影響資料庫的整體效能。建議使用資料庫效能分析工具（如 MySQL 的 EXPLAIN 與 SHOW PROFILE）來評估觸發器的執行時間，確保不會成為系統的效能瓶頸。
審計報表的查詢與分析技巧
建立審計系統的最終目的是為了能夠查詢與分析歷史資料變更。設計良好的審計報表可以幫助我們快速找到需要的資訊，無論是追查特定問題、進行合規稽核，還是分析業務趨勢。審計報表的設計需要考慮不同使用者的需求，包括技術人員、業務人員與稽核人員。
常見的審計報表類型包括時間範圍報表（查看特定時間段內的所有變更）、使用者活動報表（追蹤特定使用者的操作記錄）、記錄歷史報表（查看特定記錄的完整生命週期），以及異常活動報表（偵測可疑的操作模式）。每種報表都需要適當的 SQL 查詢與資料呈現方式。
-- ============================================================
-- 審計報表查詢範例集
-- ============================================================
-- ============================================================
-- 報表 1: 特定時間範圍內的所有異動
-- ============================================================
-- 用途：檢視特定時期的系統活動，例如月度稽核或事件調查
SELECT
DATE_FORMAT(audit_datetime, '%Y-%m-%d %H:%i:%s') AS 異動時間,
audit_user AS 執行使用者,
CASE audit_action
WHEN 'INSERT' THEN '新增'
WHEN 'UPDATE' THEN '更新'
WHEN 'DELETE' THEN '刪除'
ELSE audit_action
END AS 操作類型,
audit_change AS 變更內容,
payable_id AS 記錄ID
FROM payable_audit
WHERE audit_datetime BETWEEN '2024-01-01' AND '2024-01-31 23:59:59'
ORDER BY audit_datetime DESC;
-- ============================================================
-- 報表 2: 使用者操作統計
-- ============================================================
-- 用途：分析各使用者的活動量，識別異常行為模式
SELECT
audit_user AS 使用者,
CASE audit_action
WHEN 'INSERT' THEN '新增'
WHEN 'UPDATE' THEN '更新'
WHEN 'DELETE' THEN '刪除'
END AS 操作類型,
COUNT(*) AS 操作次數,
MIN(audit_datetime) AS 首次操作時間,
MAX(audit_datetime) AS 最後操作時間
FROM payable_audit
WHERE audit_datetime >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY audit_user, audit_action
ORDER BY audit_user, audit_action;
-- ============================================================
-- 報表 3: 特定記錄的完整變更歷史
-- ============================================================
-- 用途：追蹤單一記錄從建立到刪除的完整生命週期
-- 首先設定要查詢的記錄 ID
SET @target_payable_id = 1;
SELECT
ROW_NUMBER() OVER (ORDER BY audit_datetime) AS 序號,
DATE_FORMAT(audit_datetime, '%Y-%m-%d %H:%i:%s') AS 時間,
audit_user AS 使用者,
CASE audit_action
WHEN 'INSERT' THEN '新增'
WHEN 'UPDATE' THEN '更新'
WHEN 'DELETE' THEN '刪除'
END AS 操作,
audit_change AS 變更內容
FROM payable_audit
WHERE payable_id = @target_payable_id
ORDER BY audit_datetime;
-- ============================================================
-- 報表 4: 最近的操作記錄
-- ============================================================
-- 用途：監控即時的系統活動
SELECT
DATE_FORMAT(audit_datetime, '%Y-%m-%d %H:%i:%s') AS 時間,
audit_user AS 使用者,
audit_action AS 操作,
audit_change AS 內容,
TIMESTAMPDIFF(MINUTE, audit_datetime, NOW()) AS 幾分鐘前
FROM payable_audit
WHERE audit_datetime >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY audit_datetime DESC;
-- ============================================================
-- 報表 5: 高額交易變更追蹤
-- ============================================================
-- 用途：監控重要財務資料的變更
SELECT
audit_datetime AS 時間,
audit_user AS 使用者,
audit_action AS 操作,
audit_change AS 變更內容,
-- 從 JSON 中提取金額資訊（MySQL 5.7+）
JSON_UNQUOTE(JSON_EXTRACT(new_values, '$.amount')) AS 新金額,
JSON_UNQUOTE(JSON_EXTRACT(old_values, '$.amount')) AS 舊金額
FROM payable_audit
WHERE
(JSON_EXTRACT(new_values, '$.amount') > 100000 OR
JSON_EXTRACT(old_values, '$.amount') > 100000)
AND audit_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY audit_datetime DESC;
-- ============================================================
-- 報表 6: 狀態變更流向分析
-- ============================================================
-- 用途：分析業務流程的狀態轉換
SELECT
JSON_UNQUOTE(JSON_EXTRACT(old_values, '$.status')) AS 原狀態,
JSON_UNQUOTE(JSON_EXTRACT(new_values, '$.status')) AS 新狀態,
COUNT(*) AS 轉換次數,
COUNT(DISTINCT payable_id) AS 影響記錄數
FROM payable_audit
WHERE audit_action = 'UPDATE'
AND JSON_EXTRACT(old_values, '$.status') IS NOT NULL
AND JSON_EXTRACT(new_values, '$.status') IS NOT NULL
AND JSON_EXTRACT(old_values, '$.status') != JSON_EXTRACT(new_values, '$.status')
GROUP BY
JSON_EXTRACT(old_values, '$.status'),
JSON_EXTRACT(new_values, '$.status')
ORDER BY 轉換次數 DESC;
-- ============================================================
-- 報表 7: 異常活動偵測
-- ============================================================
-- 用途：識別可能的異常操作模式
-- 7.1: 短時間內大量操作的使用者
SELECT
audit_user AS 使用者,
DATE_FORMAT(audit_datetime, '%Y-%m-%d %H:00:00') AS 時段,
COUNT(*) AS 操作次數
FROM payable_audit
WHERE audit_datetime >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY
audit_user,
DATE_FORMAT(audit_datetime, '%Y-%m-%d %H:00:00')
HAVING COUNT(*) > 100 -- 一小時內超過 100 次操作視為異常
ORDER BY 操作次數 DESC;
-- 7.2: 非營業時間的操作
SELECT
DATE_FORMAT(audit_datetime, '%Y-%m-%d %H:%i:%s') AS 時間,
audit_user AS 使用者,
audit_action AS 操作,
audit_change AS 內容
FROM payable_audit
WHERE
(HOUR(audit_datetime) < 8 OR HOUR(audit_datetime) >= 18) -- 非 8:00-18:00
AND DAYOFWEEK(audit_datetime) NOT IN (1, 7) -- 非週末（1=週日, 7=週六）
AND audit_datetime >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY audit_datetime DESC;
-- 7.3: 連續刪除操作
SELECT
audit_user AS 使用者,
DATE_FORMAT(MIN(audit_datetime), '%Y-%m-%d %H:%i:%s') AS 開始時間,
DATE_FORMAT(MAX(audit_datetime), '%Y-%m-%d %H:%i:%s') AS 結束時間,
COUNT(*) AS 刪除次數,
GROUP_CONCAT(payable_id ORDER BY audit_datetime SEPARATOR ', ') AS 刪除記錄ID
FROM payable_audit
WHERE audit_action = 'DELETE'
AND audit_datetime >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY audit_user
HAVING COUNT(*) >= 5 -- 一天內刪除 5 筆以上視為需要關注
ORDER BY 刪除次數 DESC;
-- ============================================================
-- 報表 8: 審計資料完整性檢查
-- ============================================================
-- 用途：驗證審計系統本身的完整性
-- 8.1: 檢查是否有記錄沒有對應的審計記錄
SELECT
p.payable_id,
p.company,
p.created_at,
COUNT(pa.audit_id) AS 審計記錄數
FROM payable p
LEFT JOIN payable_audit pa ON p.payable_id = pa.payable_id
GROUP BY p.payable_id, p.company, p.created_at
HAVING 審計記錄數 = 0; -- 找出沒有任何審計記錄的業務記錄
-- 8.2: 檢查審計記錄的時間連續性
SELECT
audit_id,
audit_datetime,
LAG(audit_datetime) OVER (ORDER BY audit_datetime) AS 前一筆時間,
TIMESTAMPDIFF(SECOND,
LAG(audit_datetime) OVER (ORDER BY audit_datetime),
audit_datetime
) AS 時間差秒數
FROM payable_audit
ORDER BY audit_datetime DESC
LIMIT 100;
-- ============================================================
-- 報表 9: 審計資料容量分析
-- ============================================================
-- 用途：監控審計表格的成長趨勢
SELECT
DATE_FORMAT(audit_datetime, '%Y-%m') AS 月份,
COUNT(*) AS 記錄數,
COUNT(DISTINCT payable_id) AS 影響記錄數,
COUNT(DISTINCT audit_user) AS 活躍使用者數,
SUM(CASE WHEN audit_action = 'INSERT' THEN 1 ELSE 0 END) AS 新增次數,
SUM(CASE WHEN audit_action = 'UPDATE' THEN 1 ELSE 0 END) AS 更新次數,
SUM(CASE WHEN audit_action = 'DELETE' THEN 1 ELSE 0 END) AS 刪除次數
FROM payable_audit
GROUP BY DATE_FORMAT(audit_datetime, '%Y-%m')
ORDER BY 月份 DESC;
這些審計報表涵蓋了大部分常見的查詢需求，但在實際應用中，可能需要根據特定的業務需求設計客製化的報表。建議將常用的報表查詢封裝為視圖（View）或預存程序（Stored Procedure），這樣可以簡化使用者的操作，並確保查詢邏輯的一致性。
觸發器管理與維護最佳實踐
觸發器的長期維護是確保審計系統穩定運作的關鍵。隨著業務需求的變化、資料量的增長，以及資料庫版本的升級，觸發器可能需要進行調整或最佳化。建立完善的管理機制可以降低維護成本，並確保審計系統始終符合需求。
觸發器管理包含多個層面，包括觸發器的查看與文件化、版本控制、效能監控、定期審查，以及升級策略。每個層面都需要適當的工具與流程來支援。此外，我們還需要考慮如何在不影響生產環境的情況下測試與部署觸發器的變更。
-- ============================================================
-- 觸發器管理操作指南
-- ============================================================
-- ============================================================
-- 1. 查看所有觸發器
-- ============================================================
-- 列出資料庫中的所有觸發器及其基本資訊
SHOW TRIGGERS;
-- 更詳細的觸發器資訊查詢
SELECT
TRIGGER_NAME AS 觸發器名稱,
EVENT_MANIPULATION AS 事件類型,
EVENT_OBJECT_TABLE AS 目標表格,
ACTION_TIMING AS 觸發時機,
CREATED AS 建立時間
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
ORDER BY EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION;
-- ============================================================
-- 2. 查看特定觸發器的完整定義
-- ============================================================
-- 這個指令會顯示建立觸發器的完整 SQL 語句
SHOW CREATE TRIGGER tr_payable_insert_audit;
-- 查看所有應付帳款相關的觸發器
SELECT
TRIGGER_NAME,
ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE()
AND EVENT_OBJECT_TABLE = 'payable';
-- ============================================================
-- 3. 暫時停用觸發器（MySQL 不直接支援）
-- ============================================================
-- MySQL 沒有直接停用觸發器的功能
-- 如果需要暫時停用，有以下幾種方式：
-- 方式 1: 刪除觸發器（需要先備份定義）
-- 備份觸發器定義
SELECT ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_NAME = 'tr_payable_insert_audit'
INTO OUTFILE '/tmp/trigger_backup.sql';
-- 刪除觸發器
DROP TRIGGER IF EXISTS tr_payable_insert_audit;
-- 方式 2: 使用條件變數（需要修改觸發器邏輯）
-- 在觸發器中加入檢查，例如：
-- IF @disable_audit_trigger = 0 THEN
-- -- 審計邏輯
-- END IF;
-- 設定變數以停用
SET @disable_audit_trigger = 1;
-- 重新啟用
SET @disable_audit_trigger = 0;
-- 方式 3: 使用臨時表格替換（進階技巧）
-- 建立一個相同結構但沒有觸發器的臨時表格
-- 在維護期間使用臨時表格
-- ============================================================
-- 4. 刪除觸發器
-- ============================================================
-- 刪除單一觸發器
DROP TRIGGER IF EXISTS tr_payable_insert_audit;
-- 批次刪除所有應付帳款的審計觸發器
DROP TRIGGER IF EXISTS tr_payable_insert_audit;
DROP TRIGGER IF EXISTS tr_payable_update_audit;
DROP TRIGGER IF EXISTS tr_payable_delete_audit;
-- ============================================================
-- 5. 觸發器的版本控制
-- ============================================================
-- 建議將觸發器定義儲存在版本控制系統（如 Git）中
-- 觸發器檔案命名建議：trigger_[table]_[event]_[version].sql
-- 例如：trigger_payable_insert_audit_v1.sql
-- 在觸發器中加入版本註解
/*
觸發器名稱: tr_payable_insert_audit
版本: 2.0
建立日期: 2024-01-15
修改日期: 2024-02-20
作者: 資訊部門審計小組
變更記錄:
- v1.0 (2024-01-15): 初始版本，記錄基本新增資訊
- v1.1 (2024-02-01): 增加 JSON 格式記錄
- v2.0 (2024-02-20): 改進 JSON 建構邏輯，增加特殊字元處理
*/
-- ============================================================
-- 6. 效能監控
-- ============================================================
-- 檢查觸發器執行時間（需要開啟效能架構）
SELECT
EVENT_NAME,
COUNT_STAR AS 執行次數,
ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS 總執行時間秒,
ROUND(AVG_TIMER_WAIT / 1000000000000, 4) AS 平均執行時間秒
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%payable_audit%'
ORDER BY SUM_TIMER_WAIT DESC;
-- 檢查審計表格的大小
SELECT
table_name AS 表格名稱,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 大小MB,
table_rows AS 估計列數
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
AND table_name = 'payable_audit';
-- ============================================================
-- 7. 審計資料歸檔
-- ============================================================
-- 建立歸檔表格（按年份）
CREATE TABLE payable_audit_2023_archive LIKE payable_audit;
-- 移動舊資料到歸檔表格
INSERT INTO payable_audit_2023_archive
SELECT * FROM payable_audit
WHERE YEAR(audit_datetime) = 2023;
-- 驗證資料已正確複製
SELECT COUNT(*) FROM payable_audit_2023_archive;
-- 刪除原表格中的舊資料
DELETE FROM payable_audit
WHERE YEAR(audit_datetime) = 2023;
-- 最佳化表格（回收空間）
OPTIMIZE TABLE payable_audit;
-- ============================================================
-- 8. 觸發器錯誤處理與除錯
-- ============================================================
-- 檢查最近的觸發器錯誤
SHOW WARNINGS;
SHOW ERRORS;
-- 使用 SELECT 語句除錯觸發器邏輯
-- 將觸發器邏輯提取為獨立的 SQL 語句
-- 使用實際的資料值測試
-- 例如，測試 JSON 建構邏輯：
SET @test_company = 'Test Company "ABC"';
SET @test_amount = 12345.67;
SET @test_json = CONCAT(
'{',
'"company": "', REPLACE(@test_company, '"', '\\"'), '", ',
'"amount": ', @test_amount,
'}'
);
SELECT @test_json;
-- ============================================================
-- 9. 重建觸發器的標準流程
-- ============================================================
-- 步驟 1: 備份現有觸發器定義
-- SHOW CREATE TRIGGER tr_payable_update_audit;
-- （將輸出保存到檔案）
-- 步驟 2: 在測試環境中測試新版本
-- （在測試資料庫中建立並測試）
-- 步驟 3: 規劃維護時段
-- （選擇系統負載較低的時段）
-- 步驟 4: 刪除舊觸發器
DROP TRIGGER IF EXISTS tr_payable_update_audit;
-- 步驟 5: 建立新觸發器
-- （執行新版本的 CREATE TRIGGER 語句）
-- 步驟 6: 驗證觸發器
-- （執行測試案例驗證功能）
-- 步驟 7: 監控效能
-- （觀察新觸發器的執行時間與系統影響）
在管理觸發器時，建議建立詳細的文件記錄，包含觸發器的設計目的、實作邏輯、已知限制，以及維護歷史。這些文件對於新加入的團隊成員理解系統架構，以及在問題發生時快速定位原因都非常有幫助。此外，建議定期審查觸發器的邏輯與效能，隨著業務需求與資料特性的變化，適時調整觸發器的實作方式。
效能最佳化與擴展性考量
隨著資料量的增長與業務規模的擴大，審計系統的效能可能會成為關注焦點。設計良好的審計系統應該能夠在不顯著影響主要業務操作效能的前提下，提供完整的審計追蹤能力。效能最佳化需要從多個層面著手，包含觸發器邏輯的最佳化、審計表格的索引設計、資料保留策略，以及在必要時考慮採用非同步審計機制。
觸發器的效能影響主要來自於兩個方面：觸發器本身的執行時間，以及審計記錄的寫入操作。觸發器邏輯應該盡可能簡潔，避免複雜的計算或是額外的表格查詢。JSON 資料的建構可以考慮使用資料庫的內建函式而非字串拼接，這不僅能提升效能，也能減少錯誤的可能性。審計表格的索引設計則需要根據實際的查詢模式來調整，過多的索引會影響寫入效能，而索引不足則會降低查詢效率。
-- ============================================================
-- 效能最佳化策略與實作
-- ============================================================
-- ============================================================
-- 策略 1: 優化觸發器邏輯
-- ============================================================
-- 使用 MySQL 5.7+ 的 JSON 函式建構 JSON 資料
-- 這比手動字串拼接更快且更安全
DELIMITER //
CREATE TRIGGER tr_payable_insert_audit_optimized
AFTER INSERT ON payable
FOR EACH ROW
BEGIN
DECLARE v_new_json TEXT;
-- 使用 JSON_OBJECT 函式建構 JSON（MySQL 5.7+）
-- 這個函式會自動處理特殊字元的跳脫
SET v_new_json = JSON_OBJECT(
'payable_id', NEW.payable_id,
'company', NEW.company,
'amount', NEW.amount,
'service', NEW.service,
'due_date', NEW.due_date,
'status', NEW.status
);
-- 簡化審計記錄的插入
INSERT INTO payable_audit (
audit_datetime,
audit_user,
audit_action,
audit_change,
new_values,
payable_id
) VALUES (
NOW(),
USER(),
'INSERT',
CONCAT('新增記錄 ID: ', NEW.payable_id),
v_new_json,
NEW.payable_id
);
END //
DELIMITER ;
-- ============================================================
-- 策略 2: 審計表格的索引最佳化
-- ============================================================
-- 根據實際查詢模式建立適當的索引
-- 移除不常用的索引
-- DROP INDEX idx_audit_user ON payable_audit;
-- 建立複合索引支援常見查詢
CREATE INDEX idx_datetime_action ON payable_audit(audit_datetime, audit_action);
CREATE INDEX idx_payable_datetime ON payable_audit(payable_id, audit_datetime DESC);
-- 考慮使用部分索引（MySQL 8.0+）
-- 只索引最近的資料
-- CREATE INDEX idx_recent_audit
-- ON payable_audit(audit_datetime)
-- WHERE audit_datetime >= DATE_SUB(NOW(), INTERVAL 90 DAY);
-- ============================================================
-- 策略 3: 資料分區
-- ============================================================
-- 使用分區表格提升查詢效能並簡化資料維護
-- 建立分區的審計表格
CREATE TABLE payable_audit_partitioned (
audit_id INT PRIMARY KEY AUTO_INCREMENT,
audit_datetime DATETIME NOT NULL,
audit_user VARCHAR(100) NOT NULL,
audit_action VARCHAR(20) NOT NULL,
audit_change TEXT NOT NULL,
old_values TEXT,
new_values TEXT,
payable_id INT,
INDEX idx_datetime (audit_datetime),
INDEX idx_payable (payable_id)
)
PARTITION BY RANGE (YEAR(audit_datetime) * 100 + MONTH(audit_datetime)) (
PARTITION p202401 VALUES LESS THAN (202402),
PARTITION p202402 VALUES LESS THAN (202403),
PARTITION p202403 VALUES LESS THAN (202404),
PARTITION p202404 VALUES LESS THAN (202405),
PARTITION p202405 VALUES LESS THAN (202406),
PARTITION p202406 VALUES LESS THAN (202407),
PARTITION p202407 VALUES LESS THAN (202408),
PARTITION p202408 VALUES LESS THAN (202409),
PARTITION p202409 VALUES LESS THAN (202410),
PARTITION p202410 VALUES LESS THAN (202411),
PARTITION p202411 VALUES LESS THAN (202412),
PARTITION p202412 VALUES LESS THAN (202501),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 新增新的分區（每月執行）
ALTER TABLE payable_audit_partitioned
REORGANIZE PARTITION pmax INTO (
PARTITION p202501 VALUES LESS THAN (202502),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 刪除舊的分區（資料歸檔後）
ALTER TABLE payable_audit_partitioned
DROP PARTITION p202401;
-- ============================================================
-- 策略 4: 批次資料歸檔
-- ============================================================
-- 定期將舊資料移至歸檔表格
-- 建立歸檔程序
DELIMITER //
CREATE PROCEDURE sp_archive_old_audit_records(
IN p_archive_before_date DATE
)
BEGIN
DECLARE v_row_count INT DEFAULT 0;
-- 開始交易
START TRANSACTION;
-- 建立當年的歸檔表格（如果不存在）
SET @archive_table = CONCAT(
'payable_audit_archive_',
YEAR(p_archive_before_date)
);
-- 移動資料到歸檔表格
SET @sql = CONCAT(
'INSERT INTO ', @archive_table,
' SELECT * FROM payable_audit ',
'WHERE audit_datetime < ''', p_archive_before_date, ''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 記錄歸檔的資料筆數
SET v_row_count = ROW_COUNT();
-- 刪除已歸檔的資料
DELETE FROM payable_audit
WHERE audit_datetime < p_archive_before_date;
-- 提交交易
COMMIT;
-- 回傳歸檔筆數
SELECT CONCAT('已歸檔 ', v_row_count, ' 筆記錄') AS result;
-- 最佳化表格
OPTIMIZE TABLE payable_audit;
END //
DELIMITER ;
-- 執行歸檔（例如歸檔一年前的資料）
CALL sp_archive_old_audit_records(DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR));
-- ============================================================
-- 策略 5: 非同步審計機制（進階）
-- ============================================================
-- 對於高頻率的資料庫操作，可以考慮使用訊息佇列
-- 觸發器只將審計資訊寫入佇列，由背景程序處理
-- 建立審計佇列表格
CREATE TABLE audit_queue (
queue_id BIGINT PRIMARY KEY AUTO_INCREMENT,
table_name VARCHAR(100) NOT NULL,
record_id INT NOT NULL,
action_type VARCHAR(20) NOT NULL,
action_data TEXT NOT NULL,
queued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
processed BOOLEAN DEFAULT FALSE,
processed_at TIMESTAMP NULL,
INDEX idx_processed (processed, queued_at)
) ENGINE=InnoDB;
-- 修改觸發器使用佇列
DELIMITER //
CREATE TRIGGER tr_payable_insert_queue
AFTER INSERT ON payable
FOR EACH ROW
BEGIN
INSERT INTO audit_queue (
table_name,
record_id,
action_type,
action_data
) VALUES (
'payable',
NEW.payable_id,
'INSERT',
JSON_OBJECT(
'payable_id', NEW.payable_id,
'company', NEW.company,
'amount', NEW.amount,
'service', NEW.service,
'due_date', NEW.due_date,
'status', NEW.status,
'user', USER(),
'timestamp', NOW()
)
);
END //
DELIMITER ;
-- 背景程序處理佇列（這需要在應用程式層面實作）
-- 定期執行以下查詢並處理未處理的審計資料
/*
SELECT * FROM audit_queue
WHERE processed = FALSE
ORDER BY queued_at
LIMIT 100;
-- 處理後更新狀態
UPDATE audit_queue
SET processed = TRUE, processed_at = NOW()
WHERE queue_id IN (...);
*/
-- ============================================================
-- 策略 6: 監控與警示
-- ============================================================
-- 建立監控表格記錄審計系統的健康狀態
CREATE TABLE audit_system_metrics (
metric_id INT PRIMARY KEY AUTO_INCREMENT,
metric_datetime DATETIME NOT NULL,
audit_table_size_mb DECIMAL(10, 2),
audit_record_count BIGINT,
oldest_record_date DATE,
newest_record_date DATE,
avg_records_per_day INT,
INDEX idx_metric_datetime (metric_datetime)
) ENGINE=InnoDB;
-- 建立收集指標的程序
DELIMITER //
CREATE PROCEDURE sp_collect_audit_metrics()
BEGIN
INSERT INTO audit_system_metrics (
metric_datetime,
audit_table_size_mb,
audit_record_count,
oldest_record_date,
newest_record_date,
avg_records_per_day
)
SELECT
NOW(),
(SELECT ROUND((data_length + index_length) / 1024 / 1024, 2)
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
AND table_name = 'payable_audit'),
COUNT(*),
DATE(MIN(audit_datetime)),
DATE(MAX(audit_datetime)),
COUNT(*) / NULLIF(DATEDIFF(MAX(audit_datetime), MIN(audit_datetime)), 0)
FROM payable_audit;
END //
DELIMITER ;
-- 設定定期執行（使用 MySQL Event Scheduler）
CREATE EVENT evt_collect_audit_metrics
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO CALL sp_collect_audit_metrics();
-- 啟用 Event Scheduler
SET GLOBAL event_scheduler = ON;
-- 查看指標趨勢
SELECT
metric_datetime AS 時間,
audit_table_size_mb AS 表格大小MB,
audit_record_count AS 記錄總數,
avg_records_per_day AS 每日平均記錄數
FROM audit_system_metrics
ORDER BY metric_datetime DESC
LIMIT 30;
效能最佳化是一個持續的過程，需要根據實際的系統負載與查詢模式不斷調整。建議定期檢視審計系統的效能指標，包含觸發器執行時間、審計表格大小、查詢回應時間等。當發現效能瓶頸時，可以使用資料庫的效能分析工具（如 EXPLAIN、SHOW PROFILE）來診斷問題，並採取適當的最佳化措施。
總結與最佳實踐建議
資料庫審計觸發器是建構完整資料治理體系的重要工具。透過自動化的審計追蹤機制，企業可以滿足法規遵循要求、提升資料安全性、簡化問題調查流程，並為業務分析提供寶貴的歷史資料。本文從觸發器的基礎概念開始，逐步介紹了審計系統的完整架構設計、各種類型觸發器的實作方法、審計報表的查詢技巧，以及效能最佳化策略。
在實作審計觸發器時，設計決策需要在完整性、效能與維護性之間取得平衡。精細化的 UPDATE 觸發器雖然能夠提供更精確與易讀的審計記錄，但實作與維護的複雜度也相對較高。基礎版本的觸發器則提供了簡單可靠的審計能力，適合欄位數量較少或資源有限的場景。在選擇實作方式時，需要根據具體的業務需求、資料特性與團隊能力來決定。
審計表格的設計應該同時考慮資料完整性與查詢效能。適當的索引設計可以大幅提升審計報表的查詢速度，而分區表格則能簡化資料維護作業。資料保留政策也是重要的考量因素，需要在法規要求、儲存成本與查詢效能之間找到適當的平衡點。定期歸檔舊資料不僅能降低儲存成本，也能維持線上審計表格的查詢效能。
在管理與維護層面，建議建立完善的觸發器文件，包含設計目的、實作邏輯、測試案例與已知限制。將觸發器定義納入版本控制系統，可以追蹤變更歷史並簡化部署流程。定期審查觸發器的邏輯與效能，隨著業務演進適時調整實作方式。建立監控機制追蹤審計系統的健康狀態，及早發現潛在問題。
對於台灣的企業而言，資料保護法規的要求日益嚴格，建立完善的資料審計機制已經成為合規經營的必要條件。透過資料庫觸發器實作的審計系統，不僅能夠滿足法規要求，更能為企業的資料治理與風險管理提供堅實的基礎。建議企業根據自身的規模與需求，逐步建立完整的審計體系，從核心的財務與客戶資料開始，逐步擴展到其他重要的業務資料。
持續學習與改進是維持審計系統有效性的關鍵。隨著資料庫技術的演進、新的最佳實踐的出現，以及業務需求的變化，審計系統也需要不斷優化與升級。保持對新技術與新方法的關注，積極參與技術社群的交流，將有助於建構更完善與高效的資料庫審計系統。
