Oracle ORA-04076 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04076: Invalid NEW or OLD Specification

ORA-04076 is an Oracle error that occurs when :NEW or :OLD pseudo-records are used incorrectly inside a trigger. These special records are only valid in row-level triggers and must be used in the appropriate DML context. Understanding the rules governing their usage is essential for any developer writing Oracle triggers.

Top 3 Causes

1. Using :NEW or :OLD in a Statement-Level Trigger

The most common cause is forgetting the FOR EACH ROW clause, which makes a trigger row-level. Without it, Oracle has no concept of individual row values, so :NEW and :OLD are meaningless.

-- WRONG: Missing FOR EACH ROW → ORA-04076
CREATE OR REPLACE TRIGGER trg_bad_statement
BEFORE UPDATE ON employees
-- FOR EACH ROW is missing!
BEGIN
  IF :NEW.salary < 1000 THEN  -- ORA-04076 fires here
    RAISE_APPLICATION_ERROR(-20001, 'Salary too low.');
  END IF;
END;
/

-- CORRECT: Add FOR EACH ROW
CREATE OR REPLACE TRIGGER trg_good_row
BEFORE UPDATE ON employees
FOR EACH ROW  -- Required for :NEW/:OLD usage
BEGIN
  IF :NEW.salary < 1000 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary too low.');
  END IF;
END;
/

2. Referencing :NEW in DELETE or :OLD in INSERT Incorrectly

Oracle enforces strict rules about which pseudo-records are valid per DML event. In an INSERT trigger, :OLD is always NULL. In a DELETE trigger, :NEW is always NULL. Attempting to assign values to these in the wrong context triggers ORA-04076.

-- Correct usage per DML event type
-- INSERT: only :NEW is meaningful
CREATE OR REPLACE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('New Employee ID: ' || :NEW.employee_id);
  -- :OLD.employee_id is NULL here — don't use it
END;
/

-- DELETE: only :OLD is meaningful
CREATE OR REPLACE TRIGGER trg_after_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Deleted Employee ID: ' || :OLD.employee_id);
  -- :NEW.employee_id is NULL here — don't use it
END;
/

-- UPDATE: both :NEW and :OLD are valid
CREATE OR REPLACE TRIGGER trg_after_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Salary changed from ' || :OLD.salary || ' to ' || :NEW.salary);
END;
/

3. Modifying :NEW in an AFTER Trigger or Modifying :OLD Anywhere

:NEW values can only be modified in BEFORE triggers — this is the only way to intercept and change data before it's written. Trying to assign to :NEW in an AFTER trigger, or assigning to :OLD in any trigger (since it's read-only), will raise ORA-04076.

-- WRONG: Modifying :NEW in AFTER trigger → ORA-04076
CREATE OR REPLACE TRIGGER trg_bad_after
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  :NEW.salary := :NEW.salary * 1.1;  -- ERROR: cannot modify :NEW in AFTER trigger
END;
/

-- WRONG: Modifying :OLD (read-only) → ORA-04076
CREATE OR REPLACE TRIGGER trg_bad_old
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  :OLD.salary := 5000;  -- ERROR: :OLD is always read-only
END;
/

-- CORRECT: Modify :NEW only in a BEFORE trigger
CREATE OR REPLACE TRIGGER trg_good_before
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
  -- Cap salary increase at 2x the previous value
  IF :NEW.salary > :OLD.salary * 2 THEN
    :NEW.salary := :OLD.salary * 2;
  END IF;
END;
/

Quick Fix Solutions

Use the INSERTING, UPDATING, and DELETING predicates to safely handle multiple DML events in a single trigger without risking invalid pseudo-record access:

CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO audit_log VALUES ('INSERT', :NEW.employee_id, NULL, :NEW.salary, SYSDATE);
  ELSIF UPDATING THEN
    INSERT INTO audit_log VALUES ('UPDATE', :NEW.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
  ELSIF DELETING THEN
    INSERT INTO audit_log VALUES ('DELETE', :OLD.employee_id, :OLD.salary, NULL, SYSDATE);
  END IF;
END;
/

Prevention Tips

Always include FOR EACH ROW when using :NEW or :OLD. Make it a mandatory code review checkpoint.
Follow a naming convention such as TRG_[TABLE]_[BEFORE|AFTER]_[INS|UPD|DEL] so the trigger's context is immediately clear from its name (e.g., TRG_EMPLOYEES_BEFORE_UPD).
Use a quick reference table when writing triggers:

DML Event	`:OLD` Valid?	`:NEW` Valid?	`:NEW` Modifiable?
INSERT	No (NULL)	Yes	BEFORE only
UPDATE	Yes	Yes	BEFORE only
DELETE	Yes	No (NULL)	N/A

Related Errors

ORA-04077: WHEN clause not allowed for statement-level triggers
ORA-04079: Invalid trigger specification
ORA-04091: Mutating table error within a trigger
ORA-04098: Trigger is invalid and failed re-validation

📖 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