触发器开发：审计字段自动维护——业务表 DDL、ORM 适配与事务边界

每日一句正能量

要么滚回舒适区苟且，要么站出来创造新的秩序。
诚实地评估自己：是选择接受现状（包括其所有不如意），还是愿意承担风险、痛苦和不确定性，去开辟一条新路？没有中间地带。你现在的处境，是你的选择。

前言

在生产系统里，几乎每张核心业务表都会出现一组相似字段：

created_at created_by updated_at updated_by

它们看起来简单，却非常容易失真。

如果完全依赖应用代码维护，那么只要存在一个漏写路径，审计数据就会出现空洞。例如：

Java 服务写入时维护了 updated_at 运维脚本直接 UPDATE 时没有维护 ETL 批处理使用另一套 SQL 历史服务版本漏掉 updated_by

最终数据库里的业务数据是对的，但“谁在什么时候改过”却不可信。

触发器的价值就在这里：它可以把审计字段的维护下沉到数据库，使所有写入路径都遵守同一规则。

但触发器也不是越多越好。真正的工程难点在于：

触发器如何拿到操作人？ 应用和触发器谁优先？ 批量更新时性能如何？ ORM 能否感知触发器修改后的值？ 触发器异常会不会影响主事务？

本文以业务表审计字段自动维护为例，分别给出 MySQL 与 PostgreSQL 的实现，并说明 JDBC、MyBatis、JPA/Hibernate 的适配方式，以及触发器在事务中的真实边界。

1. 背景与问题

先看一张普通订单表：

CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(64)NOTNULLUNIQUE,user_idBIGINTNOTNULL,amountDECIMAL(18,2)NOTNULL,statusVARCHAR(32)NOTNULL,created_atTIMESTAMPNULL,created_byVARCHAR(64)NULL,updated_atTIMESTAMPNULL,updated_byVARCHAR(64)NULL);

如果全部依赖应用维护：

order.setUpdatedAt(Instant.now());order.setUpdatedBy(currentUser);repository.save(order);

单个 Java 服务里没有问题。

真正的问题是数据库往往不是只被一个代码路径访问。

例如：

UPDATEordersSETstatus='CANCELLED'WHEREorder_no='O-1001';

这条 SQL 如果由人工脚本、定时任务或旧服务执行，updated_at和updated_by就可能完全没变。

久而久之，审计字段失去价值。

触发器提供了一种更靠近数据的兜底机制：

只要 INSERT / UPDATE 真正发生， 审计字段就在数据库内自动维护。

2. 环境与数据

示例环境：

JDK 21 Spring Boot 3.3+ MySQL 8.0+ PostgreSQL 15+ HikariCP Spring JDBC MyBatis 3.x Hibernate 6 / JPA

MySQL 业务表：

CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(64)NOTNULL,user_idBIGINTNOTNULL,amountDECIMAL(18,2)NOTNULL,statusVARCHAR(32)NOTNULL,created_atTIMESTAMP(6)NOTNULL,created_byVARCHAR(64)NOTNULL,updated_atTIMESTAMP(6)NOTNULL,updated_byVARCHAR(64)NOTNULL,UNIQUEKEYuk_order_no(order_no),KEYidx_user_status(user_id,status));

为什么不简单写：

updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP

因为自动时间戳只能解决：

updated_at

却解决不了：

updated_by

而且很多团队还需要自定义“系统任务”“人工补单”“接口用户”等操作人。

因此完整方案通常仍需要触发器。

3. 复现过程

3.1 复现审计字段遗漏

先不创建触发器。

插入：

INSERTINTOorders(order_no,user_id,amount,status,created_at,created_by,updated_at,updated_by)VALUES('O-1001',101,199.00,'CREATED',NOW(6),'order-service',NOW(6),'order-service');

然后执行一个遗漏审计字段的更新：

UPDATEordersSETstatus='PAID'WHEREorder_no='O-1001';

查询：

SELECTorder_no,status,created_at,created_by,updated_at,updated_byFROMordersWHEREorder_no='O-1001';

你会看到：

status 已经变化 updated_at 仍然是旧值 updated_by 仍然是 order-service

这说明审计链已经不可信。

3.2 批量任务更容易暴露问题

例如夜间任务：

UPDATEordersSETstatus='EXPIRED'WHEREstatus='CREATED'ANDcreated_at<NOW()-INTERVAL30MINUTE;

如果更新了十万行，而 SQL 没有维护审计字段，那么十万条记录都会失去准确的最后修改信息。

这正是触发器适合兜底的场景。

4. 方案实施

4.1 MySQL：使用会话变量传递操作人

MySQL 触发器本身不知道“当前登录 Web 用户是谁”。

数据库看到的通常只是：

app_user

所以应用需要在当前数据库连接上写入会话上下文。

例如：

SET@audit_user='alice';

然后触发器读取：

@audit_user

这是一个常见实现方式。

4.2 BEFORE INSERT 触发器

DELIMITER$$CREATETRIGGERtrg_orders_bi_audit BEFOREINSERTONordersFOR EACH ROWBEGINSETNEW.created_at=CURRENT_TIMESTAMP(6);SETNEW.updated_at=CURRENT_TIMESTAMP(6);SETNEW.created_by=COALESCE(@audit_user,'system');SETNEW.updated_by=COALESCE(@audit_user,'system');END$$DELIMITER;

这里统一保证：

created_at updated_at created_by updated_by

首次写入时都有值。

4.3 BEFORE UPDATE 触发器

DELIMITER$$CREATETRIGGERtrg_orders_bu_audit BEFOREUPDATEONordersFOR EACH ROWBEGINSETNEW.created_at=OLD.created_at;SETNEW.created_by=OLD.created_by;SETNEW.updated_at=CURRENT_TIMESTAMP(6);SETNEW.updated_by=COALESCE(@audit_user,'system');END$$DELIMITER;

这里故意把：

created_*

恢复成 OLD 值。

这可以防止某些应用错误地传入：

created_at = now()

覆盖原始创建信息。

4.4 JDBC：必须在同一个 Connection 设置上下文

错误方式：

jdbcTemplate.execute("SET @audit_user='alice'");jdbcTemplate.update("UPDATE orders SET status=? WHERE id=?","PAID",orderId);

如果没有事务，两个 SQL 有可能拿到不同连接。

会话变量属于：

物理数据库连接

所以必须确保：

SET context UPDATE

使用同一个连接。

最直接 JDBC：

publicvoidupdateStatus(longorderId,Stringstatus,Stringoperator)throwsSQLException{try(Connectionc=dataSource.getConnection()){c.setAutoCommit(false);try{try(PreparedStatementps=c.prepareStatement("SET @audit_user = ?")){ps.setString(1,operator);ps.execute();}try(PreparedStatementps=c.prepareStatement(""" UPDATE orders SET status = ? WHERE id = ? """)){ps.setString(1,status);ps.setLong(2,orderId);ps.executeUpdate();}c.commit();}catch(Exceptione){c.rollback();throwe;}}}

这段代码的关键不是语法，而是：

上下文设置与业务 SQL 在同一个连接、同一个事务中执行。

4.5 Spring JdbcTemplate 适配

推荐在事务里：

@TransactionalpublicvoidupdateOrderStatus(longorderId,Stringstatus,Stringoperator){jdbcTemplate.update("SET @audit_user = ?",operator);jdbcTemplate.update(""" UPDATE orders SET status = ? WHERE id = ? """,status,orderId);}

Spring 事务会绑定当前线程使用的连接，因此两个语句通常会复用同一个事务连接。

事务结束后，连接会回到连接池。

4.6 必须清理会话上下文吗？

这是连接池场景下非常重要的边界。

物理连接不会真正关闭，而是被放回池中。

如果：

请求 A 设置 @audit_user='alice' 请求结束 连接回池 请求 B 拿到同一个物理连接 请求 B 没有重新设置 @audit_user

B 可能错误继承：

alice

因此要保证每个写事务都显式设置上下文。

更严格的做法是在 finally 中：

SET@audit_user=NULL;

例如：

finally{jdbcTemplate.execute("SET @audit_user = NULL");}

但这也必须在正确连接上下文中执行。

最稳妥的原则是：

每个写事务开始时都重新设置， 绝不依赖连接上残留值。

4.7 MyBatis 适配

可以准备一个上下文 Mapper：

<updateid="setAuditUser">SET @audit_user = #{operator}</update>

业务 Mapper：

<updateid="updateStatus">UPDATE orders SET status = #{status} WHERE id = #{orderId}</update>

Service：

@TransactionalpublicvoidupdateStatus(longorderId,Stringstatus,Stringoperator){auditMapper.setAuditUser(operator);orderMapper.updateStatus(orderId,status);}

只要两个 Mapper 使用：

同一个 DataSource 同一个 SqlSession 同一个 Spring 事务

触发器就能读取到该连接上的会话变量。

4.8 JPA/Hibernate 适配

JPA 可以通过 Native Query 设置：

@TransactionalpublicvoidupdateOrder(longid,Stringstatus,Stringoperator){entityManager.createNativeQuery("SET @audit_user = :operator").setParameter("operator",operator).executeUpdate();OrderEntityorder=entityManager.find(OrderEntity.class,id);order.setStatus(status);}

提交时 Hibernate 发出 UPDATE，数据库触发器自动维护：

updated_at updated_by

4.9 ORM 的“实体值不同步”问题

这是触发器与 ORM 一起使用时最容易忽略的问题。

数据库触发器已经把：

updated_at updated_by

改成新值。

但当前 Hibernate Entity 内存里可能仍然是旧值。

如果业务代码马上：

order.getUpdatedAt();

拿到的不一定是数据库触发器后的值。

解决方式：

entityManager.flush();entityManager.refresh(order);

例如：

order.setStatus("PAID");entityManager.flush();entityManager.refresh(order);

此时实体重新从数据库读取。

如果当前事务不需要马上使用新审计值，也可以等下一次查询再读取，避免额外一次 SELECT。

4.10 PostgreSQL 触发器实现

PostgreSQL 推荐通过：

trigger function

实现。

CREATEORREPLACEFUNCTIONfn_orders_audit()RETURNStriggerLANGUAGEplpgsqlAS$$DECLAREv_userTEXT;BEGINv_user :=COALESCE(current_setting('app.audit_user',true),'system');IFTG_OP='INSERT'THENNEW.created_at=clock_timestamp();NEW.updated_at=NEW.created_at;NEW.created_by=v_user;NEW.updated_by=v_user;ELSIF TG_OP='UPDATE'THENNEW.created_at=OLD.created_at;NEW.created_by=OLD.created_by;NEW.updated_at=clock_timestamp();NEW.updated_by=v_user;ENDIF;RETURNNEW;END;$$;

创建触发器：

CREATETRIGGERtrg_orders_audit BEFOREINSERTORUPDATEONordersFOR EACH ROWEXECUTEFUNCTIONfn_orders_audit();

设置操作人：

SETLOCALapp.audit_user='alice';

SET LOCAL很适合事务，因为作用域局限于当前事务。

4.11 为什么 PostgreSQL 的 SET LOCAL 更舒服

MySQL 会话变量跟随连接。

PostgreSQL：

SETLOCALapp.audit_user='alice';

只在当前事务有效。

事务结束后自动恢复。

这减少了连接池复用导致上下文泄漏的风险。

4.12 事务边界：触发器不是独立提交

假设：

@TransactionalpublicvoidpayOrder(){setAuditUser("alice");orderRepository.markPaid();ledgerRepository.insert();thrownewRuntimeException("injected failure");}

虽然：

markPaid

期间触发器已经执行并更新：

updated_at updated_by

但最终事务回滚时：

业务字段 审计字段 流水

都会一起回滚。

这是一个非常重要的事实：

触发器默认不是独立事务。

它属于触发它的 DML 语句和当前事务。

4.13 触发器异常也会让主语句失败

可以加输入保护：

IFCOALESCE(@audit_user,'')=''THENSIGNAL SQLSTATE'45000'SETMESSAGE_TEXT='audit_user is required';ENDIF;

这时应用忘记设置操作人：

UPDATE 直接失败

如果业务要求强审计，这种策略是合理的。

如果系统存在大量脚本和历史任务，则可以先：

缺失时使用 system

然后逐步收紧。

4.14 BEFORE 与 AFTER 怎么选

对于修改：

NEW.updated_at NEW.updated_by

应该使用：

BEFORE INSERT / BEFORE UPDATE

因为你要修改即将写入的行。

AFTER 触发器更适合：

写额外审计表 记录变更历史 事件记录

但如果只是更新当前行字段，BEFORE 更直接。

4.15 批量 UPDATE 的影响

例如：

UPDATEordersSETstatus='EXPIRED'WHEREstatus='CREATED';

触发器是：

FOR EACH ROW

意味着命中 10 万行，就执行 10 万次触发器逻辑。

所以触发器内容必须轻量。

不要在里面：

复杂 SELECT 跨大表 JOIN 远程调用 昂贵函数

4.16 触发器和 DEFAULT 谁负责 created_at

推荐明确职责。

一种清晰方案：

DEFAULT 作为基础兜底 Trigger 作为完整规则

例如：

created_atTIMESTAMP(6)NOTNULLDEFAULTCURRENT_TIMESTAMP(6)

即使未来触发器被临时移除，created_at 仍然不会为空。

但 created_by 依旧需要应用上下文或触发器处理。

4.17 DDL 版本管理

触发器不能靠 DBA 手工维护。

建议放入 Flyway：

V169_01__create_orders.sql V169_02__create_orders_audit_trigger.sql

PostgreSQL：

V169_03__create_audit_trigger_function.sql

这样触发器版本和应用版本可以一起追踪。

4.18 测试：INSERT

@TestvoidinsertShouldFillAuditFields(){orderService.create("O-2001","alice");Orderorder=repository.findByOrderNo("O-2001");assertNotNull(order.createdAt());assertNotNull(order.updatedAt());assertEquals("alice",order.createdBy());assertEquals("alice",order.updatedBy());}

4.19 测试：UPDATE 不能改变 created 字段

@TestvoidupdateShouldKeepCreatedFields(){Orderbefore=repository.findByOrderNo("O-2001");orderService.updateStatus(before.id(),"PAID","bob");Orderafter=repository.findByOrderNo("O-2001");assertEquals(before.createdAt(),after.createdAt());assertEquals(before.createdBy(),after.createdBy());assertEquals("bob",after.updatedBy());}

4.20 测试：事务回滚

@TestvoidauditFieldsShouldRollbackWithBusiness(){assertThrows(RuntimeException.class,()->orderService.updateThenFail(1001L,"PAID","alice"));Orderorder=repository.findById(1001L);assertNotEquals("PAID",order.status());}

如果主事务失败，触发器对审计字段的修改也应该消失。

4.21 测试矩阵

至少要覆盖：

INSERT UPDATE 批量 UPDATE 无操作人 事务回滚 ORM refresh 脚本直连 连接池复用

只有这样，审计字段方案才真正可用。

5. 结果对比

实施前

应用代码：

每个 Mapper / Repository 手工维护 updated_at 手工维护 updated_by

风险：

漏写 字段覆盖 脚本不统一 历史服务不一致

数据库里的审计字段只是“尽量正确”。

实施后

应用：

事务开始 -> 设置 audit_user -> 执行业务 DML

数据库：

BEFORE INSERT -> created_* -> updated_* BEFORE UPDATE -> 保留 created_* -> 自动刷新 updated_*

无论写入来源是：

JDBC MyBatis Hibernate 脚本 批任务

最终规则都在数据库层统一执行。

6. 风险与复盘

6.1 触发器会增加“隐式行为”

开发人员看到：

UPDATEordersSETstatus='PAID'WHEREid=1;

并不能从 SQL 本身看出：

updated_at updated_by

也发生了变化。

所以触发器必须：

有文档 有版本管理 有测试

6.2 不要在触发器里塞大量业务逻辑

审计字段维护很适合触发器，因为规则简单且与数据强相关。

但不要把：

发消息 复杂结算 跨服务调用 大表聚合

塞进触发器。

这会让事务变长，也让问题难排查。

6.3 会话变量要考虑连接池

MySQL 连接池最容易出现：

上下文残留

所以不要假设：

连接天然干净。

每个写事务必须重新设置操作人。

6.4 ORM 需要考虑回读

数据库触发器修改了字段，不代表 Java 实体自动同步。

需要：

flush + refresh

或重新查询。

否则应用日志可能打印旧值。

6.5 批量写入要压测

触发器按行执行。

10 万行 UPDATE 就会调用 10 万次。

审计触发器应保持：

无额外查询 无复杂计算 无外部依赖

6.6 触发器失败会影响主事务

这是优点也是风险。

优点：

强制审计规则。

风险：

触发器 bug 会阻塞所有写入。

因此触发器上线必须像应用代码一样经过：

测试 灰度 回滚脚本

结语

审计字段自动维护是触发器最适合的使用场景之一，因为它满足三个特征：

规则简单 强依赖数据写入 必须覆盖所有入口

一个成熟方案应该明确：

created_* 只在首次写入生成 updated_* 每次变更自动刷新 操作人通过事务上下文传递 触发器与主 DML 同事务提交 ORM 在需要时主动 refresh

可以把核心原则总结为：

应用负责传递“谁在操作”， 触发器负责保证“审计字段一定被写对”， 事务负责保证“业务和审计一起成功或一起回滚”。

只有把这三层边界设计清楚，触发器才会成为可靠的数据治理工具，而不是隐藏在数据库里的“神秘副作用”。

转载自：https://blog.csdn.net/u014727709/article/details/165243044
欢迎 👍点赞✍评论⭐收藏，欢迎指正