DEV Community

Pranav Bakare
Pranav Bakare

Posted on

Audit Triggers + PRAGMA AUTONOMOUS_TRANSACTION

Audit Triggers + PRAGMA AUTONOMOUS_TRANSACTION

There is a main transactional table where all business operations are recorded. To track changes and maintain accountability, a separate audit table is created to capture the data state changes of the main table. Whenever a DML operation such as INSERT, UPDATE, or DELETE occurs on the main table, a trigger mechanism fires and inserts a corresponding entry into the audit table. To ensure that these audit entries are recorded reliably, even if the main transaction is rolled back, the insertion logic is encapsulated within a PRAGMA AUTONOMOUS_TRANSACTION procedure. This ensures that the audit insertion operates as an independent transaction, committing its changes regardless of the success or failure of the main table operation. As a result, every change to the main table is logged persistently in the audit table, providing a robust, tamper-proof auditing mechanism that guarantees data traceability and accountability. This combination of triggers and autonomous transactions ensures that auditing is handled automatically and consistently, without depending on the outcome of the main transaction.

💡 Summary:

Trigger = detect event

Autonomous procedure = persist audit record independently

Trigger + PRAGMA AUTONOMOUS_TRANSACTION = robust, tamper-proof auditing


Let’s make a simple, fully integrated example with main table, audit table, trigger, and PRAGMA AUTONOMOUS_TRANSACTION procedure so it’s crystal clear.

1️⃣ Main Table (Domain Table)

CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
salary NUMBER,
dept_id NUMBER
);
Enter fullscreen mode Exit fullscreen mode

This is the table where normal business operations occur.


2️⃣ Audit Table

CREATE TABLE employees_audit (
audit_id NUMBER PRIMARY KEY,
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
dept_id NUMBER,
action_type VARCHAR2(10),
action_time DATE
);
Enter fullscreen mode Exit fullscreen mode

Stores all changes (INSERT/UPDATE/DELETE) from the main table.


3️⃣ Audit Procedure with PRAGMA AUTONOMOUS_TRANSACTION

CREATE OR REPLACE PROCEDURE log_employee_audit(
p_emp_id NUMBER,
p_emp_name VARCHAR2,
p_salary NUMBER,
p_dept_id NUMBER,
p_action VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO employees_audit (
audit_id, emp_id, emp_name, salary, dept_id, action_type, action_time
) VALUES (
employees_audit_seq.NEXTVAL, p_emp_id, p_emp_name, p_salary, p_dept_id, p_action, SYSDATE
);
COMMIT; -- Independent commit ensures audit entry persists
END;

Enter fullscreen mode Exit fullscreen mode

PRAGMA AUTONOMOUS_TRANSACTION makes this independent of main table transactions.


4️⃣ Trigger on Main Table

CREATE OR REPLACE TRIGGER trg_employee_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
log_employee_audit(:NEW.emp_id, :NEW.emp_name, :NEW.salary, :NEW.dept_id, 'INSERT');
ELSIF UPDATING THEN
log_employee_audit(:NEW.emp_id, :NEW.emp_name, :NEW.salary, :NEW.dept_id, 'UPDATE');
ELSIF DELETING THEN
log_employee_audit(:OLD.emp_id, :OLD.emp_name, :OLD.salary, :OLD.dept_id, 'DELETE');
END IF;
END;
Enter fullscreen mode Exit fullscreen mode

Trigger automatically detects DML operations on employees.

Calls the autonomous procedure to log the change independently.


5️⃣ How It Works

  1. User inserts/updates/deletes a row in employees.

  2. trg_employee_audit fires automatically.

  3. Trigger calls log_employee_audit, which writes a record to employees_audit.

  4. Even if the main transaction rolls back, the audit entry is persisted because the procedure has an autonomous transaction.

  5. Result → tamper-proof, reliable audit trail for all critical operations.

Behind-the-scenes explanation of what happens step by step when a record is inserted or updated into the main table (employees) with your audit trigger + PRAGMA AUTONOMOUS_TRANSACTION setup.


1️⃣ User performs a DML operation on the main table

Example:

INSERT INTO employees (emp_id, emp_name, salary, dept_id)
VALUES (101, 'Pranav', 50000, 10);

Oracle receives the INSERT command.

The database engine begins a transaction for this operation.

The row is added to the buffer, but it is not yet committed to disk.


2️⃣ Trigger fires automatically

Since trg_employee_audit is defined as AFTER INSERT OR UPDATE OR DELETE, Oracle detects the change and fires the trigger after the DML operation is performed (but still within the same transaction).

The trigger identifies the type of operation (INSERT, UPDATE, or DELETE).


3️⃣ Trigger calls the audit procedure

log_employee_audit(:NEW.emp_id, :NEW.emp_name, :NEW.salary, :NEW.dept_id, 'INSERT');

The procedure log_employee_audit is executed.

Inside the procedure, PRAGMA AUTONOMOUS_TRANSACTION tells Oracle:

“Start a separate transaction for this block, independent of the main transaction.”


4️⃣ Autonomous transaction inserts into the audit table

The procedure inserts a record into employees_audit with:

Employee ID, name, salary, department

Action type (‘INSERT’)

Timestamp (SYSDATE)

Then the procedure commits this transaction immediately, independently of the main transaction.


5️⃣ Control returns to the main transaction

Oracle continues processing the original INSERT in employees.

The main transaction is still active and has not committed yet.

If the main transaction rolls back, the insert in employees is undone, but the audit log remains because it was committed in the autonomous transaction.


6️⃣ End result

Audit table always contains a record of the attempted operation, even if the main table operation fails.

Main table contains the new record only if the transaction commits successfully.

This ensures:

Tamper-proof logging

Full accountability

Traceability of operations


In short:

Main table DML → Trigger fires → Calls autonomous procedure → Audit table entry committed independently → Control returns to main transaction → Main transaction commits or rolls back

Top comments (0)