在数据库应用中，记录表行级的增删改历史是数据审计、问题排查、合规校验的核心需求，通过数据库触发器实现审计追踪，无需修改业务代码即可自动捕获所有数据变更操作，是低侵入且高效的实现方案。
为什么选择触发器实现行级修改历史记录
触发器是数据库内置的特殊存储过程，会在指定的表发生INSERT、UPDATE、DELETE操作时自动触发执行，相比业务层记录变更历史，它有如下优势：
- 无业务代码侵入，不需要修改现有业务逻辑即可生效
- 所有变更操作都会被捕获，包括直接通过数据库工具执行的SQL操作
- 记录时机和事务绑定，数据一致性更高
审计表结构设计
首先需要创建一张审计表，用来存储所有行级修改的历史记录，通用结构如下：
CREATE TABLE audit_log (
log_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
table_name VARCHAR(100) NOT NULL COMMENT '被修改的表名',
operation_type VARCHAR(10) NOT NULL COMMENT '操作类型：INSERT/UPDATE/DELETE',
record_id VARCHAR(100) COMMENT '被修改行的主键ID',
old_value TEXT COMMENT '修改前的行数据，JSON格式',
new_value TEXT COMMENT '修改后的行数据，JSON格式',
operator VARCHAR(100) COMMENT '操作人',
operate_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间'
) COMMENT '行级修改审计日志表';
MySQL中通过触发器实现审计追踪
假设我们需要审计user_info表的行级修改，该表主键为user_id，下面分别创建三种操作的触发器。
INSERT操作触发器
DELIMITER //
CREATE TRIGGER tr_user_info_insert
AFTER INSERT ON user_info
FOR EACH ROW
BEGIN
INSERT INTO audit_log (
table_name,
operation_type,
record_id,
new_value,
operator
) VALUES (
'user_info',
'INSERT',
NEW.user_id,
CONCAT('{"user_id":"', NEW.user_id, '","user_name":"', NEW.user_name, '","age":"', NEW.age, '"}'),
CURRENT_USER()
);
END //
DELIMITER ;
UPDATE操作触发器
DELIMITER //
CREATE TRIGGER tr_user_info_update
AFTER UPDATE ON user_info
FOR EACH ROW
BEGIN
INSERT INTO audit_log (
table_name,
operation_type,
record_id,
old_value,
new_value,
operator
) VALUES (
'user_info',
'UPDATE',
NEW.user_id,
CONCAT('{"user_id":"', OLD.user_id, '","user_name":"', OLD.user_name, '","age":"', OLD.age, '"}'),
CONCAT('{"user_id":"', NEW.user_id, '","user_name":"', NEW.user_name, '","age":"', NEW.age, '"}'),
CURRENT_USER()
);
END //
DELIMITER ;
DELETE操作触发器
DELIMITER //
CREATE TRIGGER tr_user_info_delete
AFTER DELETE ON user_info
FOR EACH ROW
BEGIN
INSERT INTO audit_log (
table_name,
operation_type,
record_id,
old_value,
operator
) VALUES (
'user_info',
'DELETE',
OLD.user_id,
CONCAT('{"user_id":"', OLD.user_id, '","user_name":"', OLD.user_name, '","age":"', OLD.age, '"}'),
CURRENT_USER()
);
END //
DELIMITER ;
SQL Server中通过触发器实现审计追踪
SQL Server的触发器逻辑和MySQL类似，但是语法有差异，同样以user_info表为例：
-- INSERT触发器
CREATE TRIGGER tr_user_info_insert
ON user_info
AFTER INSERT
AS
BEGIN
INSERT INTO audit_log (
table_name,
operation_type,
record_id,
new_value,
operator
)
SELECT
'user_info',
'INSERT',
user_id,
'{"user_id":"' + CAST(user_id AS VARCHAR) + '","user_name":"' + user_name + '","age":"' + CAST(age AS VARCHAR) + '"}',
SUSER_SNAME()
FROM inserted;
END
GO
-- UPDATE触发器
CREATE TRIGGER tr_user_info_update
ON user_info
AFTER UPDATE
AS
BEGIN
INSERT INTO audit_log (
table_name,
operation_type,
record_id,
old_value,
new_value,
operator
)
SELECT
'user_info',
'UPDATE',
i.user_id,
'{"user_id":"' + CAST(d.user_id AS VARCHAR) + '","user_name":"' + d.user_name + '","age":"' + CAST(d.age AS VARCHAR) + '"}',
'{"user_id":"' + CAST(i.user_id AS VARCHAR) + '","user_name":"' + i.user_name + '","age":"' + CAST(i.age AS VARCHAR) + '"}',
SUSER_SNAME()
FROM inserted i
JOIN deleted d ON i.user_id = d.user_id;
END
GO
-- DELETE触发器
CREATE TRIGGER tr_user_info_delete
ON user_info
AFTER DELETE
AS
BEGIN
INSERT INTO audit_log (
table_name,
operation_type,
record_id,
old_value,
operator
)
SELECT
'user_info',
'DELETE',
user_id,
'{"user_id":"' + CAST(user_id AS VARCHAR) + '","user_name":"' + user_name + '","age":"' + CAST(age AS VARCHAR) + '"}',
SUSER_SNAME()
FROM deleted;
END
GO
方案优缺点分析
| 优点 | 缺点 |
|---|---|
| 无业务代码侵入，实现简单 | 会增加数据库写操作的开销，高并发场景可能影响性能 |
| 所有变更都会被捕获，无遗漏 | 触发器逻辑和数据库绑定，迁移数据库时需要重新适配 |
| 事务一致性高，不会出现业务记录和实际变更不一致的情况 | 审计表数据会持续增长，需要定期归档清理 |
适用场景
该方案适合对数据审计要求高、业务代码不便修改、数据变更频率中等的场景，如果是超高并发的写密集业务，建议结合业务层审计或者异步消息队列的方式实现，避免影响数据库核心性能。