Why should you care?

Regulators ask “who changed this balance?” — audit triggers on OracleCore tables answer that.

See it live — copy this example

Run these statements in SQL Developer or SQL*Plus against your lab PDB. Prefer a practice user — not SYS — for application SQL.

CREATE TABLE customers_audit (
  audit_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id NUMBER,
  old_balance NUMBER,
  new_balance NUMBER,
  changed_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  changed_by VARCHAR2(128)
);

CREATE OR REPLACE TRIGGER trg_customers_bal_au
AFTER UPDATE OF balance ON customers
FOR EACH ROW
BEGIN
  INSERT INTO customers_audit(customer_id, old_balance, new_balance, changed_by)
  VALUES (:OLD.customer_id, :OLD.balance, :NEW.balance, USER);
END;
/

UPDATE customers SET balance = balance + 50 WHERE customer_id = 1;
COMMIT;
SELECT * FROM customers_audit;

What happened?

• :OLD and :NEW are the row values before/after the change.
• AFTER UPDATE OF balance fires only when balance changes.
• USER is the Oracle session user.

Practice next

1. Create audit table and trigger.
2. Update a balance and SELECT audit rows.
3. Update full_name only and confirm no audit row.
4. Also log INSERT events.
5. Add a column action_type = 'UPDATE'.

Remember

Triggers fire automatically on DML. :OLD / :NEW hold before/after values. Prefer triggers for audit; prefer packages for business rules.

Balance audit trail

Compliance reviews every balance change for a disputed account.

Outcome: customers_audit shows old/new values and who changed them.