Oracle嵌套表插入触发器报错求助：组件数量与类型校验问题

问题1：ORA-04091 表变异错误 #

错误原因 #

你编写的是AFTER INSERT行级触发器，在触发器内部直接查询正在修改的MODEL表，触发了Oracle的**表变异（mutating table）**限制。行级触发器执行期间，主表处于未提交的中间状态，Oracle禁止触发器读取该表，避免数据不一致。

修复方案 #

不需要查询主表，直接通过:new.Component.COUNT获取新插入行的组件数量，同时将触发器改为BEFORE INSERT类型，在数据插入前完成校验，避免访问主表。

正确触发器代码：

CREATE OR REPLACE TRIGGER number_of_components
BEFORE INSERT ON Model
FOR EACH ROW
BEGIN
  IF :new.Component.COUNT < 3 THEN
    RAISE_APPLICATION_ERROR(-2061, '组件数量不足，至少需要3个');
  END IF;
END;
/

问题2：PLS-00306 DEREF调用参数错误 #

错误原因 #

1. Component是component_ty_nt类型的嵌套表，存储的是Component_ty对象实例，不是REF（引用）类型，因此不需要使用DEREF函数。
2. 变量engineCounter和bodyCounter未初始化，初始值为NULL，执行engineCounter := engineCounter + 1会导致运算错误。

修复方案 #

1. 移除DEREF函数，直接访问嵌套表元素的属性：:new.Component(i).Component_Type
2. 初始化计数器变量为0

正确触发器代码：

CREATE OR REPLACE TRIGGER check_engine_and_body
BEFORE INSERT ON model
FOR EACH ROW
DECLARE
  engineCounter NUMBER := 0;
  bodyCounter NUMBER := 0;
BEGIN
  FOR i IN 1..:new.Component.COUNT LOOP
    -- 注意匹配需求中的大小写：原需求取值为'body'、'engine'，需与实际插入值一致
    IF :new.Component(i).Component_Type = 'engine' THEN
      engineCounter := engineCounter + 1;
    ELSIF :new.Component(i).Component_Type = 'body' THEN
      bodyCounter := bodyCounter + 1;
    END IF;
  END LOOP;

  IF engineCounter != 1 OR bodyCounter != 1 THEN
    RAISE_APPLICATION_ERROR(-20000, '约束违反：必须包含且仅包含1个engine类型组件和1个body类型组件');
  END IF;
END;
/

可选优化：合并触发器 #

可以将两个校验逻辑合并到同一个BEFORE INSERT触发器中，减少触发器数量，提升效率：

CREATE OR REPLACE TRIGGER validate_model_components
BEFORE INSERT ON Model
FOR EACH ROW
DECLARE
  engineCounter NUMBER := 0;
  bodyCounter NUMBER := 0;
BEGIN
  -- 校验组件数量至少3个
  IF :new.Component.COUNT < 3 THEN
    RAISE_APPLICATION_ERROR(-2061, '组件数量不足，至少需要3个');
  END IF;

  -- 校验engine和body组件数量
  FOR i IN 1..:new.Component.COUNT LOOP
    IF :new.Component(i).Component_Type = 'engine' THEN
      engineCounter := engineCounter + 1;
    ELSIF :new.Component(i).Component_Type = 'body' THEN
      bodyCounter := bodyCounter + 1;
    END IF;
  END LOOP;

  IF engineCounter != 1 OR bodyCounter != 1 THEN
    RAISE_APPLICATION_ERROR(-20000, '约束违反：必须包含且仅包含1个engine类型组件和1个body类型组件');
  END IF;
END;
/

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