【Oracle】トリガー（TRIGGER）完全ガイド｜DML トリガー・DDL トリガー・INSTEAD OF トリガー・有効化・無効化まで解説

-- BEFORE INSERT トリガー: INSERT 前に自動的に created_at と updated_at を設定する
CREATE OR REPLACE TRIGGER trg_orders_bi
BEFORE INSERT ON orders
FOR EACH ROW    -- 行レベルトリガー（行ごとに発火）
BEGIN
    :NEW.created_at := SYSDATE;   -- INSERT 直前に created_at を現在日時に設定
    :NEW.updated_at := SYSDATE;
    :NEW.created_by := SYS_CONTEXT('USERENV', 'SESSION_USER');  -- ログインユーザー
END;
/

-- BEFORE UPDATE トリガー: UPDATE 前に updated_at を更新する
CREATE OR REPLACE TRIGGER trg_orders_bu
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSDATE;
    :NEW.updated_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
END;
/

-- 監査ログテーブルを前提とする
-- CREATE TABLE orders_audit (
--     audit_id    NUMBER DEFAULT audit_seq.NEXTVAL PRIMARY KEY,
--     order_id    NUMBER, action VARCHAR2(10),
--     old_status  VARCHAR2(20), new_status VARCHAR2(20),
--     changed_by  VARCHAR2(50), changed_at DATE
-- );

-- AFTER トリガー: INSERT / UPDATE / DELETE を1つで処理する
CREATE OR REPLACE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO orders_audit (order_id, action, new_status, changed_by, changed_at)
        VALUES (:NEW.order_id, 'INSERT', :NEW.status,
                SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE);

    ELSIF UPDATING THEN
        INSERT INTO orders_audit (order_id, action, old_status, new_status, changed_by, changed_at)
        VALUES (:NEW.order_id, 'UPDATE', :OLD.status, :NEW.status,
                SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE);

    ELSIF DELETING THEN
        INSERT INTO orders_audit (order_id, action, old_status, changed_by, changed_at)
        VALUES (:OLD.order_id, 'DELETE', :OLD.status,
                SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE);
    END IF;
END;
/

-- WHEN 句: 特定の条件のときだけトリガーを発火させる（行レベルのみ）
-- WHEN 句では ":NEW." ではなく "NEW." と書く（コロンなし）
CREATE OR REPLACE TRIGGER trg_emp_salary_check
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary * 2)   -- 給与が2倍以上になる場合のみ発火
BEGIN
    RAISE_APPLICATION_ERROR(-20001,
        '給与を一度に2倍以上に上げることはできません: ' ||
        :OLD.salary || ' → ' || :NEW.salary);
END;
/

-- REFERENCING: :NEW / :OLD の別名を定義する（テーブル名と衝突する場合に使う）
CREATE OR REPLACE TRIGGER trg_new_old_alias
BEFORE INSERT ON orders
REFERENCING NEW AS n OLD AS o   -- :n と :o で参照できる
FOR EACH ROW
BEGIN
    :n.created_at := SYSDATE;
END;
/

-- UPDATE OF 列名: 特定列が更新された場合のみ発火する
CREATE OR REPLACE TRIGGER trg_emp_email_update
AFTER UPDATE OF email ON employees
FOR EACH ROW
BEGIN
    -- email が変更された場合のみ処理する（他の列の UPDATE では発火しない）
    DBMS_OUTPUT.PUT_LINE('Email changed: ' || :OLD.email || ' -> ' || :NEW.email);
END;
/

-- 結合ビューは通常 INSERT / UPDATE / DELETE できない
CREATE OR REPLACE VIEW emp_dept_v AS
SELECT e.employee_id, e.last_name, e.salary,
       d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 上記ビューへの INSERT は ORA-01779 が発生する（キー保持でない結合を含む）
-- INSTEAD OF トリガーでビューへの INSERT を実処理に変換する
CREATE OR REPLACE TRIGGER trg_emp_dept_v_ins
INSTEAD OF INSERT ON emp_dept_v
FOR EACH ROW
DECLARE
    v_dept_id departments.department_id%TYPE;
BEGIN
    -- department_name から department_id を検索する
    SELECT department_id INTO v_dept_id
    FROM departments
    WHERE department_name = :NEW.department_name;

    -- employees テーブルに実際に INSERT する
    INSERT INTO employees (employee_id, last_name, salary, department_id)
    VALUES (:NEW.employee_id, :NEW.last_name, :NEW.salary, v_dept_id);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, '部署が見つかりません: ' || :NEW.department_name);
END;
/

-- 上記トリガーにより、ビューに INSERT できるようになる
INSERT INTO emp_dept_v (employee_id, last_name, salary, department_name)
VALUES (300, 'Yamada', 50000, 'IT');

-- DDL 監査テーブルを前提とする
-- CREATE TABLE ddl_audit (
--     event_time  DATE, event_type VARCHAR2(50),
--     object_type VARCHAR2(50), object_name VARCHAR2(128),
--     schema_name VARCHAR2(128), executed_by VARCHAR2(50)
-- );

-- スキーマレベルの DDL トリガー（HR スキーマの DDL を監査）
CREATE OR REPLACE TRIGGER trg_ddl_audit
AFTER DDL ON SCHEMA   -- ON SCHEMA: 現在スキーマの DDL に反応
BEGIN
    INSERT INTO ddl_audit (event_time, event_type, object_type, object_name, schema_name, executed_by)
    VALUES (SYSDATE,
            ORA_SYSEVENT,          -- 'CREATE'/'DROP'/'ALTER' などのイベント種類
            ORA_DICT_OBJ_TYPE,     -- 'TABLE'/'INDEX'/'VIEW' などのオブジェクト種類
            ORA_DICT_OBJ_NAME,     -- オブジェクト名
            ORA_DICT_OBJ_OWNER,    -- スキーマ名
            SYS_CONTEXT('USERENV', 'SESSION_USER'));
    COMMIT;  -- DDL は自動コミットされるため、監査ログも COMMIT する
END;
/

-- データベースレベルの DDL トリガー（全スキーマの DDL を監査・DBA 権限が必要）
CREATE OR REPLACE TRIGGER trg_ddl_db_audit
AFTER CREATE OR DROP OR ALTER ON DATABASE
BEGIN
    INSERT INTO sys.ddl_audit (event_time, event_type, object_type, object_name, schema_name, executed_by)
    VALUES (SYSDATE, ORA_SYSEVENT, ORA_DICT_OBJ_TYPE,
            ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_OWNER,
            SYS_CONTEXT('USERENV', 'SESSION_USER'));
    COMMIT;
END;
/

-- トリガーを無効化する（テーブルのデータロード時などに一時的に停止する）
ALTER TRIGGER trg_orders_bi DISABLE;

-- トリガーを再度有効化する
ALTER TRIGGER trg_orders_bi ENABLE;

-- テーブルのすべてのトリガーを無効化 / 有効化する
ALTER TABLE orders DISABLE ALL TRIGGERS;
ALTER TABLE orders ENABLE ALL TRIGGERS;

-- トリガーのコンパイルエラーを確認する
SHOW ERRORS TRIGGER trg_orders_audit;
-- または
SELECT line, position, text FROM USER_ERRORS
WHERE name = 'TRG_ORDERS_AUDIT' AND type = 'TRIGGER'
ORDER BY line;

-- トリガーの一覧と状態を確認する
SELECT trigger_name, trigger_type, triggering_event,
       table_name, status
FROM USER_TRIGGERS
ORDER BY table_name, trigger_name;

-- トリガーのソースコードを確認する
SELECT text FROM USER_SOURCE
WHERE name = 'TRG_ORDERS_AUDIT' AND type = 'TRIGGER'
ORDER BY line;

-- トリガーを削除する
DROP TRIGGER trg_orders_bi;