Oracle ORA-04070 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04070: Invalid Trigger Name — Causes, Fixes, and Prevention

ORA-04070 is an Oracle database error that occurs when a trigger name violates Oracle's naming conventions or references a non-existent trigger object. This error typically surfaces during CREATE, ALTER, or DROP TRIGGER operations and can halt deployments if not addressed promptly. Understanding the root causes and applying the right fix will save significant debugging time.

Top 3 Causes and Fixes

Cause 1: Trigger Name Violates Oracle Naming Rules

Oracle trigger names must start with a letter, contain only letters, digits, or underscores (_), and must not exceed 30 characters (pre-12.2) or 128 characters (12.2+). Using a name that starts with a number, contains spaces, or includes special characters like hyphens will immediately trigger this error.

-- ❌ Invalid: starts with a number
CREATE OR REPLACE TRIGGER 1st_emp_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.created_date := SYSDATE;
END;
/

-- ✅ Valid: starts with a letter, uses underscores
CREATE OR REPLACE TRIGGER trg_emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.created_date := SYSDATE;
END;
/

Cause 2: Using an Oracle Reserved Word as the Trigger Name

Oracle reserved words like TRIGGER, TABLE, SELECT, or INDEX cannot be used as object names without double-quoting, and even then it is strongly discouraged. Always verify your intended name against Oracle's reserved word list before using it.

-- Check if a word is reserved
SELECT keyword, reserved
FROM v$reserved_words
WHERE keyword = UPPER('your_trigger_name');

-- ❌ Invalid: using a reserved word
CREATE OR REPLACE TRIGGER trigger
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  NULL;
END;
/

-- ✅ Valid: descriptive, non-reserved name
CREATE OR REPLACE TRIGGER trg_emp_delete_log
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO emp_audit (emp_id, action, log_date)
  VALUES (:OLD.employee_id, 'DELETE', SYSDATE);
END;
/

Cause 3: Referencing a Non-Existent Trigger in ALTER or DROP

Attempting to ALTER or DROP a trigger that does not exist in the target schema is a common cause of this error, especially when running the same script across multiple environments (dev, staging, prod).

-- Check if the trigger exists before dropping
SELECT trigger_name, status
FROM user_triggers
WHERE trigger_name = 'TRG_EMP_BEFORE_INSERT';  -- Always use UPPERCASE

-- Safe conditional DROP using PL/SQL
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM user_triggers
  WHERE trigger_name = 'TRG_EMP_BEFORE_INSERT';

  IF v_count > 0 THEN
    EXECUTE IMMEDIATE 'DROP TRIGGER trg_emp_before_insert';
    DBMS_OUTPUT.PUT_LINE('Trigger dropped successfully.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Trigger does not exist. Skipping DROP.');
  END IF;
END;
/

-- Enable or disable a trigger safely
ALTER TRIGGER trg_emp_before_insert ENABLE;
ALTER TRIGGER trg_emp_before_insert DISABLE;

Quick Fix Checklist

✅ Ensure the trigger name starts with a letter (A–Z).
✅ Use only letters, numbers, and underscores (_).
✅ Keep the name under 30 characters for compatibility (128 max in 12.2+).
✅ Avoid Oracle reserved words — check V$RESERVED_WORDS.
✅ Always verify existence in USER_TRIGGERS or ALL_TRIGGERS before ALTER/DROP.
✅ Use UPPER() when querying data dictionary views — names are stored in uppercase.

Prevention Tips

1. Enforce a Standard Naming Convention
Adopt a consistent pattern such as TRG_[TABLE]_[EVENT] (e.g., TRG_EMPLOYEES_BEFORE_INSERT) across your team. Document this standard and incorporate DDL linting into your CI/CD pipeline to catch violations before deployment.

2. Pre-Deployment Validation Script
Always run a pre-check script before deploying trigger DDL to any environment. This eliminates surprises caused by missing objects or stale references.

-- Pre-deployment trigger audit
SELECT trigger_name,
       table_name,
       trigger_type,
       triggering_event,
       status
FROM user_triggers
ORDER BY table_name, trigger_name;

Related Oracle Errors

Error Code	Description
ORA-04071	Missing BEFORE, AFTER, or INSTEAD OF keyword
ORA-04072	Invalid trigger type specified
ORA-04080	Trigger name does not exist
ORA-04098	Trigger is invalid and failed re-validation
ORA-00900	Invalid SQL statement in trigger body

📖 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