Audit Table with Trigger in Oracle Database

🔍 Audit Table with Trigger – Core Concept

Audit tables are used to log changes (INSERT, UPDATE, DELETE) made to a main table. This tracking is handled automatically using triggers.

⚙️ How It Works (Step-by-Step):

1. Main Table: The actual table where business data is stored.

2. Audit Table: A mirror table (same columns + extra audit columns like action type, timestamp, user).

3. Trigger: A BEFORE or AFTER trigger on the main table.

4. Trigger Logic:

Detects the type of operation (INSERT, UPDATE, DELETE).

Captures old/new values.

Inserts a log entry into the audit table.

💡 Example (Concise):

-- Main Table
CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER
);

-- Audit Table
CREATE TABLE emp_audit (
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER,
action VARCHAR2(10),
changed_by VARCHAR2(100),
changed_on DATE
);

-- Trigger
CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO emp_audit VALUES (:NEW.emp_id, :NEW.emp_name, :NEW.salary, 'INSERT', USER, SYSDATE);
ELSIF UPDATING THEN
INSERT INTO emp_audit VALUES (:NEW.emp_id, :NEW.emp_name, :NEW.salary, 'UPDATE', USER, SYSDATE);
ELSIF DELETING THEN
INSERT INTO emp_audit VALUES (:OLD.emp_id, :OLD.emp_name, :OLD.salary, 'DELETE', USER, SYSDATE);
END IF;
END;
/

✅ Summary Line:

An audit table captures changes on a main table using a trigger that detects the operation type (INSERT, UPDATE, DELETE) and logs the change with metadata like user and timestamp.

Audit tables are used to track changes made to critical data in main tables. Triggers, specifically AFTER DML triggers, are defined on the main table to automatically fire during INSERT, UPDATE, or DELETE operations. When triggered, they capture key information such as old/new values, operation type, timestamp, and the user who performed the action. This data is then inserted into the audit table to maintain a detailed change history. Together, audit tables and triggers ensure data traceability, accountability, and compliance with audit requirements.