【Oracle】トリガー情報を取得するSQL文｜USER/ALL/DBA_TRIGGERS・作成・有効化・実務パターンまで完全解説

この記事でわかること

• USER_TRIGGERS / ALL_TRIGGERS / DBA_TRIGGERS の違いと使い分け
• トリガーの一覧取得・詳細確認のSQL
• USER_SOURCE によるソースコード取得
• トリガーの有効/無効の確認と ALTER TRIGGER ENABLE/DISABLE
• CREATE TRIGGER の基本構文（BEFORE/AFTER、行レベル/文レベル）
• :NEW / :OLD 疑似レコードの使い方
• INSTEAD OF トリガー（ビュー用）の仕組み
• DROP TRIGGER による削除方法
• 他RDBMS比較（MySQL、PostgreSQL、SQL Server）
• よくあるエラーと対処法（ORA-04091 等）
• 実務パターン（監査ログ、自動採番、論理削除フラグ）

-- ユーザーがINSERTを実行
INSERT INTO employees (emp_id, emp_name) VALUES (100, '山田太郎');

-- ↓ Oracleが自動的にトリガーを起動
-- ↓ 例：監査テーブルに操作ログを自動記録
-- ↓ 例：自動採番カラムに値を自動セット

ポイント

この3ビューの命名規則は、USER_TABLES / ALL_TABLES / DBA_TABLES と同じパターンです。Oracle のデータディクショナリビューは USER_ → ALL_ → DBA_ の順で参照範囲が広がります。

-- 自分が所有するトリガーの一覧を取得
SELECT
    trigger_name,       -- トリガー名
    trigger_type,       -- BEFORE EACH ROW / AFTER STATEMENT 等
    triggering_event,   -- INSERT / UPDATE / DELETE
    table_name,         -- 対象テーブル名
    status              -- ENABLED / DISABLED
FROM
    user_triggers
ORDER BY
    table_name, trigger_name;

実行結果（例）

TRIGGER_NAME          TRIGGER_TYPE         TRIGGERING_EVENT  TABLE_NAME   STATUS
--------------------- -------------------- ----------------- ------------ --------
TRG_EMP_AUDIT         AFTER EACH ROW       INSERT OR UPDATE  EMPLOYEES    ENABLED
TRG_EMP_SEQ           BEFORE EACH ROW      INSERT            EMPLOYEES    ENABLED
TRG_ORDER_CHECK       BEFORE EACH ROW      INSERT            ORDERS       DISABLED

-- アクセス可能な全トリガー（他スキーマ含む）
SELECT
    owner,              -- 所有者
    trigger_name,
    trigger_type,
    triggering_event,
    table_owner,        -- 対象テーブルの所有者
    table_name,
    status
FROM
    all_triggers
WHERE
    owner = 'HR'      -- スキーマ名で絞り込み
ORDER BY
    owner, table_name, trigger_name;

-- データベース上のすべてのトリガー（DBA権限が必要）
SELECT
    owner,
    trigger_name,
    trigger_type,
    triggering_event,
    table_name,
    status
FROM
    dba_triggers
WHERE
    owner NOT IN ('SYS', 'SYSTEM')  -- システムトリガーを除外
ORDER BY
    owner, table_name, trigger_name;

注意：DBA_TRIGGERS には SELECT ANY DICTIONARY または SELECT_CATALOG_ROLE が必要です。権限がないと ORA-00942: table or view does not exist が発生します。

-- 特定テーブルのトリガー一覧（詳細情報付き）
SELECT
    trigger_name,
    trigger_type,
    triggering_event,
    status,
    description        -- トリガーの宣言部（CREATE TRIGGER ...）
FROM
    user_triggers
WHERE
    table_name = 'EMPLOYEES'
ORDER BY
    trigger_name;

注意：テーブル名はデータディクショナリ内では大文字で格納されています。WHERE table_name = 'employees' ではヒットしないため、'EMPLOYEES' と大文字で指定するか UPPER() を使ってください。

-- トリガーのソースコードを行番号順に取得
SELECT
    line,       -- 行番号
    text        -- ソースコード
FROM
    user_source
WHERE
    name = 'TRG_EMP_AUDIT'
    AND type = 'TRIGGER'
ORDER BY
    line;

実行結果（例）

LINE  TEXT
----- --------------------------------------------------
    1 TRG_EMP_AUDIT
    2 AFTER INSERT OR UPDATE ON employees
    3 FOR EACH ROW
    4 BEGIN
    5   INSERT INTO emp_audit_log (
    6     action_date, action_type, emp_id, old_name, new_name
    7   ) VALUES (
    8     SYSDATE, 'UPDATE', :NEW.emp_id, :OLD.emp_name, :NEW.emp_name
    9   );
   10 END;

-- DDL全体を取得（CREATE TRIGGER 文を再構成）
SELECT
    DBMS_METADATA.GET_DDL('TRIGGER', 'TRG_EMP_AUDIT') AS ddl_text
FROM
    dual;

-- 無効化されているトリガーを一覧表示
SELECT
    trigger_name,
    table_name,
    status
FROM
    user_triggers
WHERE
    status = 'DISABLED'
ORDER BY
    table_name;

-- 個別のトリガーを無効化
ALTER TRIGGER trg_emp_audit DISABLE;

-- 個別のトリガーを有効化
ALTER TRIGGER trg_emp_audit ENABLE;

-- テーブルの全トリガーを一括無効化
ALTER TABLE employees DISABLE ALL TRIGGERS;

-- テーブルの全トリガーを一括有効化
ALTER TABLE employees ENABLE ALL TRIGGERS;

注意：トリガーを無効化したまま戻し忘れると、監査ログが記録されない・整合性チェックが効かないなどの問題が起きます。必ず作業後に再有効化してください。

CREATE [OR REPLACE] TRIGGER トリガー名
    {BEFORE | AFTER}                -- 実行タイミング
    {INSERT | UPDATE [OF 列名] | DELETE}  -- 対象イベント
    [OR {INSERT | UPDATE | DELETE}]  -- 複数イベント指定可
    ON テーブル名
    [FOR EACH ROW]              -- 行レベルトリガー
    [WHEN (条件)]               -- 起動条件（オプション）
[DECLARE]
    -- 変数宣言（オプション）
BEGIN
    -- PL/SQL処理
END;

CREATE OR REPLACE TRIGGER trg_emp_seq
    BEFORE INSERT ON employees
    FOR EACH ROW
BEGIN
    -- シーケンスから主キーを自動採番
    IF :NEW.emp_id IS NULL THEN
        :NEW.emp_id := emp_seq.NEXTVAL;
    END IF;
END;

CREATE OR REPLACE TRIGGER trg_emp_audit
    AFTER INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW
DECLARE
    v_action VARCHAR2(10);
BEGIN
    CASE
        WHEN INSERTING THEN v_action := 'INSERT';
        WHEN UPDATING  THEN v_action := 'UPDATE';
        WHEN DELETING  THEN v_action := 'DELETE';
    END CASE;

    INSERT INTO emp_audit_log (
        audit_date, action_type, emp_id,
        old_emp_name, new_emp_name, changed_by
    ) VALUES (
        SYSDATE, v_action, NVL(:NEW.emp_id, :OLD.emp_id),
        :OLD.emp_name, :NEW.emp_name, USER
    );
END;

-- DML文の実行後に1回だけ起動
CREATE OR REPLACE TRIGGER trg_emp_statement
    AFTER INSERT ON employees
    -- FOR EACH ROW を省略 → 文レベル
BEGIN
    -- 例：処理完了ログをテーブルに記録
    INSERT INTO operation_log (log_date, message)
    VALUES (SYSDATE, 'EMPLOYEESテーブルにINSERTが実行されました');
END;

実行順序の整理

1つのDML文に対して、トリガーは以下の順序で起動します。
BEFORE文レベル → (行ごとに) BEFORE行レベル → DML実行 → AFTER行レベル → AFTER文レベル

CREATE OR REPLACE TRIGGER trg_salary_check
    BEFORE UPDATE OF salary ON employees
    FOR EACH ROW
BEGIN
    -- 給与の減額を禁止する
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            '給与の減額はできません（旧: ' || :OLD.salary ||
            ' → 新: ' || :NEW.salary || '）'
        );
    END IF;
END;

ポイント：WHEN 句内では : を付けずに NEW.salary / OLD.salary と記述します。BEGIN ... END 内では :NEW / :OLD とコロンを付けるのがルールです。

-- 結合ビューの作成
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT
    e.emp_id, e.emp_name, e.salary,
    d.dept_id, d.dept_name
FROM
    employees e
    JOIN departments d ON e.dept_id = d.dept_id;

-- INSTEAD OFトリガーでビューへのINSERTを実現
CREATE OR REPLACE TRIGGER trg_v_emp_dept_insert
    INSTEAD OF INSERT ON v_emp_dept
    FOR EACH ROW
BEGIN
    -- 基底テーブル（employees）にINSERT
    INSERT INTO employees (emp_id, emp_name, salary, dept_id)
    VALUES (:NEW.emp_id, :NEW.emp_name, :NEW.salary, :NEW.dept_id);
END;

INSTEAD OFトリガーの特徴

• ビューにのみ使用可（テーブルには使えない）
• 常に行レベル（FOR EACH ROW）で動作
• BEFORE / AFTER ではなくINSTEAD OFを指定
• ビューへのDMLを「横取り」して、基底テーブルを直接操作する

-- トリガーの削除
DROP TRIGGER trg_emp_audit;

-- 他スキーマのトリガーを削除（DROP ANY TRIGGER 権限が必要）
DROP TRIGGER hr.trg_emp_audit;

注意：テーブルを DROP TABLE すると、そのテーブルに紐づくトリガーも自動的に削除されます。一方、トリガーを DROP してもテーブル本体には影響しません。

PostgreSQLのトリガー

PostgreSQLではトリガー本体を直接書けず、まずトリガー関数を作成し、それを CREATE TRIGGER で紐づける2段構えになります。Oracle とは構文が大きく異なるので注意してください。

-- NG: 行レベルトリガー内で自テーブルを参照 → ORA-04091
CREATE OR REPLACE TRIGGER trg_check_count
    BEFORE INSERT ON employees
    FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    -- employees テーブル自体を SELECT → ミューテーティングエラー!
    SELECT COUNT(*) INTO v_count FROM employees;
    IF v_count >= 1000 THEN
        RAISE_APPLICATION_ERROR(-20002, '社員数の上限に達しました');
    END IF;
END;

-- OK: コンパウンドトリガーで回避（Oracle 11g以降）
CREATE OR REPLACE TRIGGER trg_check_count
    FOR INSERT ON employees
    COMPOUND TRIGGER

    v_count NUMBER;

    -- 文の実行前に件数を取得
    BEFORE STATEMENT IS
    BEGIN
        SELECT COUNT(*) INTO v_count FROM employees;
    END BEFORE STATEMENT;

    -- 各行のINSERT前にチェック
    BEFORE EACH ROW IS
    BEGIN
        v_count := v_count + 1;
        IF v_count > 1000 THEN
            RAISE_APPLICATION_ERROR(-20002, '社員数の上限に達しました');
        END IF;
    END BEFORE EACH ROW;

END trg_check_count;

-- 監査テーブルの作成
CREATE TABLE emp_audit_log (
    audit_id     NUMBER GENERATED ALWAYS AS IDENTITY,
    audit_date   TIMESTAMP DEFAULT SYSTIMESTAMP,
    action_type  VARCHAR2(10),   -- INSERT / UPDATE / DELETE
    emp_id       NUMBER,
    old_salary   NUMBER,
    new_salary   NUMBER,
    changed_by   VARCHAR2(30) DEFAULT USER
);

-- 監査トリガーの作成
CREATE OR REPLACE TRIGGER trg_emp_salary_audit
    AFTER UPDATE OF salary ON employees
    FOR EACH ROW
    WHEN (OLD.salary <> NEW.salary)  -- 変更があった場合のみ
BEGIN
    INSERT INTO emp_audit_log (
        action_type, emp_id, old_salary, new_salary
    ) VALUES (
        'UPDATE', :NEW.emp_id, :OLD.salary, :NEW.salary
    );
END;

CREATE OR REPLACE TRIGGER trg_emp_updated_at
    BEFORE UPDATE ON employees
    FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSTIMESTAMP;
    :NEW.updated_by := USER;
END;

-- 物理削除を論理削除に変換するINSTEAD OFトリガー
CREATE OR REPLACE VIEW v_active_employees AS
SELECT * FROM employees WHERE deleted_flag = 'N';

CREATE OR REPLACE TRIGGER trg_soft_delete
    INSTEAD OF DELETE ON v_active_employees
    FOR EACH ROW
BEGIN
    -- 物理DELETEせず、フラグを更新
    UPDATE employees
    SET
        deleted_flag = 'Y',
        deleted_at   = SYSTIMESTAMP,
        deleted_by   = USER
    WHERE
        emp_id = :OLD.emp_id;
END;

-- シーケンス + トリガーによる自動採番（11g以前の定番パターン）
CREATE SEQUENCE seq_order_id START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_order_seq
    BEFORE INSERT ON orders
    FOR EACH ROW
BEGIN
    :NEW.order_id := seq_order_id.NEXTVAL;
END;

ポイント：Oracle 12c 以降では GENERATED ALWAYS AS IDENTITY カラムが使えるため、自動採番のためだけにトリガーを作る必要はなくなりました。レガシーシステムの保守で見かけるパターンとして覚えておきましょう。

記事のまとめ

• トリガー情報は USER_TRIGGERS（自分）→ ALL_TRIGGERS（アクセス可能）→ DBA_TRIGGERS（全体）の3ビューで確認
• ソースコードの取得は USER_SOURCE または DBMS_METADATA.GET_DDL
• BEFORE は値の変更が可能（自動採番等）、AFTER は参照のみ（監査ログ等）
• 行レベル（FOR EACH ROW）は行ごとに起動、文レベルはDML文ごとに1回
• :NEW / :OLD で変更前後のデータにアクセス
• INSTEAD OF トリガーでビューへのDMLを実現
• ORA-04091（ミューテーティングテーブル）はコンパウンドトリガーで回避
• 大量データロード時は ALTER TABLE ... DISABLE ALL TRIGGERS で一括無効化
• Oracle 12c以降の自動採番には IDENTITY カラムが推奨