Oracle ORA-04084 Error: Causes and Solutions Complete Guide

ORA-04084: Cannot Change NEW Values for This Trigger Type

ORA-04084 is thrown by Oracle when a trigger attempts to assign a value to the :NEW pseudo-record in a context where it is not permitted. Oracle strictly controls read/write access to :NEW and :OLD depending on the trigger type (BEFORE/AFTER) and the triggering event (INSERT/UPDATE/DELETE). Understanding this access matrix is essential for any developer writing Oracle triggers.

---

Top 3 Causes

1. Modifying :NEW Inside an AFTER Trigger

The most common cause. By the time an AFTER trigger fires, the DML statement has already been processed, so Oracle does not allow :NEW values to be changed.

-- WRONG: Attempting to modify :NEW in an AFTER trigger
CREATE OR REPLACE TRIGGER trg_emp_after_wrong
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.salary := :NEW.salary * 1.1;  -- ORA-04084 raised here!
END;
/

-- CORRECT: Use a BEFORE trigger to modify :NEW values
CREATE OR REPLACE TRIGGER trg_emp_before_correct
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 2000 THEN
        :NEW.salary := 2000;
    END IF;
    :NEW.last_name := UPPER(:NEW.last_name);
END;
/

2. Modifying :NEW Inside a DELETE Trigger

In a DELETE operation, there is no "new" row being inserted or updated — the row is simply being removed. Therefore, :NEW has no meaningful value and cannot be modified.

-- WRONG: Using :NEW in a DELETE trigger
CREATE OR REPLACE TRIGGER trg_emp_delete_wrong
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    :NEW.employee_id := 0;  -- ORA-04084! No :NEW exists for DELETE
END;
/

-- CORRECT: Use :OLD to capture deleted row data
CREATE OR REPLACE TRIGGER trg_emp_delete_log
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO emp_delete_log (employee_id, last_name, deleted_at)
    VALUES (:OLD.employee_id, :OLD.last_name, SYSTIMESTAMP);
END;
/

3. Using :NEW in a Statement-Level Trigger

Statement-level triggers (without FOR EACH ROW) do not operate on individual rows, so neither :NEW nor :OLD pseudo-records are available.

-- WRONG: Using :NEW in a statement-level trigger
CREATE OR REPLACE TRIGGER trg_emp_stmt_wrong
BEFORE INSERT ON employees
-- Missing FOR EACH ROW!
BEGIN
    :NEW.created_at := SYSDATE;  -- Error: no row context available
END;
/

-- CORRECT Option 1: Add FOR EACH ROW for row-level operations
CREATE OR REPLACE TRIGGER trg_emp_stmt_fixed
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.created_at := SYSDATE;
    :NEW.created_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
END;
/

-- CORRECT Option 2: Remove :NEW for statement-level logic
CREATE OR REPLACE TRIGGER trg_emp_stmt_audit
AFTER INSERT ON employees
BEGIN
    INSERT INTO audit_log (table_name, action, action_time)
    VALUES ('EMPLOYEES', 'INSERT', SYSTIMESTAMP);
END;
/

---

Quick Fix Solutions

Use the following reference matrix before writing any trigger:

/*
Trigger Type     | :NEW Read | :NEW Write | :OLD Read | :OLD Write
-----------------|-----------|------------|-----------|------------
BEFORE INSERT    |    YES    |    YES     |   NO      |    NO
AFTER  INSERT    |    YES    |    NO      |   NO      |    NO
BEFORE UPDATE    |    YES    |    YES     |   YES     |    NO
AFTER  UPDATE    |    YES    |    NO      |   YES     |    NO
BEFORE DELETE    |    NO     |    NO      |   YES     |    NO
AFTER  DELETE    |    NO     |    NO      |   YES     |    NO
*/

-- Check for invalid triggers after fixing
SELECT trigger_name, status
FROM   user_triggers
WHERE  table_name = 'EMPLOYEES';

-- Recompile a specific trigger
ALTER TRIGGER trg_emp_before COMPILE;

-- Find all trigger compilation errors
SELECT name, line, text
FROM   user_errors
WHERE  type = 'TRIGGER';

---

Prevention Tips

1. Always consult the :NEW/:OLD access matrix before writing a trigger. Make it a mandatory checklist item during code reviews. If your intent is to modify data before it is saved, always use BEFORE INSERT OR UPDATE triggers — never AFTER triggers.

2. Automate trigger validation in your CI/CD pipeline. Add a post-deployment step that queries user_errors for any TRIGGER type errors and fails the build if any are found. This catches ORA-04084 and similar issues before they reach production.

---

Related Errors

• ORA-04085: Cannot change :OLD values — :OLD is always read-only in all trigger types.
• ORA-04082: :NEW or :OLD referenced in a statement-level trigger without FOR EACH ROW.
• ORA-04091: Mutating table error — another common trigger design pitfall.
• ORA-04098: Trigger is invalid or failed re-validation, often a downstream effect of ORA-04084.

---

📖 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.