【Oracle】トリガーを作成する方法｜BEFORE/AFTER・行/文レベル・:NEW/:OLD・INSTEAD OF・有効化/無効化まで解説

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE [OF column_name] | DELETE}
[OR {INSERT | UPDATE | DELETE}]
ON table_name
[FOR EACH ROW]
[WHEN (condition)]
[DECLARE
    -- variables]
BEGIN
    -- trigger body
END;
/

-- employees テーブルに INSERT されたらログに記録
CREATE OR REPLACE TRIGGER trg_emp_insert_log
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, key_value, action_date, action_user)
    VALUES ('EMPLOYEES', 'INSERT', :NEW.employee_id, SYSDATE, USER);
END;
/

-- INSERT / UPDATE 前に created_at / updated_at を自動設定
CREATE OR REPLACE TRIGGER trg_emp_timestamp
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        :NEW.created_at := SYSDATE;
    END IF;
    :NEW.updated_at := SYSDATE;
END;
/

-- UPDATE 後に変更内容を監査テーブルに記録
CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at, changed_by)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE, USER);
END;
/

-- employees テーブルへの DML が完了した後に 1 回だけ実行
CREATE OR REPLACE TRIGGER trg_emp_statement
AFTER INSERT OR UPDATE OR DELETE ON employees
-- FOR EACH ROW なし = 文レベル
BEGIN
    INSERT INTO change_notifications (table_name, notified_at)
    VALUES ('EMPLOYEES', SYSDATE);
END;
/

-- UPDATE トリガー: 給与変更の監査（:OLD と :NEW の比較）
CREATE OR REPLACE TRIGGER trg_salary_change
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary <> OLD.salary)  -- WHEN 句ではコロンを付けない
BEGIN
    INSERT INTO salary_history
        (employee_id, old_salary, new_salary, changed_at)
    VALUES
        (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

CREATE OR REPLACE TRIGGER trg_emp_all_dml
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
    v_action VARCHAR2(10);
    v_key    NUMBER;
BEGIN
    IF INSERTING THEN
        v_action := 'INSERT';
        v_key    := :NEW.employee_id;
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
        v_key    := :OLD.employee_id;
    ELSIF DELETING THEN
        v_action := 'DELETE';
        v_key    := :OLD.employee_id;
    END IF;

    INSERT INTO audit_log (table_name, action, key_value, action_date, action_user)
    VALUES ('EMPLOYEES', v_action, v_key, SYSDATE, USER);
END;
/

-- 結合ビュー
CREATE OR REPLACE VIEW emp_dept_view 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;

-- ビューへの UPDATE を可能にする INSTEAD OF トリガー
CREATE OR REPLACE TRIGGER trg_emp_dept_view_update
INSTEAD OF UPDATE ON emp_dept_view
FOR EACH ROW
BEGIN
    UPDATE employees
    SET last_name = :NEW.last_name,
        salary    = :NEW.salary
    WHERE employee_id = :OLD.employee_id;

    UPDATE departments
    SET department_name = :NEW.department_name
    WHERE department_name = :OLD.department_name;
END;
/

-- これでビュー経由の UPDATE が可能になる
UPDATE emp_dept_view SET salary = 5000 WHERE employee_id = 100;

-- トリガーを無効化（一時停止）
ALTER TRIGGER trg_emp_insert_log DISABLE;

-- トリガーを有効化（再開）
ALTER TRIGGER trg_emp_insert_log ENABLE;

-- テーブル上の全トリガーを一括無効化（大量データ投入時に便利）
ALTER TABLE employees DISABLE ALL TRIGGERS;

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

-- トリガーを削除
DROP TRIGGER trg_emp_insert_log;

-- 自分のスキーマのトリガー一覧
SELECT trigger_name, trigger_type, triggering_event,
       table_name, status
FROM user_triggers
ORDER BY table_name, trigger_name;

-- トリガーのソースコードを確認
SELECT trigger_body FROM user_triggers WHERE trigger_name = 'TRG_EMP_INSERT_LOG';

CREATE OR REPLACE TRIGGER trg_orders_timestamp
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        :NEW.created_at := SYSTIMESTAMP;
        :NEW.created_by := USER;
    END IF;
    :NEW.updated_at := SYSTIMESTAMP;
    :NEW.updated_by := USER;
END;
/

-- salary が 0 未満にならないようにチェック
CREATE OR REPLACE TRIGGER trg_emp_salary_check
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '給与は 0 以上で入力してください');
    END IF;
    IF UPDATING AND :NEW.salary > :OLD.salary * 2 THEN
        RAISE_APPLICATION_ERROR(-20002, '給与の増加率が上限（200%）を超えています');
    END IF;
END;
/

-- DELETE 操作を禁止する
CREATE OR REPLACE TRIGGER trg_emp_soft_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    RAISE_APPLICATION_ERROR(-20003,
        '物理削除は禁止です。UPDATE SET is_deleted=1 を使ってください');
END;
/

-- Oracle 11g 以前（12c 以降は IDENTITY 列を推奨）
CREATE SEQUENCE seq_emp_id START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_emp_auto_id
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF :NEW.employee_id IS NULL THEN
        :NEW.employee_id := seq_emp_id.NEXTVAL;
    END IF;
END;
/