【PL/SQL】トリガー完全ガイド｜6種類の使い分け・Compound Trigger・Mutating Table回避・INSTEAD OF・実務5パターン

CREATE OR REPLACE TRIGGER <トリガー名>
{BEFORE | AFTER | INSTEAD OF}
  {INSERT | UPDATE [OF 列名] | DELETE}
  [OR {INSERT | UPDATE | DELETE}] ...
ON <テーブル名|ビュー名>
[FOR EACH ROW]              -- 行トリガー（省略時はstatement）
[WHEN (<発火条件>)]       -- 条件式（NEW/OLDは : なし）
[DECLARE
  -- ローカル変数
]
BEGIN
  -- 実行したい処理
  -- :NEW.col で新値、:OLD.col で旧値にアクセス
[EXCEPTION
  -- 例外ハンドラ
]
END;
/

CREATE OR REPLACE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  :NEW.updated_at := SYSTIMESTAMP;
END;
/

-- これだけで UPDATE users ... のたびに updated_at が自動でSYSTIMESTAMPに

                  Row (FOR EACH ROW)       Statement (省略)
               ┌──────────────────────┬──────────────────────┐
BEFORE         │ ①:NEW書き換え        │ ③事前ログ/集計       │
               │  （updated_at等）     │  （実行前状態の記録）  │
               ├──────────────────────┼──────────────────────┤
AFTER          │ ②監査ログ/通知       │ ④事後集計/通知       │
               │  （:NEW/:OLDが確定後）│  （件数/完了ログ）    │
               └──────────────────────┴──────────────────────┘

CREATE OR REPLACE TRIGGER trg_orders
BEFORE INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
BEGIN
  -- 操作種別判定（Event Predicates）
  IF INSERTING THEN
    :NEW.created_at := SYSTIMESTAMP;
    :NEW.updated_at := SYSTIMESTAMP;
    :NEW.version    := 1;

  ELSIF UPDATING THEN
    :NEW.updated_at := SYSTIMESTAMP;
    :NEW.version    := NVL(:OLD.version, 0) + 1;
    -- 新旧値を比較してログ
    IF NVL(:OLD.status, ' ') != NVL(:NEW.status, ' ') THEN
      INSERT INTO audit_log VALUES (
        :OLD.id, 'STATUS_CHANGE',
        :OLD.status || ' → ' || :NEW.status,
        SYSTIMESTAMP
      );
    END IF;

  ELSIF DELETING THEN
    -- 削除前のデータをアーカイブに保存
    INSERT INTO orders_archive VALUES (
      :OLD.id, :OLD.user_id, :OLD.amount, :OLD.created_at, SYSTIMESTAMP
    );
  END IF;
END;

-- 給与が変わった時だけ発火
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary <> OLD.salary OR
      (NEW.salary IS NULL AND OLD.salary IS NOT NULL) OR
      (NEW.salary IS NOT NULL AND OLD.salary IS NULL))
BEGIN
  INSERT INTO salary_audit(emp_id, old_salary, new_salary, changed_at)
  VALUES (:NEW.id, :OLD.salary, :NEW.salary, SYSTIMESTAMP);
END;
/

-- 特定ステータスへの変更だけ通知
CREATE OR REPLACE TRIGGER trg_order_shipped
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (NEW.status = 'shipped' AND OLD.status != 'shipped')
BEGIN
  send_shipment_notification(:NEW.id);
END;

CREATE OR REPLACE TRIGGER trg_orders_compound
FOR INSERT OR UPDATE OR DELETE ON orders
COMPOUND TRIGGER

  -- ★状態変数：全フェーズで共有
  TYPE t_ids IS TABLE OF orders.id%TYPE INDEX BY PLS_INTEGER;
  v_changed_ids t_ids;
  v_change_count PLS_INTEGER := 0;

  BEFORE STATEMENT IS
  BEGIN
    -- DML開始前の初期化
    v_changed_ids.DELETE;
    v_change_count := 0;
    DBMS_OUTPUT.PUT_LINE('DML開始');
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    -- 各行のBEFORE処理
    IF INSERTING OR UPDATING THEN
      :NEW.updated_at := SYSTIMESTAMP;
    END IF;
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    -- 各行のAFTER処理（IDを溜めるだけ）
    v_change_count := v_change_count + 1;
    IF INSERTING OR UPDATING THEN
      v_changed_ids(v_change_count) := :NEW.id;
    ELSE
      v_changed_ids(v_change_count) := :OLD.id;
    END IF;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- DML完了後、蓄積したIDに対して処理（Mutating Tableを避ける鍵）
    FOR i IN 1..v_changed_ids.COUNT LOOP
      INSERT INTO audit_log(table_name, row_id, action, changed_at)
      VALUES ('orders', v_changed_ids(i), 'BULK_CHANGE', SYSTIMESTAMP);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_change_count || '件完了');
  END AFTER STATEMENT;

END trg_orders_compound;
/

CREATE OR REPLACE TRIGGER trg_check_max_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
  v_max_salary NUMBER;
BEGIN
  -- 同じemployeesテーブルをSELECT → ORA-04091
  SELECT MAX(salary) INTO v_max_salary FROM employees;

  IF :NEW.salary > v_max_salary * 1.5 THEN
    RAISE_APPLICATION_ERROR(-20001, '最高給与の1.5倍を超えています');
  END IF;
END;
/

-- INSERT実行時のエラー
-- ORA-04091: table SCHEMA.EMPLOYEES is mutating, trigger/function may not see it

CREATE OR REPLACE TRIGGER trg_check_salary_compound
FOR INSERT OR UPDATE ON employees
COMPOUND TRIGGER

  v_max_salary NUMBER;

  BEFORE STATEMENT IS
  BEGIN
    -- DML開始前（テーブルはまだmutating前）に集計
    SELECT MAX(salary) INTO v_max_salary FROM employees;
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    -- 各行で比較（SELECTしない）
    IF :NEW.salary > v_max_salary * 1.5 THEN
      RAISE_APPLICATION_ERROR(-20001, '最高給与の1.5倍を超えています');
    END IF;
  END BEFORE EACH ROW;

END;

CREATE OR REPLACE TRIGGER trg_log_with_autonomous
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_count NUMBER;
BEGIN
  -- 自律トランザクション内での別操作
  INSERT INTO audit_log(...) VALUES (...);
  COMMIT;
END;

-- パッケージで状態を保持
CREATE OR REPLACE PACKAGE pkg_emp_check IS
  TYPE t_ids IS TABLE OF employees.id%TYPE INDEX BY PLS_INTEGER;
  g_ids t_ids;
  g_count PLS_INTEGER;
END;
/

-- Row Triggerで対象行のIDだけ溜める
CREATE OR REPLACE TRIGGER trg_emp_row
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
  pkg_emp_check.g_count := pkg_emp_check.g_count + 1;
  pkg_emp_check.g_ids(pkg_emp_check.g_count) := :NEW.id;
END;
/

-- Statement TriggerでまとめてSELECT可能
CREATE OR REPLACE TRIGGER trg_emp_stmt
AFTER INSERT OR UPDATE ON employees
BEGIN
  -- ここではemployeesのSELECTが可能
  FOR i IN 1..pkg_emp_check.g_count LOOP
    -- 集計処理...
    NULL;
  END LOOP;
  pkg_emp_check.g_ids.DELETE;
  pkg_emp_check.g_count := 0;
END;

-- 複数テーブルを結合したビュー
CREATE OR REPLACE VIEW v_user_orders AS
SELECT u.id AS user_id, u.email,
       o.id AS order_id, o.amount, o.status
FROM users u JOIN orders o ON o.user_id = u.id;

-- このビューにINSERTするトリガー
CREATE OR REPLACE TRIGGER trg_user_orders_ins
INSTEAD OF INSERT ON v_user_orders
FOR EACH ROW
BEGIN
  -- users側をUPSERT
  BEGIN
    INSERT INTO users(id, email) VALUES (:NEW.user_id, :NEW.email);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      UPDATE users SET email = :NEW.email WHERE id = :NEW.user_id;
  END;

  -- orders側をINSERT
  INSERT INTO orders(id, user_id, amount, status)
  VALUES (:NEW.order_id, :NEW.user_id, :NEW.amount, :NEW.status);
END;
/

-- 使用側：ビュー経由で複数テーブル更新
INSERT INTO v_user_orders(user_id, email, order_id, amount, status)
VALUES (1, 'test@example.com', 100, 5000, 'pending');

CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER DDL ON SCHEMA    -- またはDATABASE
BEGIN
  INSERT INTO ddl_audit_log(
    event_time, username, event_type,
    object_type, object_name, sql_text
  ) VALUES (
    SYSTIMESTAMP,
    SYS_CONTEXT('USERENV', 'SESSION_USER'),
    ora_sysevent,
    ora_dict_obj_type,
    ora_dict_obj_name,
    SUBSTR(ora_sql_txt_to_str(), 1, 4000)
  );
END;
/

-- ログオン時の初期化
CREATE OR REPLACE TRIGGER trg_logon_audit
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO logon_audit(
    login_time, username, machine, program, ip_address
  ) VALUES (
    SYSTIMESTAMP,
    SYS_CONTEXT('USERENV', 'SESSION_USER'),
    SYS_CONTEXT('USERENV', 'HOST'),
    SYS_CONTEXT('USERENV', 'MODULE'),
    SYS_CONTEXT('USERENV', 'IP_ADDRESS')
  );
END;
/

-- 監査は自律トランザクションでCOMMIT推奨（ログイン失敗でもログ残す）

-- 監査ログテーブル
CREATE TABLE audit_log(
  id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name  VARCHAR2(30),
  action      VARCHAR2(10),
  row_id      NUMBER,
  old_data    CLOB,
  new_data    CLOB,
  changed_by  VARCHAR2(100),
  changed_at  TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- トリガー
CREATE OR REPLACE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
DECLARE
  v_action VARCHAR2(10);
  v_old    CLOB;
  v_new    CLOB;
  v_id     NUMBER;
BEGIN
  IF INSERTING THEN
    v_action := 'INSERT';
    v_id := :NEW.id;
    v_new := 'amount=' || :NEW.amount || ',status=' || :NEW.status;
  ELSIF UPDATING THEN
    v_action := 'UPDATE';
    v_id := :NEW.id;
    v_old := 'amount=' || :OLD.amount || ',status=' || :OLD.status;
    v_new := 'amount=' || :NEW.amount || ',status=' || :NEW.status;
  ELSE
    v_action := 'DELETE';
    v_id := :OLD.id;
    v_old := 'amount=' || :OLD.amount || ',status=' || :OLD.status;
  END IF;

  INSERT INTO audit_log(table_name, action, row_id, old_data, new_data, changed_by)
  VALUES ('ORDERS', v_action, v_id, v_old, v_new,
          SYS_CONTEXT('USERENV', 'SESSION_USER'));
END;
/

CREATE OR REPLACE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  :NEW.updated_at := SYSTIMESTAMP;
  -- 作成日時は不変（上書きされない保護）
  :NEW.created_at := :OLD.created_at;
END;

CREATE OR REPLACE TRIGGER trg_users_version
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  -- バージョン列を自動インクリメント
  :NEW.version := NVL(:OLD.version, 0) + 1;

  -- アプリ層から渡された期待バージョンと照合
  -- （アプリは UPDATE ... WHERE version = :expected で更新）
END;

CREATE OR REPLACE TRIGGER trg_orders_validate
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
  -- 金額マイナス禁止
  IF :NEW.amount < 0 THEN
    RAISE_APPLICATION_ERROR(-20101, '金額はマイナス不可: ' || :NEW.amount);
  END IF;

  -- 状態遷移ルール
  IF UPDATING THEN
    IF :OLD.status = 'closed' AND :NEW.status != 'closed' THEN
      RAISE_APPLICATION_ERROR(-20102, 'closed状態から復帰できません');
    END IF;

    IF :OLD.status = 'shipped' AND :NEW.status = 'pending' THEN
      RAISE_APPLICATION_ERROR(-20103, 'shipped→pendingは不可');
    END IF;
  END IF;
END;

-- ordersの合計金額をusers.total_spentに反映
CREATE OR REPLACE TRIGGER trg_update_user_total
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
BEGIN
  IF INSERTING OR UPDATING THEN
    UPDATE users
    SET total_spent = total_spent + :NEW.amount - NVL(:OLD.amount, 0)
    WHERE id = :NEW.user_id;
  ELSE
    UPDATE users
    SET total_spent = total_spent - :OLD.amount
    WHERE id = :OLD.user_id;
  END IF;
END;

-- 一時無効化（大量データ投入時など）
ALTER TRIGGER trg_orders_audit DISABLE;

-- 一括無効化（テーブル単位）
ALTER TABLE orders DISABLE ALL TRIGGERS;

-- 再有効化
ALTER TRIGGER trg_orders_audit ENABLE;
ALTER TABLE orders ENABLE ALL TRIGGERS;

-- 削除
DROP TRIGGER trg_orders_audit;

-- 既存トリガー一覧（現ユーザー）
SELECT trigger_name, trigger_type, triggering_event,
       table_name, status
FROM user_triggers
WHERE table_name = 'ORDERS';

-- ソース確認
SELECT text FROM user_source
WHERE name = 'TRG_ORDERS_AUDIT' AND type = 'TRIGGER'
ORDER BY line;