mrcaption49

Posted on Jul 19

Audit Tables + Triggers + Pragma Autonomous Transactions in Oracle | mrcaption49

#webdev #programming #oracle #data

Audit Tables, Triggers & Autonomous Transactions in Oracle

We have implemented a secure auditing mechanism using audit tables, triggers, and PRAGMA AUTONOMOUS_TRANSACTION to ensure reliable log entry. The audit triggers capture DML operations like insert, update, and delete, and log relevant details into dedicated audit tables. By using the autonomous transaction pragma, the audit logging is committed independently of the main transaction. This ensures that even if the primary transaction fails or is rolled back, the audit log remains intact. This approach provides a robust and tamper-proof logging solution, ideal for security and compliance requirements.

🛳️ Secure Cargo Tracking with Audit Tables, Triggers & Autonomous Transactions in Oracle

In enterprise systems—especially in logistics and supply chain applications like a Cargo Management System—it's critical to track who modified what, when, and how. Whether you're building for compliance, security, or business intelligence, auditing is the key.

In this post, we’ll walk through how to implement a robust auditing system using:

Audit Tables
Triggers
Stored Procedures with PRAGMA AUTONOMOUS_TRANSACTION

We’ll tie everything together with a real-world example of a cargo shipment audit log.

🚛 1. Cargo Shipment Table Example

Here’s the main table for the application that we are auditing:

CREATE TABLE cargo_shipment (
  cargo_id      NUMBER PRIMARY KEY,
  cargo_name    VARCHAR2(100),
  weight        NUMBER,
  destination   VARCHAR2(100),
  status        VARCHAR2(50),
  last_updated  DATE
);

Every time a user adds, updates, or deletes a cargo entry, the above trigger and procedure will log the operation in the cargo_audit_log table—even if the transaction fails afterward.

🗃️ 2. Audit Table – The Foundation for Logging

The first step in an audit mechanism is to create a dedicated audit table. This table holds historical records of all DML activities performed on a critical business table. It typically includes relevant columns from the main table, along with additional audit metadata like action_type (INSERT, UPDATE, DELETE), changed_by (user), changed_on (timestamp), and optionally transaction_id. This table acts as a centralized log, supporting traceability, debugging, and compliance with data governance or regulatory standards.

✅ Code Example – cargo_audit_log

CREATE TABLE cargo_audit_log (
  audit_id      NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cargo_id      NUMBER,
  cargo_name    VARCHAR2(100),
  weight        NUMBER,
  destination   VARCHAR2(100),
  status        VARCHAR2(50),
  action_type   VARCHAR2(10),       -- INSERT / UPDATE / DELETE
  changed_by    VARCHAR2(100),      -- Who changed the data
  changed_on    DATE,               -- Timestamp
  txn_comment   VARCHAR2(200)       -- Description of the operation
);

Verify table creations...

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
FROM ALL_TABLES
WHERE table_name IN ('CARGO_SHIPMENT', 'CARGO_AUDIT_LOG');

🔁 3. Trigger – The Change Detector

After defining the audit table, a trigger is created on the main table to detect any DML operation (INSERT, UPDATE, or DELETE). This trigger is usually an AFTER FOR EACH ROW trigger, meaning it executes once for every row affected by a DML operation. Instead of directly inserting into the audit table within the trigger body, it's a best practice to call a stored procedure from the trigger. This approach improves modularity, simplifies maintenance, and isolates audit logic for reuse across other triggers or modules.

✅ Code Example – trg_cargo_audit

CREATE OR REPLACE TRIGGER trg_cargo_audit
AFTER INSERT OR UPDATE OR DELETE ON cargo_shipment
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    log_cargo_change(
      :NEW.cargo_id, :NEW.cargo_name, :NEW.weight, :NEW.destination, :NEW.status,
      'INSERT', 'New cargo added.'
    );

  ELSIF UPDATING THEN
    log_cargo_change(
      :NEW.cargo_id, :NEW.cargo_name, :NEW.weight, :NEW.destination, :NEW.status,
      'UPDATE', 'Cargo details updated.'
    );

  ELSIF DELETING THEN
    log_cargo_change(
      :OLD.cargo_id, :OLD.cargo_name, :OLD.weight, :OLD.destination, :OLD.status,
      'DELETE', 'Cargo removed from system.'
    );
  END IF;
END;
/

🔐 4. Procedure with PRAGMA AUTONOMOUS_TRANSACTION – The Reliable Logger

The procedure called from within the trigger is marked with PRAGMA AUTONOMOUS_TRANSACTION, making it run in a completely independent transaction context. This is critical because it ensures that even if the main transaction is rolled back due to an error, the audit log remains safely committed. The procedure captures the operation type, user session details, timestamps, and any old/new values, and writes them into the audit table. This decoupled, autonomous design guarantees audit log resilience, integrity, and non-repudiation—even under failure conditions.

✅ Code Example – log_cargo_change

CREATE OR REPLACE PROCEDURE log_cargo_change (
  p_cargo_id     NUMBER,
  p_cargo_name   VARCHAR2,
  p_weight       NUMBER,
  p_destination  VARCHAR2,
  p_status       VARCHAR2,
  p_action       VARCHAR2,
  p_comment      VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO cargo_audit_log (
    cargo_id, cargo_name, weight, destination, status,
    action_type, changed_by, changed_on, txn_comment
  ) VALUES (
    p_cargo_id, p_cargo_name, p_weight, p_destination, p_status,
    p_action, USER, SYSDATE, p_comment
  );

  COMMIT;
END;
/

✅ Final Thoughts

Auditing is often an afterthought in many applications—but it's essential for data integrity, security, and accountability. By combining:

Audit Tables (for logging),
Triggers (for detecting changes),
and PRAGMA AUTONOMOUS_TRANSACTION (for reliability),

you can build a robust, tamper-proof audit system that fits right into enterprise-grade applications like cargo or shipment management systems.

To test the audit functionality implemented using audit tables, triggers, and autonomous transactions, you should perform direct DML operations (i.e., INSERT, UPDATE, DELETE) on the main domain table (e.g., cargo_shipment).
The trigger defined on this table will automatically invoke the audit logging procedure, which runs in an autonomous transaction, and inserts the relevant audit data into the cargo_audit_log table.

✅ Step-by-Step to Test the Setup:

-- 1. Check initial contents

SELECT * FROM cargo_shipment;
SELECT * FROM cargo_audit_log;

-- 2. Perform an INSERT

INSERT INTO cargo_shipment (
  cargo_id,
  cargo_name,
  weight,
  destination,
  status,
  last_updated
) VALUES (
  1,
  'Electronics Components',
  1250.75,
  'Frankfurt, Germany',
  'In Transit',
  SYSDATE
);


-- 3. Perform an UPDATE

UPDATE cargo_shipment
SET status = 'Pending'
WHERE CARGO_ID = 1;

-- 4. Perform a DELETE

DELETE FROM cargo_shipment
WHERE CARGO_ID = 1;

-- 5. Verify Audit Entries

SELECT * FROM cargo_audit_log
ORDER BY changed_on DESC;

🧠 What's Happening Behind the Scenes?

Each of the DML operations triggers the AFTER trigger on cargo_shipment.
The trigger invokes a stored procedure that captures the :OLD and :NEW values, user info, and action type.
The procedure uses PRAGMA AUTONOMOUS_TRANSACTION to log the change in cargo_audit_log without depending on the main transaction commit.

DEV Community