SQL触发器中如何避免触发循环调用问题？

MySQL触发器禁止直接UPDATE本表，解析阶段即拦截并报ERROR 1442；BEFORE中SET NEW安全，AFTER中任何DML均被拦；跨库操作若形成反向触发仍报错；SQL Server需用CONTEXT_INFO防递归；触发器仅适用校验、时间戳、审计标记三类轻量场景。

MySQL触发器里UPDATE本表直接报ERROR 1442，不是逻辑问题而是语法拦截

MySQL在解析阶段就禁止触发器内对当前表执行任何DML操作，只要代码里出现UPDATE orders、INSERT INTO orders或DELETE FROM orders，不管WHERE条件多精确、改没改到同一行，都会立刻抛出ERROR 1442 (HY000): Can't update table 'orders' in stored function/trigger...。这不是性能或并发问题，是数据库强制的保护机制。

• BEFORE触发器中用SET NEW.col = ...完全安全，它不触发新事件，只修改即将插入/更新的行值
• AFTER触发器里哪怕只写UPDATE orders SET updated_at = NOW() WHERE id = NEW.id也会被拦
• 跨库操作（如UPDATE db2.t2）如果db2.t2上也有触发器反向写回当前库，照样触发ERROR 1442
• max_sp_recursion_depth只是调试开关，设成1只能让错误提前暴露，不能绕过限制

SQL Server触发器自更新导致无限递归，CONTEXT_INFO比@@NESTLEVEL更可靠

SQL Server允许触发器修改自身表，默认开启递归（RECURSIVE_TRIGGERS = ON），但@@NESTLEVEL无法区分“合法嵌套”和“自调用死循环”，且在视图触发等路径下行为不稳定。真正有效的方案是用会话级上下文标记。

• 业务代码执行前先设标记：SET CONTEXT_INFO 0x54524947474552（ASCII编码的”TRIGGER”）
• 触发器开头读取并判断：IF CONTEXT_INFO() = 0x54524947474552 RETURN
• 不要用临时表或全局变量存标记——临时表跨会话不可见，@@SPID在并行执行下可能重复
• DISABLE TRIGGER + ENABLE TRIGGER有并发风险：一个请求禁用后还没启用，另一个请求已进入

跨表触发时仅靠UPDATE()函数防不住循环，COLUMNS_UPDATED()才够准

UPDATE(col)只检测SET子句是否显式写了该列名，不关心值是否真变；它防不了跨表循环——比如t1触发器更新t2，而t2的触发器又更新t1，UPDATE()在t2触发器里照样返回TRUE。

• 用COLUMNS_UPDATED()配合位运算，能精准识别哪几列被修改，例如判断第1列（索引从0开始）：IF (COLUMNS_UPDATED() & POWER(2, 1)) = POWER(2, 1)
• 字段顺序影响位图位置，改表结构（如增删列）后必须同步调整位运算逻辑
• 避免在触发器里调用含DML的存储过程，即使过程本身不操作当前表，只要路径中可能间接触发，仍会被拦截
• 双表互相同步密码字段这类场景，禁用对方触发器（ALTER TABLE ... DISABLE TRIGGER）是常见做法，但需严格配对启用，否则残留禁用状态会导致数据不同步

真正该放触发器里的事其实很少

触发器不是业务逻辑的中转站。它只该做三类事情：强制校验（如金额不能为负）、维护强一致字段（updated_at）、写轻量审计标记（如is_modified_by_trigger = 1）。凡是涉及聚合计算、跨表关联更新、异步通知的逻辑，都该移出触发器。

• 想“每新增订单就更新客户总消费额”？用UPDATE customers SET total_spent = total_spent + i.amount FROM inserted i WHERE customers.id = i.customer_id一行集合操作搞定，别循环
• 想“首次下单才初始化客户记录”？用MERGE INTO customers USING inserted WHEN NOT MATCHED THEN INSERT，别先IF NOT EXISTS再INSERT
• 高并发下逐行触发器锁风险极高：1000行插入 → 1000次单行更新 → 每次持customers.id上的X锁 → 多事务更新同一客户直接排队