Audit Tables in Oracle SQL Database

✅ What Are Audit Tables?

Audit tables are auxiliary tables used to log and track changes (INSERT, UPDATE, DELETE) made to a main business table.

🔁 How Are Entries Logged?

Audit tables are typically populated using a database trigger — most commonly called an "audit trigger" — which fires automatically whenever a DML operation happens on the main table.

🔧 Trigger-Based Audit Logging: Core Logic

Operation on Main Table What Happens in Audit Table

INSERT New row values are logged with operation type 'INSERT'.
UPDATE New (or both old and new) row values are logged with 'UPDATE'.
DELETE Old row values are logged with operation type 'DELETE'.

Example Table Structure for Audit Table:

CREATE TABLE employee_audit (
audit_id NUMBER GENERATED ALWAYS AS IDENTITY,
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER,
operation VARCHAR2(10), -- 'INSERT', 'UPDATE', 'DELETE'
changed_by VARCHAR2(100), -- USER or :SESSION_USER
change_time TIMESTAMP DEFAULT SYSTIMESTAMP
);

Example Trigger Logic:

CREATE OR REPLACE TRIGGER trg_employee_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO employee_audit (emp_id, emp_name, salary, operation, changed_by)
VALUES (:NEW.emp_id, :NEW.emp_name, :NEW.salary, 'INSERT', USER);

ELSIF UPDATING THEN
INSERT INTO employee_audit (emp_id, emp_name, salary, operation, changed_by)
VALUES (:NEW.emp_id, :NEW.emp_name, :NEW.salary, 'UPDATE', USER);

ELSIF DELETING THEN
INSERT INTO employee_audit (emp_id, emp_name, salary, operation, changed_by)
VALUES (:OLD.emp_id, :OLD.emp_name, :OLD.salary, 'DELETE', USER);
END IF;
END;

🛡️ Relation to Data Security

Audit tables play a key role in:

Data security

Change tracking

Accountability & compliance

Even though triggers handle the logging, the purpose is to enforce transparency, helping with:

Tracking who changed what

When it happened

And what the data looked like before and/or after

🧠 Final Summary

✅ "Audit tables are like a black box recorder for database tables."

They do not prevent operations, but log them for traceability.

Powered by triggers, often called data security triggers.

INSERT, UPDATE, and DELETE are the key actions that populate audit logs