如何编写SQL触发器基于其他行条件更新表字段值？

我有一张存储双人对局后玩家段位信息的表，初始结构如下：

现在要给表新增第四列opponent_rank（对手段位），要求每次插入新对局数据时，根据对阵关系自动填充该列值。
比如新对局是A对阵C，最终表数据预期如下：

我写了如下触发器尝试实现需求，但触发器不仅不生效，还导致表无法插入新记录：

CREATE TRIGGER opp_rank_update 
BEFORE INSERT INTO stats
FOR EACH ROW

UPDATE rank
SET opponent_rank = SELECT (SELECT previous_rank FROM rank WHERE player = new.winner)
WHERE player = new.loser

触发器逻辑依赖另一张存储对局原始数据的表，结构如下：

需要修正触发器逻辑，实现opponent_rank自动更新，解决插入阻塞问题。

原触发器存在三个直接导致失效的问题：

• 绑定对象错误：触发器建在stats表上，但实际需要响应的是对局记录表的插入动作，触发时机和操作对象完全错配
• 语法错误：给opponent_rank赋值的子查询没有加必要的括号，嵌套查询冗余，SQL执行直接报错
• 逻辑缺失：只写了负者的对手段位更新逻辑，没有同步更新胜者的对应字段，且BEFORE INSERT触发时新数据还未持久化，很容易引发事务锁或者查询空值问题

1. 先给段位表新增字段，设置默认值 #

ALTER TABLE rank 
ADD COLUMN opponent_rank VARCHAR(32) 
DEFAULT 'irrelevant（无对应对局）';

未参与新对局的玩家会自动保留默认值，不需要额外处理。

2. 删除原有错误触发器 #

DROP TRIGGER IF EXISTS opp_rank_update;

3. 创建正确的触发器（以MySQL语法为例） #

将触发器绑定到对局原始记录表上，每次插入新对局完成后，同步更新对阵双方的对手段位：

DELIMITER //
CREATE TRIGGER opp_rank_update
AFTER INSERT ON matches -- 如果你的对局表名是stats就改成stats
FOR EACH ROW
BEGIN
    -- 更新胜者的对手段位：取负者对局前的历史段位
    UPDATE rank 
    SET opponent_rank = (SELECT previous_rank FROM rank WHERE player = NEW.loser)
    WHERE player = NEW.winner;

    -- 更新负者的对手段位：取胜者对局前的历史段位
    UPDATE rank 
    SET opponent_rank = (SELECT previous_rank FROM rank WHERE player = NEW.winner)
    WHERE player = NEW.loser;
END //
DELIMITER ;

• 用AFTER INSERT作为触发时机：等新对局记录写入完成后再执行更新，避免行锁冲突和空值查询问题
• 触发器绑定对局表而非段位表：只有新增对局时才需要更新对手段位，和段位表自身的增改操作解耦，不会阻塞段位表的正常写入
• 双方的对手段位都取对方对局前的previous_rank值，和示例要求的取值逻辑完全一致

内容的提问来源于stack exchange，提问作者raphle