PostgreSQL 09000 Error: Causes and Solutions Complete Guide

PostgreSQL Error 09000: triggered action exception

PostgreSQL error code 09000 (triggered action exception) occurs when a trigger function raises an exception during execution, causing the entire triggering transaction to be rolled back. This can happen either through an explicit RAISE EXCEPTION call within the trigger or through a runtime error encountered during the trigger's logic. Because triggers fire automatically on DML operations, this error can be confusing to diagnose if the trigger's exception handling is poorly designed.

---

Top 3 Causes

1. Explicit RAISE EXCEPTION Inside a Trigger Function

The most common cause is an intentional RAISE EXCEPTION used to enforce business rules within a trigger.

-- Example: trigger function enforcing a business rule
CREATE OR REPLACE FUNCTION enforce_positive_salary()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative. Got: %', NEW.salary
            USING ERRCODE = 'P0001',
                  HINT = 'Provide a salary value >= 0.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_salary_check
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION enforce_positive_salary();

-- This INSERT will trigger the 09000 error:
INSERT INTO employees (name, salary) VALUES ('Alice', -500);

2. Runtime Errors Inside the Trigger Logic

Triggers can fail due to unhandled runtime errors such as division by zero, null dereference, or constraint violations triggered by the trigger's own DML.

-- Bad practice: no NULL/zero guard
CREATE OR REPLACE FUNCTION bad_ratio_trigger()
RETURNS TRIGGER AS $$
BEGIN
    -- Will throw division by zero if total_count = 0
    NEW.ratio := NEW.success_count / NEW.total_count;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Safe version with proper guard
CREATE OR REPLACE FUNCTION safe_ratio_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.total_count IS NULL OR NEW.total_count = 0 THEN
        NEW.ratio := 0;
    ELSE
        NEW.ratio := NEW.success_count::NUMERIC / NEW.total_count;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. Cascading Triggers Raising Exceptions

When a trigger performs DML on another table that also has triggers, exceptions in the downstream trigger propagate back and abort the original transaction.

-- Identify all triggers and their associated functions
SELECT
    t.tgname        AS trigger_name,
    c.relname       AS table_name,
    p.proname       AS function_name,
    t.tgenabled     AS enabled
FROM pg_trigger t
JOIN pg_class  c ON c.oid = t.tgrelid
JOIN pg_proc   p ON p.oid = t.tgfoid
WHERE NOT t.tgisinternal
ORDER BY c.relname, t.tgname;

-- Temporarily disable a trigger to isolate the problem
ALTER TABLE your_table DISABLE TRIGGER trigger_name;

-- Re-run the failing DML, then re-enable
ALTER TABLE your_table ENABLE TRIGGER trigger_name;

---

Quick Fix Solutions

Wrap risky logic in an EXCEPTION block to prevent the entire transaction from being aborted when a non-critical trigger (e.g., audit logging) fails:

CREATE OR REPLACE FUNCTION safe_audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    BEGIN
        INSERT INTO audit_log (table_name, operation, changed_at)
        VALUES (TG_TABLE_NAME, TG_OP, NOW());
    EXCEPTION
        WHEN OTHERS THEN
            RAISE WARNING 'Audit log failed: % %', SQLSTATE, SQLERRM;
    END;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Use RAISE NOTICE for debugging to trace which trigger is firing and what data it sees:

CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Trigger: %, Table: %, Op: %, NewRow: %',
        TG_NAME, TG_TABLE_NAME, TG_OP, row_to_json(NEW);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

---

Prevention Tips

1. Keep trigger functions simple and always include exception handling. Move complex business logic to application layers or stored procedures. Every trigger function should have an EXCEPTION WHEN OTHERS block, and use meaningful ERRCODE and HINT values in any RAISE EXCEPTION calls so application code can distinguish and handle errors cleanly.

2. Test triggers in staging before deploying to production, and monitor logs actively. Set log_min_messages = WARNING in postgresql.conf so that RAISE WARNING messages inside triggers are captured in server logs. Regularly review pg_stat_user_tables and application error logs to catch unexpected trigger exceptions before they impact end users.

---

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