如何实现SQL分级审批逻辑_利用触发器更新审批状态-SQL-PHP中文网

触发器中禁止直接提交事务，因其破坏事务原子性；应将审批逻辑移至应用层或存储过程，触发器仅作轻量校验与状态合法性检查。

如何实现sql分级审批逻辑_利用触发器更新审批状态

触发器里不能直接提交事务（`COMMIT`）

MySQL 和 PostgreSQL 的触发器中，COMMIT 或 ROLLBACK 会直接报错，比如 MySQL 报 ERROR 1422: Explicit or implicit commit is not allowed in stored function or trigger。这是因为触发器属于当前事务上下文，强行提交会破坏事务原子性。

实操建议：

把审批状态更新逻辑放到应用层或存储过程中，触发器只做轻量校验（如检查 status 是否合法、是否跳过中间级）
如果必须用触发器驱动后续动作，改用 AFTER INSERT/UPDATE 触发器写入一个审批任务表，再由外部定时任务或监听程序处理
PostgreSQL 可用 dblink 异步调用远程事务，但复杂度高、运维成本大，不推荐用于核心审批流

`BEFORE UPDATE` 触发器修改 `NEW.status` 的边界情况

分级审批常依赖字段值变化来判断是否进入下一级，比如从 'pending' → 'approved_by_mgr' → 'approved_by_hr'。在 BEFORE UPDATE 中直接赋值 NEW.status 看似可行，但容易漏掉并发冲突和前置条件未满足的问题。

常见错误现象：

用户手动 UPDATE 表，绕过业务逻辑，触发器强行覆盖 NEW.status 导致状态错乱
同一审批单被多人同时操作，两个 BEFORE UPDATE 触发器都试图设为 'approved_by_hr'，结果只有一条生效，另一条被覆盖
没检查上一级是否已通过，就允许设置下一级状态（例如跳过部门经理直接到 HR）

实操建议：

在触发器内加显式校验：IF OLD.status = 'pending' AND NEW.status != 'approved_by_mgr' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Must approve by manager first'; END IF;
状态变更必须基于 OLD.status 和具体操作人角色字段（如 approver_role），不能只看新值
避免在触发器里做跨表查询判断（如查 users.role），性能差且易锁表；优先把角色信息冗余到主表或用应用层传入

PostgreSQL vs MySQL 触发器对 `RETURNING` 和 `NEW` 的处理差异

MySQL 的触发器无法返回值，而 PostgreSQL 支持 RETURNING 子句配合触发器函数返回结果，但很多人误以为能在触发器里直接用 RETURNING 更新下游状态——其实不能。

关键区别：

MySQL：只有 BEFORE/AFTER，无返回值机制；所有状态变更必须在触发器体内完成
PostgreSQL：BEFORE 触发器函数可返回 NULL（跳过原操作）或 NEW（修改后行），但不能执行 DML；真正要更新其他表，得靠 AFTER + PERFORM 调用函数
两者都不支持在触发器里发起新事务，所以无法在触发器中调用含 COMMIT 的函数

实操建议：

跨数据库迁移时，别直接照搬 MySQL 的 BEFORE UPDATE SET NEW.status = ... 写法到 PG；PG 需封装成 FUNCTION 并在 BEFORE 中 RETURN NEW
若需记录审批日志，MySQL 用 AFTER UPDATE INSERT INTO audit_log...；PG 同理，但注意避免在触发器中做大量 INSERT，可能拖慢主表写入