触发器触发表变异错误：需实现岗位薪资上限控制触发器及函数

首先得说清楚你遇到的「table is mutating」错误根源：行级触发器（FOR EACH ROW）执行期间，触发表（这里是STAFF）处于锁定/变异状态，此时任何对该表的查询或修改都会触发这个错误——而你调用的packet1.AVG_SALARY函数内部正好查询了STAFF表，这就踩坑了。

下面是针对你的需求的完美解决方案，用Oracle的复合触发器来避开表变异问题，同时实现薪资自动调整逻辑：

解决方案代码 #

CREATE OR REPLACE TRIGGER TRG_STAFF_SALARY_LIMIT
FOR INSERT OR UPDATE OF SALARY, ID_POSITION ON STAFF
COMPOUND TRIGGER

    -- 定义关联数组，用来提前存储所有岗位的平均薪资
    TYPE t_position_avg_sal IS TABLE OF NUMBER INDEX BY POSITIONS.ID_POSITION%TYPE;
    v_pos_avg_sal t_position_avg_sal;

BEFORE STATEMENT IS
BEGIN
    -- 语句执行前，批量计算所有岗位的平均薪资（等价于你的AVG_SALARY函数逻辑）
    -- 这个阶段STAFF表处于稳定状态，不会触发变异错误
    SELECT P.ID_POSITION, AVG(S.SALARY)
    BULK COLLECT INTO v_pos_avg_sal
    FROM POSITIONS P
    LEFT JOIN STAFF S ON P.ID_POSITION = S.ID_POSITION
    GROUP BY P.ID_POSITION;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
    v_avg_salary NUMBER;
    v_max_allowed_salary NUMBER;
BEGIN
    -- 从提前计算好的数组里取当前岗位的平均薪资，无需再调用查询STAFF的函数
    v_avg_salary := v_pos_avg_sal(:NEW.ID_POSITION);

    -- 处理「新岗位无员工」的边界情况：可按需调整逻辑
    -- 示例1：允许新岗位设置任意薪资（直接跳过检查）
    -- IF v_avg_salary IS NULL THEN
    --     RETURN;
    -- END IF;
    -- 示例2：默认平均薪资为0（仅作演示，不建议实际使用）
    IF v_avg_salary IS NULL THEN
        v_avg_salary := 0;
    END IF;

    -- 计算允许的最高薪资：平均薪资×130%
    v_max_allowed_salary := v_avg_salary * 1.3;

    -- 若输入薪资超过上限，自动调整为上限值
    IF :NEW.SALARY > v_max_allowed_salary THEN
        :NEW.SALARY := v_max_allowed_salary;
    END IF;
END BEFORE EACH ROW;

END TRG_STAFF_SALARY_LIMIT;
/

为什么这个方案能解决问题？ #

1. 彻底避开表变异：把查询STAFF表计算平均薪资的操作放到了语句级的BEFORE阶段——这个阶段还未开始行级的插入/更新，STAFF表处于稳定状态，完全可以安全查询。
2. 高效复用数据：用关联数组提前存好所有岗位的平均薪资，行级触发器直接从数组取值，不用再调用会触发问题的函数，彻底避免了访问变异表。
3. 覆盖全场景：触发器监听SALARY和ID_POSITION字段的变化，员工更换岗位时也会自动检查新岗位的薪资上限。

额外说明 #

如果你必须保留packet1.AVG_SALARY函数的调用，也可以把函数调用放到BEFORE STATEMENT阶段（比如循环遍历所有岗位调用函数），但直接用批量查询的效率会更高，逻辑也更清晰。

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