Oracle ORA-04094 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04094: Cannot ROLLBACK in a Trigger — Causes, Fixes & Prevention

ORA-04094 is thrown by Oracle Database when a ROLLBACK statement is executed inside a trigger body. Since triggers operate within the same transaction context as the DML statement that fired them, Oracle does not permit explicit transaction control statements like ROLLBACK inside a trigger. Understanding this limitation is essential for any PL/SQL developer working with Oracle triggers.

Top 3 Causes

1. Explicit ROLLBACK Inside the Trigger Body

The most common cause is placing a ROLLBACK statement directly in the trigger's execution or exception block.

-- Problematic trigger (causes ORA-04094)
CREATE OR REPLACE TRIGGER trg_validate_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
  IF :NEW.salary < 0 THEN
    ROLLBACK; -- NOT allowed inside a trigger
  END IF;
END;
/

-- Fix: Use RAISE_APPLICATION_ERROR instead
CREATE OR REPLACE TRIGGER trg_validate_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
  IF :NEW.salary < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative.');
    -- Oracle automatically rolls back the triggering DML on unhandled exceptions
  END IF;
END;
/

2. Calling a Stored Procedure That Contains ROLLBACK

Even without a direct ROLLBACK in the trigger, calling a procedure that internally executes ROLLBACK will cause the same error.

-- Problematic procedure called from a trigger
CREATE OR REPLACE PROCEDURE log_and_rollback(p_msg IN VARCHAR2) IS
BEGIN
  INSERT INTO app_log (msg, ts) VALUES (p_msg, SYSDATE);
  ROLLBACK; -- Will cause ORA-04094 when called from a trigger
END;
/

-- Fix: Use PRAGMA AUTONOMOUS_TRANSACTION for logging procedures
CREATE OR REPLACE PROCEDURE log_event(p_msg IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO app_log (msg, ts) VALUES (p_msg, SYSDATE);
  COMMIT; -- Allowed within an autonomous transaction
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

-- Safe trigger using the autonomous transaction procedure
CREATE OR REPLACE TRIGGER trg_audit_emp
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
  log_event('Employee table modified.');
END;
/

3. Code Migrated from Other Databases (MySQL / SQL Server)

Other databases such as MySQL or SQL Server allow transaction rollbacks inside triggers. Migrated code often retains these patterns, which are incompatible with Oracle's transaction model.

-- MySQL-style logic (invalid in Oracle after migration)
-- Equivalent Oracle-safe version:
CREATE OR REPLACE TRIGGER trg_check_order_status
BEFORE INSERT ON orders
FOR EACH ROW
DECLARE
  v_status VARCHAR2(20);
BEGIN
  SELECT status INTO v_status
  FROM customers
  WHERE customer_id = :NEW.customer_id;

  IF v_status = 'SUSPENDED' THEN
    -- Replace any ROLLBACK with RAISE_APPLICATION_ERROR
    RAISE_APPLICATION_ERROR(-20010,
      'Orders not allowed for suspended customer: ' || :NEW.customer_id);
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20011,
      'Customer not found: ' || :NEW.customer_id);
END;
/

Quick Fix Summary

Scenario	Solution
Direct `ROLLBACK` in trigger	Replace with `RAISE_APPLICATION_ERROR`
Procedure with `ROLLBACK` called from trigger	Refactor using `PRAGMA AUTONOMOUS_TRANSACTION`
Migrated code from MySQL/SQL Server	Rewrite TCL statements to Oracle exception patterns

Prevention Tips

1. Enforce a No-TCL Rule in Triggers via Code Review
Add a mandatory check to your code review process and CI/CD pipeline to reject any trigger containing ROLLBACK, COMMIT, or SAVEPOINT. Static analysis tools like PL/SQL Cop or SonarQube with Oracle plugins can automate this check before deployment.

2. Always Use AUTONOMOUS_TRANSACTION for Audit/Logging Triggers
Any trigger designed for logging or auditing purposes should delegate persistence logic to a procedure declared with PRAGMA AUTONOMOUS_TRANSACTION. This ensures logs are committed independently of the parent transaction and eliminates any need for TCL statements inside the trigger itself.

Related Oracle Errors

ORA-04092 — COMMIT is not allowed in a trigger (sibling error to ORA-04094)
ORA-04091 — Mutating table error; often co-occurs with poorly designed triggers
ORA-04095 — Trigger already exists for the same table and timing point

📖 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