Oracle ORA-04092 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04092: cannot COMMIT in a trigger

ORA-04092 is thrown when a COMMIT (or ROLLBACK) statement is executed inside an Oracle trigger. Because a trigger runs within the same transaction context as the DML statement that fired it, Oracle strictly prohibits any attempt to independently commit or roll back from within a trigger body. This error is common in legacy systems where business logic is heavily embedded in triggers.

Top 3 Causes and Fixes

Cause 1: Direct COMMIT Inside the Trigger Body

The most straightforward cause — a developer places a COMMIT statement directly inside the trigger to immediately persist changes.

-- ❌ Causes ORA-04092
CREATE OR REPLACE TRIGGER trg_order_log
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_log (order_id, log_date)
    VALUES (:NEW.order_id, SYSDATE);
    COMMIT; -- Not allowed inside a trigger
END;
/

-- ✅ Fixed: Remove COMMIT; let the calling transaction handle it
CREATE OR REPLACE TRIGGER trg_order_log
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_log (order_id, log_date)
    VALUES (:NEW.order_id, SYSDATE);
    -- No COMMIT here; handled by the application layer
END;
/

Fix: Remove the COMMIT from the trigger. Let the application or the top-level PL/SQL block manage the transaction boundary.

Cause 2: Calling a Stored Procedure That Contains COMMIT

Even without a direct COMMIT in the trigger, calling a procedure that internally commits will raise ORA-04092. This is especially tricky with shared logging utilities.

-- ❌ Problematic shared procedure
CREATE OR REPLACE PROCEDURE write_log(p_msg VARCHAR2) IS
BEGIN
    INSERT INTO app_log (msg, log_date) VALUES (p_msg, SYSDATE);
    COMMIT; -- This will cause ORA-04092 when called from a trigger
END;
/

-- ✅ Fixed: Declare as AUTONOMOUS_TRANSACTION
CREATE OR REPLACE PROCEDURE write_log(p_msg VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO app_log (msg, log_date) VALUES (p_msg, SYSDATE);
    COMMIT; -- Safe inside autonomous transaction
END;
/

Fix: Add PRAGMA AUTONOMOUS_TRANSACTION to the called procedure so it runs in its own independent transaction scope.

Cause 3: Missing PRAGMA AUTONOMOUS_TRANSACTION in Audit Triggers

Developers often want audit or error log records to be committed immediately regardless of whether the parent transaction succeeds or fails — but forget to declare the autonomous transaction pragma.

-- ❌ Missing PRAGMA — causes ORA-04092
CREATE OR REPLACE TRIGGER trg_salary_change
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_audit (emp_id, old_sal, new_sal, chg_date)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
    COMMIT; -- ORA-04092 without PRAGMA
END;
/

-- ✅ Correct: Use PRAGMA AUTONOMOUS_TRANSACTION
CREATE OR REPLACE TRIGGER trg_salary_change
AFTER UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO salary_audit (emp_id, old_sal, new_sal, chg_date)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
    COMMIT; -- Commits only within the autonomous transaction
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

⚠️ Warning: Changes committed inside an autonomous transaction are not rolled back if the parent transaction rolls back. Use this only for audit logs, error logs, or scenarios where permanent recording is explicitly required.

Quick Prevention Tips

Enforce a coding standard: Add a mandatory code review checklist item — "No TCL statements (COMMIT/ROLLBACK/SAVEPOINT) inside trigger bodies." Use static analysis tools where possible.
Document shared procedures: Clearly mark which utility procedures use PRAGMA AUTONOMOUS_TRANSACTION and which do not, so developers know whether they are safe to call from a trigger context.

Related Oracle Errors

Error Code	Description
ORA-04091	Table is mutating — trigger cannot read/modify the firing table
ORA-06519	Active autonomous transaction detected and rolled back
ORA-04093	LONG column references not allowed in triggers

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community