【Oracle】トリガー作成・高度な活用完全ガイド｜複合トリガー・DDLトリガー・WHEN句・自律トランザクション・パフォーマンス注意点まで解説

CREATE OR REPLACE TRIGGER trg_emp_compound
    FOR INSERT OR UPDATE OR DELETE ON emp
    COMPOUND TRIGGER

    -- セクション間で共有するコレクション（パッケージ変数的な役割）
    TYPE t_log_rec IS RECORD (
        emp_id   emp.emp_id%TYPE,
        action   VARCHAR2(10),
        changed  TIMESTAMP
    );
    TYPE t_log_tab IS TABLE OF t_log_rec INDEX BY PLS_INTEGER;
    g_logs  t_log_tab;
    g_idx   PLS_INTEGER := 0;

    -- ① 文レベルの前処理
    BEFORE STATEMENT IS
    BEGIN
        g_idx  := 0;
        g_logs.DELETE;
    END BEFORE STATEMENT;

    -- ② 行ごとにログデータを収集（INSERT は重くない）
    AFTER EACH ROW IS
    BEGIN
        g_idx := g_idx + 1;
        g_logs(g_idx).emp_id  := CASE WHEN DELETING THEN :OLD.emp_id ELSE :NEW.emp_id END;
        g_logs(g_idx).action  := CASE WHEN INSERTING THEN 'INSERT'
                                       WHEN UPDATING  THEN 'UPDATE'
                                       ELSE                'DELETE' END;
        g_logs(g_idx).changed := SYSTIMESTAMP;
    END AFTER EACH ROW;

    -- ③ 全行処理後に一括 INSERT（ループ数が少ないため高速）
    AFTER STATEMENT IS
    BEGIN
        FORALL i IN 1 .. g_idx
            INSERT INTO emp_audit (emp_id, action, changed_at)
            VALUES (g_logs(i).emp_id, g_logs(i).action, g_logs(i).changed);
    END AFTER STATEMENT;

END trg_emp_compound;
/

-- DDL 変更を記録するテーブル（事前に作成）
CREATE TABLE ddl_audit_log (
    log_id      NUMBER GENERATED ALWAYS AS IDENTITY,
    event_type  VARCHAR2(30),
    object_type VARCHAR2(30),
    object_name VARCHAR2(128),
    schema_name VARCHAR2(128),
    db_user     VARCHAR2(128),
    os_user     VARCHAR2(128),
    event_time  TIMESTAMP DEFAULT SYSTIMESTAMP,
    CONSTRAINT pk_ddl_audit PRIMARY KEY (log_id)
);

-- スキーマ内のすべての DDL を記録するトリガー
CREATE OR REPLACE TRIGGER trg_ddl_audit
    AFTER DDL ON SCHEMA
BEGIN
    INSERT INTO ddl_audit_log (
        event_type, object_type, object_name, schema_name,
        db_user, os_user
    ) VALUES (
        ORA_SYSEVENT,
        ORA_DICT_OBJ_TYPE,
        ORA_DICT_OBJ_NAME,
        ORA_DICT_OBJ_OWNER,
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        SYS_CONTEXT('USERENV', 'OS_USER')
    );
END;
/

-- 本番スキーマでの DROP TABLE を禁止する
CREATE OR REPLACE TRIGGER trg_no_drop
    BEFORE DROP ON SCHEMA
BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'この環境では DROP TABLE は禁止されています');
END;
/

-- ログイン記録テーブル（事前に作成）
CREATE TABLE logon_audit (
    session_id  NUMBER,
    db_user     VARCHAR2(128),
    os_user     VARCHAR2(128),
    ip_address  VARCHAR2(40),
    logon_time  TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- データベース全体へのログオンを記録
CREATE OR REPLACE TRIGGER trg_logon_audit
    AFTER LOGON ON DATABASE
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO logon_audit (session_id, db_user, os_user, ip_address)
    VALUES (
        SYS_CONTEXT('USERENV', 'SESSIONID'),
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        SYS_CONTEXT('USERENV', 'OS_USER'),
        SYS_CONTEXT('USERENV', 'IP_ADDRESS')
    );
    COMMIT;
END;
/

-- SCOTT ユーザーのログオンを禁止（例：セキュリティポリシー）
CREATE OR REPLACE TRIGGER trg_deny_scott
    AFTER LOGON ON DATABASE
BEGIN
    IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'SCOTT' THEN
        RAISE_APPLICATION_ERROR(-20002, 'このユーザーのログオンは禁止されています');
    END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_high_salary_log
    AFTER UPDATE OF salary ON emp
    FOR EACH ROW
    WHEN (NEW.salary >= 1000000)   -- :NEW ではなく NEW と書く（WHEN 句は特別）
BEGIN
    INSERT INTO salary_change_log (emp_id, old_sal, new_sal, changed_at)
    VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSTIMESTAMP);
END;
/

CREATE OR REPLACE TRIGGER trg_emp_key_change
    AFTER UPDATE OF salary, dept_id ON emp
    FOR EACH ROW
BEGIN
    IF UPDATING('SALARY') THEN
        INSERT INTO emp_change_log (emp_id, change_type, old_val, new_val)
        VALUES (:NEW.emp_id, 'SALARY', TO_CHAR(:OLD.salary), TO_CHAR(:NEW.salary));
    END IF;
    IF UPDATING('DEPT_ID') THEN
        INSERT INTO emp_change_log (emp_id, change_type, old_val, new_val)
        VALUES (:NEW.emp_id, 'DEPT_ID', TO_CHAR(:OLD.dept_id), TO_CHAR(:NEW.dept_id));
    END IF;
END;
/

-- trg_emp_validate が先に実行されてから trg_emp_audit が実行される
CREATE OR REPLACE TRIGGER trg_emp_audit
    AFTER INSERT ON emp
    FOR EACH ROW
    FOLLOWS trg_emp_validate    -- trg_emp_validate の後に実行
BEGIN
    INSERT INTO emp_audit (emp_id, action) VALUES (:NEW.emp_id, 'INSERT');
END;
/

CREATE OR REPLACE TRIGGER trg_emp_error_log
    BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;  -- 自律トランザクション宣言
BEGIN
    -- このトリガー内の処理は独立したトランザクション
    INSERT INTO emp_dml_log (
        action, emp_id, executed_by, log_time
    ) VALUES (
        CASE WHEN INSERTING THEN 'INSERT' ELSE 'UPDATE' END,
        :NEW.emp_id,
        SYS_CONTEXT('USERENV', 'SESSION_USER'),
        SYSTIMESTAMP
    );
    COMMIT;  -- 自律トランザクションは必ず COMMIT または ROLLBACK で終了させる
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        -- ログ書き込み失敗は無視して本体処理を継続させる
END;
/

-- NG: emp 行レベルトリガー内で emp を SELECT している
CREATE OR REPLACE TRIGGER trg_emp_mutating  -- このトリガーは ORA-04091 を起こす
    AFTER INSERT ON emp
    FOR EACH ROW
DECLARE
    v_cnt NUMBER;
BEGIN
    -- 同じ emp テーブルを参照 → ORA-04091
    SELECT COUNT(*) INTO v_cnt FROM emp WHERE dept_id = :NEW.dept_id;
END;
/

-- OK: 複合トリガーの AFTER STATEMENT セクションで集計する
CREATE OR REPLACE TRIGGER trg_emp_dept_check
    FOR INSERT ON emp
    COMPOUND TRIGGER

    TYPE dept_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    g_dept_ids dept_tab;
    g_idx PLS_INTEGER := 0;

    AFTER EACH ROW IS
    BEGIN
        g_idx := g_idx + 1;
        g_dept_ids(g_idx) := :NEW.dept_id;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
        v_cnt NUMBER;
    BEGIN
        -- INSERT 完了後の AFTER STATEMENT ではテーブルを参照できる
        FOR i IN 1 .. g_idx LOOP
            SELECT COUNT(*) INTO v_cnt FROM emp WHERE dept_id = g_dept_ids(i);
            IF v_cnt > 100 THEN
                RAISE_APPLICATION_ERROR(-20003, '部署の人数上限を超えています');
            END IF;
        END LOOP;
    END AFTER STATEMENT;

END trg_emp_dept_check;
/