【Oracle】特定の列が更新されたときだけ発火するトリガーの作成方法｜UPDATE OF・UPDATING()・NULL安全な変更検出・実務パターンまで解説

-- salary 列が UPDATE されたときだけ発火
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary 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;
/

-- salary を更新 → トリガー発火
UPDATE employees SET salary = 5000 WHERE employee_id = 100;

-- department_id を更新 → トリガー発火しない
UPDATE employees SET department_id = 20 WHERE employee_id = 100;

-- salary または commission_pct が更新されたとき発火
CREATE OR REPLACE TRIGGER trg_compensation_audit
AFTER UPDATE OF salary, commission_pct ON employees
FOR EACH ROW
BEGIN
    INSERT INTO compensation_audit
        (employee_id, field_name, old_value, new_value, changed_at)
    VALUES
        (:OLD.employee_id,
         CASE WHEN UPDATING('SALARY') THEN 'SALARY'
              WHEN UPDATING('COMMISSION_PCT') THEN 'COMMISSION_PCT'
         END,
         CASE WHEN UPDATING('SALARY') THEN TO_CHAR(:OLD.salary)
              WHEN UPDATING('COMMISSION_PCT') THEN TO_CHAR(:OLD.commission_pct)
         END,
         CASE WHEN UPDATING('SALARY') THEN TO_CHAR(:NEW.salary)
              WHEN UPDATING('COMMISSION_PCT') THEN TO_CHAR(:NEW.commission_pct)
         END,
         SYSDATE);
END;
/

-- INSERT / UPDATE / DELETE に対応する汎用トリガー内で
-- 特定列の更新時だけ追加処理を行う
CREATE OR REPLACE TRIGGER trg_emp_all_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    -- 共通: 全 DML の監査ログ
    INSERT INTO audit_log (table_name, action, action_date)
    VALUES ('EMPLOYEES',
            CASE WHEN INSERTING THEN 'INSERT'
                 WHEN UPDATING  THEN 'UPDATE'
                 WHEN DELETING  THEN 'DELETE' END,
            SYSDATE);

    -- salary が更新されたときだけ追加処理
    IF UPDATING('SALARY') THEN
        INSERT INTO salary_audit (employee_id, old_sal, new_sal, changed_at)
        VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
    END IF;

    -- status が更新されたときだけ追加処理
    IF UPDATING('STATUS') THEN
        -- ステータス変更通知
        NULL;  -- 通知処理
    END IF;
END;
/

-- NG: NULL を含む比較が正しく動かない
IF :OLD.commission_pct <> :NEW.commission_pct THEN
    -- :OLD が NULL または :NEW が NULL の場合、<> は UNKNOWN を返す
    -- → このブロックは実行されない!
END IF;

-- 例: commission_pct が NULL → 0.1 に変更されても検出されない

-- 方法(1): NVL で NULL を代替値に変換
IF NVL(:OLD.commission_pct, -1) <> NVL(:NEW.commission_pct, -1) THEN
    -- NULL → 0.1 の変更も検出可能
END IF;

-- 方法(2): DECODE で NULL 同士を等値判定
IF DECODE(:OLD.commission_pct, :NEW.commission_pct, 0, 1) = 1 THEN
    -- DECODE は NULL 同士を「等しい」と扱う → 変更時のみ 1 を返す
END IF;

-- 方法(3): OR IS NULL パターン（汎用）
IF (:OLD.commission_pct <> :NEW.commission_pct)
   OR (:OLD.commission_pct IS NULL AND :NEW.commission_pct IS NOT NULL)
   OR (:OLD.commission_pct IS NOT NULL AND :NEW.commission_pct IS NULL) THEN
    -- 完全な変更検出（冗長だが最も確実）
END IF;

CREATE OR REPLACE TRIGGER trg_salary_change_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NVL(NEW.salary, -1) <> NVL(OLD.salary, -1))  -- 値が実際に変わった場合のみ
BEGIN
    INSERT INTO salary_audit
        (employee_id, old_salary, new_salary, change_pct,
         changed_at, changed_by)
    VALUES (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        ROUND((:NEW.salary - :OLD.salary) / NULLIF(:OLD.salary, 0) * 100, 2),
        SYSDATE,
        USER
    );
END;
/

-- ステータスの遷移ルール:
-- PENDING → ACTIVE → INACTIVE（逆方向は禁止）
CREATE OR REPLACE TRIGGER trg_status_transition
BEFORE UPDATE OF status ON orders
FOR EACH ROW
BEGIN
    -- INACTIVE から ACTIVE への逆遷移を禁止
    IF :OLD.status = 'INACTIVE' AND :NEW.status = 'ACTIVE' THEN
        RAISE_APPLICATION_ERROR(-20010,
            'INACTIVE から ACTIVE への遷移は許可されていません');
    END IF;

    -- ACTIVE から PENDING への逆遷移を禁止
    IF :OLD.status = 'ACTIVE' AND :NEW.status = 'PENDING' THEN
        RAISE_APPLICATION_ERROR(-20011,
            'ACTIVE から PENDING への遷移は許可されていません');
    END IF;
END;
/

-- employee_id（主キー）の変更を禁止
CREATE OR REPLACE TRIGGER trg_prevent_id_change
BEFORE UPDATE OF employee_id ON employees
FOR EACH ROW
BEGIN
    RAISE_APPLICATION_ERROR(-20020,
        'employee_id の変更は禁止されています');
END;
/

-- 特定条件下でのみ変更を禁止（承認済みレコードの金額変更禁止）
CREATE OR REPLACE TRIGGER trg_prevent_amount_change
BEFORE UPDATE OF amount ON invoices
FOR EACH ROW
WHEN (OLD.status = 'APPROVED')
BEGIN
    IF :OLD.amount <> :NEW.amount THEN
        RAISE_APPLICATION_ERROR(-20021,
            '承認済みの請求書の金額は変更できません');
    END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_emp_change_detail
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
    PROCEDURE log_change(p_col VARCHAR2, p_old VARCHAR2, p_new VARCHAR2) IS
    BEGIN
        IF NVL(p_old, CHR(0)) <> NVL(p_new, CHR(0)) THEN
            INSERT INTO change_history
                (table_name, record_id, column_name, old_value, new_value, changed_at)
            VALUES
                ('EMPLOYEES', :OLD.employee_id, p_col, p_old, p_new, SYSDATE);
        END IF;
    END;
BEGIN
    log_change('SALARY',         TO_CHAR(:OLD.salary),         TO_CHAR(:NEW.salary));
    log_change('LAST_NAME',      :OLD.last_name,               :NEW.last_name);
    log_change('DEPARTMENT_ID',  TO_CHAR(:OLD.department_id),  TO_CHAR(:NEW.department_id));
    log_change('COMMISSION_PCT', TO_CHAR(:OLD.commission_pct), TO_CHAR(:NEW.commission_pct));
END;
/

-- 給与が 20% 以上変更された場合にアラートテーブルに記録
CREATE OR REPLACE TRIGGER trg_salary_alert
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary * 1.2 OR NEW.salary < OLD.salary * 0.8)
BEGIN
    INSERT INTO salary_alerts
        (employee_id, old_salary, new_salary, change_pct, alert_date)
    VALUES (
        :OLD.employee_id,
        :OLD.salary,
        :NEW.salary,
        ROUND((:NEW.salary - :OLD.salary) / :OLD.salary * 100, 1),
        SYSDATE
    );
END;
/