Oracle触发器报错：NISHAN.TBL_ADMIN正在变异，触发器/函数无法读取

你碰到的这个NISHAN.TBL_ADMIN is mutating, trigger/function may not see it错误，是Oracle里典型的变异表问题。原因很直接：你的行级触发器在触发时，又去查询了正在被修改的tbl_admin表——Oracle不允许行级触发器读取或修改触发它的表，因为此时表的数据处于中间状态，可能引发并发一致性问题。

问题根源 #

你的触发器代码里多此一举地去查询tbl_admin表获取最新插入的用户名：

SELECT username INTO new_username FROM (
    SELECT username FROM tbl_admin ORDER BY username DESC
) WHERE ROWNUM = 1;

其实在AFTER INSERT FOR EACH ROW触发器里，完全可以直接通过:NEW伪记录拿到刚插入行的所有字段值，根本不需要再去查原表。

修正后的触发器代码 #

直接用:NEW.username获取刚插入的用户名，去掉多余的查询逻辑：

CREATE OR REPLACE TRIGGER tr_admin_user_role
AFTER INSERT ON tbl_admin
FOR EACH ROW
BEGIN
    -- 直接使用:NEW伪记录获取刚插入的用户名
    INSERT INTO tbl_user_role(username, user_role)
    VALUES(:NEW.username, 'ROLE_ADMIN');
END;
/

额外注意事项 #

另外，我注意到tbl_user_role的主键user_role_id没有默认值或自增设置，执行插入时如果不指定这个值，会因为主键为空报错。可以根据Oracle版本处理：

• Oracle 12c及以上：把user_role_id改成自增列

  ALTER TABLE tbl_user_role MODIFY user_role_id GENERATED ALWAYS AS IDENTITY;
  

• 旧版本Oracle：创建序列生成主键值，再修改触发器

  -- 先创建序列
  CREATE SEQUENCE seq_user_role_id START WITH 1 INCREMENT BY 1;
  
  -- 修改触发器
  CREATE OR REPLACE TRIGGER tr_admin_user_role
  AFTER INSERT ON tbl_admin
  FOR EACH ROW
  BEGIN
      INSERT INTO tbl_user_role(user_role_id, username, user_role)
      VALUES(seq_user_role_id.NEXTVAL, :NEW.username, 'ROLE_ADMIN');
  END;
  /
  

这样修改后，再执行你的插入语句：

INSERT INTO tbl_admin(username, passwords) VALUES('nisha', 'nisha');

就不会再出现变异表错误，同时也能正确向tbl_user_role插入对应的管理员角色记录了。

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