資料庫觸發器與資料變更追蹤機制完整實作指南

-- 建立選民資料稽核記錄表
-- 此表格用於儲存選民資料表的所有變更歷程
CREATE TABLE voter_audit (
    -- 稽核記錄的唯一識別碼
    -- 使用自動遞增主鍵確保每筆記錄都有唯一ID
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- 資料變更發生的時間戳記
    -- 記錄精確到秒的變更時間
    audit_datetime DATETIME NOT NULL,
    
    -- 執行變更操作的資料庫使用者帳號
    -- 追蹤是誰執行了這次變更
    audit_user VARCHAR(255) NOT NULL,
    
    -- 變更內容的詳細描述
    -- 記錄哪些欄位被修改以及新舊值
    audit_change TEXT NOT NULL,
    
    -- 建立索引以加速時間範圍查詢
    INDEX idx_audit_datetime (audit_datetime),
    
    -- 建立索引以加速使用者操作查詢
    INDEX idx_audit_user (audit_user)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='選民資料變更稽核記錄表';

-- 刪除既有的觸發器(如果存在)
-- 這確保我們可以重新建立或更新觸發器定義
DROP TRIGGER IF EXISTS tr_voter_au;

-- 暫時變更陳述式結束符號
-- 因為觸發器定義本身包含分號,需要使用不同的結束符號
DELIMITER //

-- 建立選民資料更新觸發器
-- AFTER UPDATE表示在資料更新完成後執行
-- FOR EACH ROW表示對每一列受影響的資料都執行一次
CREATE TRIGGER tr_voter_au
AFTER UPDATE ON voter
FOR EACH ROW
BEGIN
    -- 宣告變數用於儲存變更描述訊息
    -- 初始化為空字串,後續會逐步拼接變更內容
    DECLARE change_msg TEXT DEFAULT '';
    
    -- 檢查選民姓名是否變更
    -- OLD代表更新前的值,NEW代表更新後的值
    IF NEW.voter_name != OLD.voter_name THEN
        -- 拼接姓名變更訊息
        -- 記錄欄位名稱、舊值與新值
        SET change_msg = CONCAT(
            change_msg,
            '選民姓名從「', OLD.voter_name, 
            '」變更為「', NEW.voter_name, '」'
        );
    END IF;
    
    -- 檢查選民地址是否變更
    IF NEW.voter_address != OLD.voter_address THEN
        -- 如果已有其他變更訊息,加上分隔符號
        IF LENGTH(change_msg) > 0 THEN
            SET change_msg = CONCAT(change_msg, '; ');
        END IF;
        -- 拼接地址變更訊息
        SET change_msg = CONCAT(
            change_msg,
            '選民地址從「', OLD.voter_address,
            '」變更為「', NEW.voter_address, '」'
        );
    END IF;
    
    -- 檢查選民所屬縣市是否變更
    IF NEW.voter_county != OLD.voter_county THEN
        IF LENGTH(change_msg) > 0 THEN
            SET change_msg = CONCAT(change_msg, '; ');
        END IF;
        SET change_msg = CONCAT(
            change_msg,
            '選民縣市從「', OLD.voter_county,
            '」變更為「', NEW.voter_county, '」'
        );
    END IF;
    
    -- 檢查選民所屬選區是否變更
    IF NEW.voter_district != OLD.voter_district THEN
        IF LENGTH(change_msg) > 0 THEN
            SET change_msg = CONCAT(change_msg, '; ');
        END IF;
        SET change_msg = CONCAT(
            change_msg,
            '選民選區從「', OLD.voter_district,
            '」變更為「', NEW.voter_district, '」'
        );
    END IF;
    
    -- 檢查選民所屬投票區是否變更
    IF NEW.voter_precinct != OLD.voter_precinct THEN
        IF LENGTH(change_msg) > 0 THEN
            SET change_msg = CONCAT(change_msg, '; ');
        END IF;
        SET change_msg = CONCAT(
            change_msg,
            '選民投票區從「', OLD.voter_precinct,
            '」變更為「', NEW.voter_precinct, '」'
        );
    END IF;
    
    -- 檢查選民所屬政黨是否變更
    IF NEW.voter_party != OLD.voter_party THEN
        IF LENGTH(change_msg) > 0 THEN
            SET change_msg = CONCAT(change_msg, '; ');
        END IF;
        SET change_msg = CONCAT(
            change_msg,
            '選民政黨從「', OLD.voter_party,
            '」變更為「', NEW.voter_party, '」'
        );
    END IF;
    
    -- 只有在確實有欄位變更時才寫入稽核記錄
    -- 避免記錄沒有實質變更的更新操作
    IF LENGTH(change_msg) > 0 THEN
        -- 將變更資訊插入稽核記錄表
        -- NOW()取得當前時間戳記
        -- USER()取得執行操作的資料庫使用者帳號
        INSERT INTO voter_audit (
            audit_datetime,
            audit_user,
            audit_change
        ) VALUES (
            NOW(),
            USER(),
            CONCAT('選民ID ', OLD.voter_id, ': ', change_msg)
        );
    END IF;
END//

-- 恢復預設的陳述式結束符號
DELIMITER ;

-- 執行測試更新操作
-- 同時修改選民的姓名與政黨資料
UPDATE voter
SET voter_name = '李明華',
    voter_party = '民主黨'
WHERE voter_id = 5876;

-- 查詢稽核記錄表驗證觸發器運作
-- 應該能看到剛才更新操作的完整記錄
SELECT 
    audit_id,
    audit_datetime,
    audit_user,
    audit_change
FROM voter_audit
ORDER BY audit_datetime DESC
LIMIT 10;

-- 建立通用稽核記錄表
-- 用於記錄多個資料表的變更歷程
CREATE TABLE audit_log (
    -- 稽核記錄唯一識別碼
    audit_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- 發生變更的資料表名稱
    -- 用於識別變更來源
    table_name VARCHAR(100) NOT NULL,
    
    -- 受影響資料列的主鍵值
    -- 記錄是哪一筆資料被修改
    record_id VARCHAR(255) NOT NULL,
    
    -- 操作類型: INSERT, UPDATE, DELETE
    operation_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    
    -- 變更發生的時間戳記
    audit_datetime DATETIME NOT NULL,
    
    -- 執行操作的資料庫使用者
    audit_user VARCHAR(255) NOT NULL,
    
    -- 變更內容的詳細描述
    audit_change TEXT,
    
    -- 建立複合索引以支援常見查詢模式
    INDEX idx_table_datetime (table_name, audit_datetime),
    INDEX idx_user_datetime (audit_user, audit_datetime),
    INDEX idx_record (table_name, record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='通用稽核記錄表';

-- 為候選人資料表建立更新觸發器
DROP TRIGGER IF EXISTS tr_candidate_au;

DELIMITER //

CREATE TRIGGER tr_candidate_au
AFTER UPDATE ON candidate
FOR EACH ROW
BEGIN
    DECLARE change_msg TEXT DEFAULT '';
    
    -- 檢查候選人姓名變更
    IF NEW.candidate_name != OLD.candidate_name THEN
        SET change_msg = CONCAT(
            change_msg,
            '候選人姓名從「', OLD.candidate_name,
            '」變更為「', NEW.candidate_name, '」'
        );
    END IF;
    
    -- 檢查所屬政黨變更
    IF NEW.candidate_party != OLD.candidate_party THEN
        IF LENGTH(change_msg) > 0 THEN
            SET change_msg = CONCAT(change_msg, '; ');
        END IF;
        SET change_msg = CONCAT(
            change_msg,
            '所屬政黨從「', OLD.candidate_party,
            '」變更為「', NEW.candidate_party, '」'
        );
    END IF;
    
    -- 檢查競選職位變更
    IF NEW.candidate_position != OLD.candidate_position THEN
        IF LENGTH(change_msg) > 0 THEN
            SET change_msg = CONCAT(change_msg, '; ');
        END IF;
        SET change_msg = CONCAT(
            change_msg,
            '競選職位從「', OLD.candidate_position,
            '」變更為「', NEW.candidate_position, '」'
        );
    END IF;
    
    -- 寫入通用稽核記錄表
    IF LENGTH(change_msg) > 0 THEN
        INSERT INTO audit_log (
            table_name,
            record_id,
            operation_type,
            audit_datetime,
            audit_user,
            audit_change
        ) VALUES (
            'candidate',
            OLD.candidate_id,
            'UPDATE',
            NOW(),
            USER(),
            change_msg
        );
    END IF;
END//

DELIMITER ;

-- 查詢特定使用者的所有操作記錄
-- 按時間倒序排列,最新的記錄在最上方
SELECT 
    table_name AS '資料表',
    record_id AS '記錄ID',
    operation_type AS '操作類型',
    audit_datetime AS '變更時間',
    audit_change AS '變更內容'
FROM audit_log
WHERE audit_user = 'admin@localhost'
ORDER BY audit_datetime DESC
LIMIT 50;

-- 查詢特定時段內所有資料表的變更統計
-- 按資料表與操作類型分組計數
SELECT 
    table_name AS '資料表',
    operation_type AS '操作類型',
    COUNT(*) AS '變更次數'
FROM audit_log
WHERE audit_datetime BETWEEN '2025-11-01' AND '2025-11-30'
GROUP BY table_name, operation_type
ORDER BY table_name, operation_type;

-- 追蹤特定資料列的完整變更歷程
-- 查看某筆候選人資料的所有修改記錄
SELECT 
    audit_datetime AS '變更時間',
    audit_user AS '操作使用者',
    operation_type AS '操作類型',
    audit_change AS '變更內容'
FROM audit_log
WHERE table_name = 'candidate'
  AND record_id = '1234'
ORDER BY audit_datetime ASC;

-- 建立企業人事資料庫
CREATE DATABASE IF NOT EXISTS business
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

USE business;

-- 建立員工基本資料表
-- 包含敏感的薪資資訊
CREATE TABLE employee (
    -- 員工唯一識別碼
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    
    -- 員工姓名
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    
    -- 部門資訊
    department VARCHAR(100) NOT NULL,
    
    -- 職稱
    job_title VARCHAR(100) NOT NULL,
    
    -- 薪資資訊(敏感欄位)
    -- 只有人力資源部門有權存取
    salary DECIMAL(15, 2) NOT NULL,
    
    -- 建立索引以加速部門查詢
    INDEX idx_department (department),
    
    -- 建立索引以加速姓名查詢
    INDEX idx_name (last_name, first_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='員工基本資料表';

-- 插入測試資料
INSERT INTO employee (first_name, last_name, department, job_title, salary)
VALUES 
    ('志明', '陳', '會計部', '會計專員', 81200.00),
    ('美玲', '林', '會計部', '財務長', 246000.00),
    ('俊傑', '王', '行銷部', '創意總監', 178000.00),
    ('雅婷', '張', '資訊部', '程式設計師', 119500.00),
    ('建國', '李', '法務部', '法遵主管', 157000.00);

-- 驗證資料已正確插入
SELECT 
    employee_id,
    CONCAT(last_name, first_name) AS '姓名',
    department AS '部門',
    job_title AS '職稱',
    salary AS '月薪'
FROM employee;

-- 建立員工基本資料檢視表
-- 排除敏感的薪資欄位
CREATE VIEW v_employee_basic AS
SELECT 
    employee_id,
    first_name,
    last_name,
    department,
    job_title
FROM employee;

-- 一般使用者查詢此檢視表時
-- 只能看到基本資訊,無法取得薪資資料
SELECT 
    employee_id AS '員工編號',
    CONCAT(last_name, first_name) AS '姓名',
    department AS '部門',
    job_title AS '職稱'
FROM v_employee_basic
ORDER BY department, last_name;

-- 建立人力資源部使用者帳號
-- 此帳號需要完整存取員工資料表的權限
CREATE USER IF NOT EXISTS 'hr_manager'@'localhost'
IDENTIFIED BY 'SecurePassword123!';

-- 授予人力資源部使用者對員工資料表的完整權限
-- 包含查詢、插入、更新與刪除操作
GRANT SELECT, INSERT, UPDATE, DELETE 
ON business.employee 
TO 'hr_manager'@'localhost';

-- 授予人力資源部使用者對稽核記錄表的查詢權限
-- 允許查看所有的資料變更歷程
GRANT SELECT 
ON business.audit_log 
TO 'hr_manager'@'localhost';

-- 建立會計部使用者帳號
-- 此帳號只能存取基本資料檢視表
CREATE USER IF NOT EXISTS 'accounting_user'@'localhost'
IDENTIFIED BY 'SecurePassword456!';

-- 授予會計部使用者對基本資料檢視表的查詢權限
-- 無法看到敏感的薪資資訊
GRANT SELECT 
ON business.v_employee_basic 
TO 'accounting_user'@'localhost';

-- 建立資訊部使用者帳號
CREATE USER IF NOT EXISTS 'it_user'@'localhost'
IDENTIFIED BY 'SecurePassword789!';

-- 授予資訊部使用者對基本資料檢視表的查詢權限
GRANT SELECT 
ON business.v_employee_basic 
TO 'it_user'@'localhost';

-- 刷新權限使變更立即生效
FLUSH PRIVILEGES;

-- 撤銷使用者的特定權限
-- 例如當員工調離人力資源部門時
REVOKE DELETE, UPDATE 
ON business.employee 
FROM 'hr_manager'@'localhost';

-- 查詢特定使用者的現有權限
-- 用於權限稽核與檢視
SHOW GRANTS FOR 'accounting_user'@'localhost';

-- 完全移除使用者帳號
-- 當員工離職時清除其資料庫存取權限
DROP USER IF EXISTS 'former_employee'@'localhost';

-- 刷新權限確保變更立即生效
FLUSH PRIVILEGES;