umzzil nng

Posted on Aug 25 Originally published at oraerror.com

Oracle ORA-04077 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04077: WHEN Clause Cannot Be Used with Statement Triggers

ORA-04077 is thrown by Oracle when you attempt to define a WHEN clause on a statement-level trigger — one that fires once per DML statement rather than once per affected row. The WHEN clause is exclusively reserved for row-level triggers declared with the FOR EACH ROW option, because only row-level triggers have access to the :NEW and :OLD pseudo-records that WHEN conditions evaluate against. This error is common during development or when migrating trigger logic from other database platforms.

Top 3 Causes

1. Using WHEN on a Statement-Level Trigger Directly

The most frequent cause: a developer adds a WHEN clause to filter conditions but forgets — or doesn't realize — that the trigger has no FOR EACH ROW clause.

-- ERROR: ORA-04077
CREATE OR REPLACE TRIGGER trg_emp_update
BEFORE UPDATE ON employees
WHEN (NEW.salary > 5000)   -- Invalid on statement trigger
BEGIN
    DBMS_OUTPUT.PUT_LINE('Salary update detected.');
END;
/

2. Accidentally Omitting FOR EACH ROW

Copying and adapting an existing trigger template can lead to FOR EACH ROW being dropped while the WHEN clause remains intact.

-- Incomplete trigger — FOR EACH ROW missing
CREATE OR REPLACE TRIGGER trg_audit_orders
AFTER INSERT ON orders
-- FOR EACH ROW  <-- accidentally removed
WHEN (NEW.total_amount > 1000)  -- ORA-04077
BEGIN
    INSERT INTO audit_log (order_id, logged_at)
    VALUES (:NEW.order_id, SYSDATE);
END;
/

3. Cross-DBMS Migration Without Syntax Adaptation

Trigger syntax from MySQL or SQL Server does not map directly to Oracle. Migrated scripts often contain WHEN-like conditional constructs applied at the statement level, which violates Oracle's strict rule.

-- Migrated script causing ORA-04077
CREATE OR REPLACE TRIGGER trg_stock_check
BEFORE DELETE ON inventory
WHEN (OLD.quantity = 0)   -- Statement trigger + WHEN = error
BEGIN
    DBMS_OUTPUT.PUT_LINE('Zero-quantity deletion attempted.');
END;
/

Quick Fix Solutions

Fix 1 — Add FOR EACH ROW to enable the WHEN clause:

CREATE OR REPLACE TRIGGER trg_emp_update
BEFORE UPDATE ON employees
FOR EACH ROW                        -- Add this line
WHEN (NEW.salary > 5000)            -- Now valid
BEGIN
    DBMS_OUTPUT.PUT_LINE('New salary: ' || :NEW.salary);
END;
/

Fix 2 — Remove WHEN and move the condition inside the trigger body:

CREATE OR REPLACE TRIGGER trg_emp_update
BEFORE UPDATE ON employees          -- Statement-level, no FOR EACH ROW
BEGIN
    -- Handle conditional logic inside the body instead
    DBMS_OUTPUT.PUT_LINE('Employee table updated.');
END;
/

Fix 3 — Full rewrite for row-level audit with WHEN:

CREATE OR REPLACE TRIGGER trg_audit_orders
AFTER INSERT ON orders
FOR EACH ROW
WHEN (NEW.total_amount > 1000)
BEGIN
    INSERT INTO audit_log (order_id, amount, logged_at)
    VALUES (:NEW.order_id, :NEW.total_amount, SYSDATE);
END;
/

Note: Inside the WHEN clause, reference columns as NEW.column (no colon). Inside the trigger body, use :NEW.column (with colon).

Verify Trigger Status

-- Check for INVALID triggers after deployment
SELECT trigger_name, status, trigger_type
FROM   user_triggers
WHERE  table_name = 'EMPLOYEES';

-- Recompile if needed
ALTER TRIGGER trg_emp_update COMPILE;

Prevention Tips

Enforce a trigger design checklist. Before writing any trigger, ask: "Do I need to evaluate per-row conditions?" If yes, always pair FOR EACH ROW with your WHEN clause. Document this rule in your team's coding standards and enforce it during code review.
Automate post-deployment validation. Add a verification step to your CI/CD pipeline that queries user_objects for INVALID triggers immediately after deployment. A trigger in INVALID state will cause runtime errors on the next DML operation, making early detection critical.

-- Add to deployment pipeline
SELECT object_name, status
FROM   user_objects
WHERE  object_type = 'TRIGGER'
AND    status = 'INVALID';

Related Errors

ORA-04076 — :NEW/:OLD references used inside a statement-level trigger body.
ORA-04079 — General invalid trigger specification due to unsupported option combinations.
ORA-04098 — Trigger is invalid and fails re-validation at runtime execution.

📖 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