【Oracle】コンパウンドトリガー完全ガイド｜ORA-04091 ミュータルテーブルの解決・一括 DML の効率化まで解説

CREATE OR REPLACE TRIGGER trg_compound_example
    FOR INSERT OR UPDATE OR DELETE ON target_table
COMPOUND TRIGGER    -- COMPOUND TRIGGER キーワード

    -- ===== 共有変数の宣言セクション =====
    -- ここで宣言した変数はすべてのタイミングポイントで共有される
    -- DML ステートメントの開始時に初期化される
    TYPE t_id_list IS TABLE OF target_table.id%TYPE INDEX BY PLS_INTEGER;
    g_ids   t_id_list;    -- 変更された行のIDを収集するコレクション
    g_count PLS_INTEGER := 0;

    -- ===== タイミングポイント =====

    BEFORE STATEMENT IS
    BEGIN
        -- DML ステートメント実行前に1回だけ実行される
        -- 共有変数の初期化に使う
        g_ids.DELETE;
        g_count := 0;
    END BEFORE STATEMENT;

    BEFORE EACH ROW IS
    BEGIN
        -- 各行の変更前に実行される（通常のBEFORE ROWトリガーと同様）
        -- :NEW および :OLD にアクセスできる
        NULL;  -- ここでは使わない
    END BEFORE EACH ROW;

    AFTER EACH ROW IS
    BEGIN
        -- 各行の変更後に実行される（通常のAFTER ROWトリガーと同様）
        -- 変更された行の情報を収集する
        g_count := g_count + 1;
        g_ids(g_count) := :NEW.id;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        -- DML ステートメント完了後に1回だけ実行される
        -- 収集したデータを一括処理する
        FOR i IN 1..g_count LOOP
            DBMS_OUTPUT.PUT_LINE('変更された ID: ' || g_ids(i));
        END LOOP;
    END AFTER STATEMENT;

END trg_compound_example;
/

-- 例: employees テーブルの salary を更新した後、
--     同じ department の平均給与を department_stats テーブルに反映する

-- 通常のトリガーでは以下がORA-04091を引き起こす:
-- AFTER UPDATE OF salary ON employees FOR EACH ROW
-- BEGIN
--     SELECT AVG(salary) INTO v_avg FROM employees  -- ← employees が変更中なのでエラー
--     WHERE department_id = :NEW.department_id;
-- END;

-- コンパウンドトリガーで解決する
CREATE OR REPLACE TRIGGER trg_dept_salary_stats
    FOR INSERT OR UPDATE OF salary OR DELETE ON employees
COMPOUND TRIGGER

    -- 変更があった department_id を収集するコレクション
    TYPE t_dept_ids IS TABLE OF employees.department_id%TYPE INDEX BY PLS_INTEGER;
    g_dept_ids  t_dept_ids;
    g_count     PLS_INTEGER := 0;

    BEFORE STATEMENT IS
    BEGIN
        g_dept_ids.DELETE;
        g_count := 0;
    END BEFORE STATEMENT;

    AFTER EACH ROW IS
        v_dept_id employees.department_id%TYPE;
    BEGIN
        -- 変更があった department_id を収集する（重複あり、後でまとめてクエリ）
        v_dept_id := COALESCE(:NEW.department_id, :OLD.department_id);
        IF v_dept_id IS NOT NULL THEN
            g_count := g_count + 1;
            g_dept_ids(g_count) := v_dept_id;
        END IF;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
        v_avg_salary NUMBER;
        v_emp_count  NUMBER;
    BEGIN
        -- ステートメント完了後に employees テーブルへのクエリが可能（ミュータル状態が解除）
        FOR i IN 1..g_count LOOP
            -- 重複を避けるため EXISTS チェックや DISTINCT 処理が必要な場合がある
            SELECT AVG(salary), COUNT(*)
            INTO v_avg_salary, v_emp_count
            FROM employees
            WHERE department_id = g_dept_ids(i);

            -- 集計結果を別テーブルに格納する
            MERGE INTO department_stats ds
            USING DUAL ON (ds.department_id = g_dept_ids(i))
            WHEN MATCHED THEN
                UPDATE SET avg_salary = v_avg_salary,
                           emp_count  = v_emp_count,
                           updated_at = SYSTIMESTAMP
            WHEN NOT MATCHED THEN
                INSERT(department_id, avg_salary, emp_count, updated_at)
                VALUES(g_dept_ids(i), v_avg_salary, v_emp_count, SYSTIMESTAMP);
        END LOOP;
    END AFTER STATEMENT;

END trg_dept_salary_stats;
/

CREATE OR REPLACE TRIGGER trg_orders_audit_bulk
    FOR INSERT ON orders
COMPOUND TRIGGER

    -- 監査ログデータを一時保存するコレクション
    TYPE t_order_ids  IS TABLE OF orders.order_id%TYPE  INDEX BY PLS_INTEGER;
    TYPE t_cust_ids   IS TABLE OF orders.customer_id%TYPE INDEX BY PLS_INTEGER;
    TYPE t_amounts    IS TABLE OF orders.total_amount%TYPE INDEX BY PLS_INTEGER;

    g_order_ids  t_order_ids;
    g_cust_ids   t_cust_ids;
    g_amounts    t_amounts;
    g_count      PLS_INTEGER := 0;

    BEFORE STATEMENT IS
    BEGIN
        g_order_ids.DELETE;
        g_cust_ids.DELETE;
        g_amounts.DELETE;
        g_count := 0;
    END BEFORE STATEMENT;

    AFTER EACH ROW IS
    BEGIN
        -- 各行のデータをコレクションに追加する
        g_count := g_count + 1;
        g_order_ids(g_count) := :NEW.order_id;
        g_cust_ids(g_count)  := :NEW.customer_id;
        g_amounts(g_count)   := :NEW.total_amount;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        -- FORALL で一括 INSERT する（行レベルより大幅に高速）
        FORALL i IN 1..g_count
            INSERT INTO order_audit_log(order_id, customer_id, amount, inserted_at)
            VALUES(g_order_ids(i), g_cust_ids(i), g_amounts(i), SYSTIMESTAMP);
    END AFTER STATEMENT;

END trg_orders_audit_bulk;
/