Oracle 11g实现自定义自增ID触发ORA-04091变异表错误如何解决

• 触发ORA-04091变异表错误的核心原因是：你在操作REGISTEREDINFO表的行级触发器中，调用的函数直接查询了正在处于变更状态的目标表，Oracle为了保证数据一致性，禁止行级触发器读取/修改正在触发它的表。
• 原有逻辑还有隐藏bug：函数中的SELECT语句没有加过滤条件，只要表中存在1条以上数据，就算没有变异表错误，也会触发TOO_MANY_ROWS报错，无法正常运行。
• 你需要拼接的Account_Type、FirstName都是当前待插入行的新值，完全不需要查询整张表，直接通过触发器的:new前缀取对应字段值即可。

方案1：直接把逻辑写入触发器（推荐，更简洁） #

原有序列不需要修改，直接替换原有触发器即可：

create or replace trigger reginfo_trig
before insert on registeredinfo
for each row
begin
  if (:new.account_number is null) then
    :new.account_number := SUBSTR(:new.Account_Type,1,2) 
                          || SUBSTR(:new.FirstName,1,3) 
                          || RPAD(to_char(Account_Number_seq.nextval),10,'0');
  end if;
end;
/

方案2：保留独立函数（需要改造为传参形式） #

如果你的业务需要复用这个ID生成逻辑，可以把函数改造成接收字段参数的形式，避免函数内部查询业务表：

-- 改写函数为接收参数的形式
create or replace function reginfo_func(p_account_type varchar2, p_firstname varchar2)
return varchar2
is 
begin
    return SUBSTR(p_account_type,1,2) || SUBSTR(p_firstname,1,3) || RPAD(to_char(Account_Number_seq.nextval),10,'0');
end;
/

-- 触发器调用时传入当前插入行的字段值
create or replace trigger reginfo_trig
before insert on registeredinfo
for each row
begin
if (:new.account_number is null) then
:new.account_number := reginfo_func(:new.Account_Type, :new.FirstName);
end if;
end;
/

• 可以根据业务需要对字段值加空值兼容处理，比如用NVL(SUBSTR(:new.Account_Type,1,2),'00')避免字段为空时生成的ID出现空值片段。
• 序列生成的序号不保证严格连续，事务回滚、实例重启都会导致序号跳变，如果要求ID完全连续，需要额外做序号管控逻辑。

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