Oracle触发器ORA-04091报错：需实现员工类型变更时更新ID

错误原因 #

ORA-04091错误是因为你在AFTER行级触发器中尝试更新触发它的USERS表。当行级触发器执行时，原表处于变异状态（正在被修改），Oracle不允许触发器在此状态下读取或修改原表，这是为了避免并发修改导致的数据不一致。

修正后的触发器代码 #

create or replace TRIGGER "TRIGGER_UPDATE_USERS_ID" 
BEFORE UPDATE OF empl_type
ON USERS
FOR EACH ROW
BEGIN
    -- 当empl_type在S和H之间切换时更新ID
    IF (:OLD.empl_type = 'S' AND :NEW.empl_type = 'H') THEN
        :NEW.id := 'H' || :OLD.clock;
        DBMS_OUTPUT.PUT_LINE('已更新ID前缀为H，新类型：' || :NEW.empl_type);
    ELSIF (:OLD.empl_type = 'H' AND :NEW.empl_type = 'S') THEN
        :NEW.id := 'S' || :OLD.clock;
        DBMS_OUTPUT.PUT_LINE('已更新ID前缀为S，新类型：' || :NEW.empl_type);
    END IF;
END;

关键修改点 #

1. 触发器时机改为BEFORE：BEFORE行级触发器可以直接修改:NEW对象的字段值，无需执行UPDATE语句，从根源避免表变异问题。
2. 移除不必要的变量：直接使用:OLD和:NEW内置对象访问字段，简化代码结构。
3. 替换硬编码的ID值：原代码中固定的S222222、H274952改为动态使用:OLD.clock生成新ID，适配所有符合条件的记录。
4. 修正输出提示：调整DBMS_OUTPUT的文本，使其与实际操作逻辑匹配。

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