【Oracle】ORA-04091: 表は変化しています（ミューテーティングテーブル）の原因と解決方法完全ガイド

CREATE TABLE employees (
    employee_id   NUMBER PRIMARY KEY,
    name          VARCHAR2(100),
    salary        NUMBER,
    department_id NUMBER
);

-- 行トリガー：給与が部門平均を超えていないかチェックする
CREATE OR REPLACE TRIGGER trg_check_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    v_avg_salary NUMBER;
BEGIN
    -- ★ ここで自テーブル employees を SELECT している → ORA-04091
    SELECT AVG(salary) INTO v_avg_salary
    FROM employees
    WHERE department_id = :NEW.department_id;

    IF :NEW.salary > v_avg_salary * 3 THEN
        RAISE_APPLICATION_ERROR(-20001,
            '給与が部門平均の3倍を超えています');
    END IF;
END;
/

-- テスト
UPDATE employees SET salary = 9999999 WHERE employee_id = 1;
-- → ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it

CREATE OR REPLACE TRIGGER trg_check_salary
FOR INSERT OR UPDATE OF salary ON employees
COMPOUND TRIGGER

    -- ★ トリガー内で共有する変数（パッケージ変数の代わり）
    TYPE t_dept_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    g_dept_ids t_dept_ids;
    g_idx      PLS_INTEGER := 0;

    -- ========== 行レベル: 変更された部門IDを記録する ==========
    AFTER EACH ROW IS
    BEGIN
        g_idx := g_idx + 1;
        g_dept_ids(g_idx) := :NEW.department_id;
    END AFTER EACH ROW;

    -- ========== 文レベル: DML完了後にまとめてチェックする ==========
    AFTER STATEMENT IS
        v_avg_salary NUMBER;
        v_max_salary NUMBER;
    BEGIN
        FOR i IN 1..g_idx LOOP
            -- ★ ここでは DML が完了しているので自テーブルを安全に SELECT できる
            SELECT AVG(salary), MAX(salary)
            INTO v_avg_salary, v_max_salary
            FROM employees
            WHERE department_id = g_dept_ids(i);

            IF v_max_salary > v_avg_salary * 3 THEN
                RAISE_APPLICATION_ERROR(-20001,
                    '部門' || g_dept_ids(i) || 'に給与が平均の3倍を超える社員がいます');
            END IF;
        END LOOP;
    END AFTER STATEMENT;

END trg_check_salary;
/

-- ステップ 1: データ一時保存用のパッケージを作成
CREATE OR REPLACE PACKAGE pkg_emp_check AS
    TYPE t_dept_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    g_dept_ids t_dept_ids;
    g_idx      PLS_INTEGER := 0;
    PROCEDURE reset;
END pkg_emp_check;
/

CREATE OR REPLACE PACKAGE BODY pkg_emp_check AS
    PROCEDURE reset IS
    BEGIN
        g_dept_ids.DELETE;
        g_idx := 0;
    END reset;
END pkg_emp_check;
/

-- ステップ 2: 行トリガーで変更された部門IDを記録
CREATE OR REPLACE TRIGGER trg_emp_row
AFTER INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    pkg_emp_check.g_idx := pkg_emp_check.g_idx + 1;
    pkg_emp_check.g_dept_ids(pkg_emp_check.g_idx) := :NEW.department_id;
END;
/

-- ステップ 3: 文トリガーでまとめてチェック
CREATE OR REPLACE TRIGGER trg_emp_stmt
AFTER INSERT OR UPDATE OF salary ON employees
DECLARE
    v_avg_salary NUMBER;
    v_max_salary NUMBER;
BEGIN
    FOR i IN 1..pkg_emp_check.g_idx LOOP
        SELECT AVG(salary), MAX(salary)
        INTO v_avg_salary, v_max_salary
        FROM employees
        WHERE department_id = pkg_emp_check.g_dept_ids(i);

        IF v_max_salary > v_avg_salary * 3 THEN
            RAISE_APPLICATION_ERROR(-20001,
                '部門' || pkg_emp_check.g_dept_ids(i) || 'に給与が平均の3倍を超える社員がいます');
        END IF;
    END LOOP;
    pkg_emp_check.reset;  -- 次回の DML に備えてリセット
END;
/

-- DML完了後にテーブル全体をチェックする文レベルトリガー
CREATE OR REPLACE TRIGGER trg_check_salary_stmt
AFTER INSERT OR UPDATE OF salary ON employees
-- ★ FOR EACH ROW を書かない → 文レベルトリガーになる
DECLARE
    v_violation_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_violation_count
    FROM (
        SELECT department_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_sal
        FROM employees
    )
    WHERE salary > avg_sal * 3;

    IF v_violation_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20001,
            '給与が部門平均の3倍を超える社員が' || v_violation_count || '名います');
    END IF;
END;
/