萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 觸發器實戰範例:INSERT/UPDATE/DELETE 審計日誌實作

場景:業務表變更自動寫入歷史表

主表 test、歷史表 test_hisy,記錄操作類型、時間、前後值。

表結構定義

CREATE TABLE `test` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL DEFAULT '',
  `type` VARCHAR(100),
  `create_time` DATETIME,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `test_hisy` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL DEFAULT '',
  `type` VARCHAR(100),
  `create_time` DATETIME,
  `operation` VARCHAR(20) COMMENT 'insert/update/delete',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

觸發器定義

1. AFTER INSERT:新增記錄同步

DELIMITER //
CREATE TRIGGER `t_after_insert_test` AFTER INSERT ON `test`
FOR EACH ROW
BEGIN
  IF NEW.`type` = '1' THEN
    INSERT INTO `test_hisy` (`name`, `type`, `create_time`, `operation`)
    VALUES (NEW.`name`, NEW.`type`, NEW.`create_time`, 'insert');
  END IF;
END//
DELIMITER ;

2. BEFORE UPDATE:修改前值備份 (type='2' 才記錄)

DELIMITER //
CREATE TRIGGER `t_before_update_test` BEFORE UPDATE ON `test`
FOR EACH ROW
BEGIN
  IF NEW.`type` = '2' THEN
    INSERT INTO `test_hisy` (`name`, `type`, `create_time`, `operation`)
    VALUES (OLD.`name`, OLD.`type`, OLD.`create_time`, 'update');
  END IF;
END//
DELIMITER ;

3. BEFORE DELETE:刪除前完整備份

DELIMITER //
CREATE TRIGGER `t_before_delete_test` BEFORE DELETE ON `test`
FOR EACH ROW
BEGIN
  INSERT INTO `test_hisy` (`name`, `type`, `create_time`, `operation`)
  VALUES (OLD.`name`, OLD.`type`, OLD.`create_time`, 'delete');
END//
DELIMITER ;

關鍵概念:NEW vs OLD

事件NEWOLD
INSERT新值 ✓不可用
UPDATE新值 ✓舊值 ✓
DELETE不可用舊值 ✓

進階用法與注意事項

  • 條件觸發:IF NEW.status != OLD.status THEN ... 只記錄狀態變更
  • 存取其他表:觸發器內可查詢/修改其他表,但不能修改觸發觸發器的表 (避免遞迴)
  • 事務關係:觸發器在觸發語句的事務內執行,觸發器拋錯誤 → 主事務回滾
  • 效能影響:高頻寫入表慎用,每行觸發增加 CPU/I/O;考慮異步寫入 (Binlog 解析 / Canal / Kafka)
  • 備份/遷移:mysqldump --triggers 包含觸發器;SHOW TRIGGERS 查看
  • 權限:需 TRIGGER 權限 (MySQL 5.1.6+)

查看與管理

SHOW TRIGGERS\G
DROP TRIGGER IF EXISTS `t_after_insert_test`;
copyright © 萬盛學電腦網 all rights reserved