如何实现SQL用户行为追踪_通过触发器记录操作明细-SQL-PHP中文网

记录用户行为必须用after触发器，因其能获取最终写入值；before无法获取update后的新值，且after中禁止修改new/old等伪记录，否则引发递归写入错误。

如何实现sql用户行为追踪_通过触发器记录操作明细

触发器里用 `AFTER INSERT/UPDATE/DELETE` 还是 `BEFORE`

记录用户行为必须用 AFTER 触发器，BEFORE 会拿不到最终写入的值，尤其在 UPDATE 场景下——比如你改了 user_status 字段，BEFORE 里看到的还是旧值。AFTER 才能确保日志里存的是真实落地的数据。但注意：AFTER 不允许修改 NEW 或 OLD（MySQL）或 INSERTED/DELETED（SQL Server）里的字段，否则报错。

常见错误现象：ERROR 1442 (HY000): Can't update table 'xxx' in stored function/trigger，本质是试图在 AFTER 触发器里再更新原表，形成递归写入。

只对需要审计的表建 AFTER 触发器，别全库铺开
避免在触发器里调用存储过程做复杂逻辑，容易锁表或超时
PostgreSQL 用 FOR EACH ROW EXECUTE FUNCTION，语法位置和 MySQL 不同，别直接套用

`USER()` 和 `CURRENT_USER()` 哪个能反映真实操作人

CURRENT_USER() 返回连接认证时的账号（比如 'app@10.0.1.%'），而 USER() 返回客户端声明的用户名+IP（比如 'admin@192.168.2.5'）。如果你的应用用统一数据库账号连库，那这两个都只能看到中间件账号，根本不是终端用户。这时候必须在 SQL 里显式传参，比如插入前加 SET @trace_user = 'zhangsan'，再在触发器里读 @trace_user。

使用场景：后台系统用连接池，所有请求都走 webapp@localhost，不传参就永远记不到谁点了删除按钮。

MySQL 5.7+ 支持 INFORMATION_SCHEMA.PROCESSLIST 查当前连接的 USER，但触发器里不能查这个视图
SQL Server 可用 ORIGINAL_LOGIN() 拿最初登录名，比 SUSER_NAME() 更可靠
别依赖 HOSTNAME() 或 @@SPID，它们跟操作人完全无关

日志表设计绕不开的三个字段：`operation_type`、`old_data`、`new_data`

光记“谁什么时候改了哪张表”没用，得知道改了什么。建议日志表固定带 operation_type ENUM('INSERT','UPDATE','DELETE')，再配两个 JSON 字段存变更前后快照。MySQL 5.7+、PostgreSQL、SQL Server 2016+ 都支持 JSON 类型，比拼接字符串或用 TEXT 解析安全得多。

性能影响：每次 UPDATE 都要序列化整行数据进 JSON，大宽表（>50列）会明显拖慢主业务写入。可折中只存变化列：JSON_OBJECT('name', NEW.name, 'email', NEW.email)，但得在触发器里手动判断字段是否真变了（IF NEW.email != OLD.email THEN ...）。

别把日志表和业务表放同一库，IO 竞争会导致主业务超时
old_data 在 INSERT 时为 NULL，new_data 在 DELETE 时为 NULL，代码里要兼容
PostgreSQL 的 ROW_TO_JSON(OLD) 比手拼快，但会包含系统字段（如 ctid），需用 TO_JSONB(OLD.*) - 'ctid' 过滤

触发器失效的隐蔽原因：`autocommit=0` 和 `TRIGGER` 权限缺失

开发环境测试正常，上线后日志全丢？先查 SELECT @@autocommit。如果应用用了事务且没显式 COMMIT，触发器写的日志会随事务一起回滚——日志表也得在同一个事务里，不能设成 AUTOCOMMIT=1 单独提交。另一个高频问题是权限：MySQL 要给触发器定义者（DEFINER）授予 INSERT 到日志表的权限，不是只给调用者。

错误信息示例：ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled，这是 MySQL 开了 binlog 但 DEFINER 权限不够，得加 log_bin_trust_function_creators=1 或换高权限账号创建。