PostgreSQL Triggered Action Exception (SQLSTATE 09000)

When a trigger function raises an error, PostgreSQL reports it with SQLSTATE 09000 and condition name triggered_action_exception. In psql or application logs, you will typically see a message like ERROR: <your message> followed by a context line such as CONTEXT: PL/pgSQL function my_trigger_fn() line 12 at RAISE. The root error originates inside the trigger body, and the 09000 code tells you that the failure path ran through a trigger rather than directly from the calling statement.

What This Error Means

SQLSTATE 09000 belongs to PostgreSQL error class 09 — "Triggered Action Exception." This class has only one condition: triggered_action_exception. It is the catch-all code for any unhandled error that surfaces while a trigger function is executing, whether that error comes from an explicit RAISE EXCEPTION statement written by the developer or from a secondary SQL error (a constraint violation, a division by zero, etc.) that the trigger function did not catch itself.

When a trigger fires, it runs inside the same transaction as the statement that caused it. If the trigger function raises an unhandled exception, the entire statement — and by extension the current transaction — is aborted. PostgreSQL marks the transaction as error state, and no further SQL can succeed until the caller issues a ROLLBACK (or ROLLBACK TO SAVEPOINT if a savepoint was set before the triggering statement). This is standard PostgreSQL error-handling behavior; SQLSTATE 09000 does not introduce any special connection state.

Because triggers can fire for INSERT, UPDATE, DELETE, and TRUNCATE on tables and views, and can be BEFORE, AFTER, or INSTEAD OF, the same 09000 code can surface from very different call paths. The CONTEXT field in the error detail is the most reliable way to identify exactly which trigger and which line caused the problem.

Common Causes

1. Explicit RAISE EXCEPTION in the trigger function. The most common cause: a developer added business-rule enforcement inside a trigger. For example, a trigger that prevents negative account balances may call RAISE EXCEPTION 'Balance cannot go negative for account %', NEW.account_id.

2. An unhandled error inside the trigger body. The trigger itself issues SQL (e.g., INSERT INTO audit_log ...) that fails — perhaps due to a NOT NULL constraint violation, a foreign key violation, or a unique_violation — and the function has no EXCEPTION block to catch it. PostgreSQL propagates the inner error as 09000.

3. A RAISE with no message in the wrong context. A bare RAISE (re-raise) in a PL/pgSQL EXCEPTION block re-throws the current exception. If this reaches the trigger boundary without being caught, it surfaces as 09000.

4. Calling RAISE EXCEPTION from a nested function invoked by the trigger. If the trigger calls a helper PL/pgSQL function that raises an exception, and neither the helper nor the trigger catches it, the exception propagates out as 09000 from the trigger context.

5. ASSERT statements failing during development. PL/pgSQL ASSERT condition, 'message' raises assert_failure (SQLSTATE P0004). If the trigger does not catch it, PostgreSQL wraps it in the trigger context and the client may see 09000 depending on the driver.

How to Fix triggered_action_exception

1. Read the full error context. The error message itself contains the developer-defined message. The CONTEXT field names the function, the line number, and the statement type. Always read the full error output before proceeding:

   -- psql displays context automatically; in application code, inspect
   -- the error's 'context' field (e.g., PG::Error#pg_result context in Ruby,
   -- or sqlalchemy.exc.DatabaseError args in Python).
   

2. Find the trigger definition and read the function body. Use \df+ trigger_function_name in psql, or query the catalog:

   SELECT prosrc
   FROM pg_proc
   WHERE proname = 'my_trigger_fn';
   

   Read the function logic to understand under what conditions it raises. The RAISE call — or the SQL statement that failed — will be near the line number shown in the context.

3. Fix the business-rule violation. If the trigger raised because input data violates an intentional rule (e.g., negative balance, invalid state transition), fix the data or the application logic sending that data. The trigger is working as designed.

4. Add an EXCEPTION block if the inner error should be handled gracefully. If an incidental error (e.g., a duplicate key in an audit log) is triggering the exception, catch it inside the trigger function:

   CREATE OR REPLACE FUNCTION my_trigger_fn()
   RETURNS TRIGGER LANGUAGE plpgsql AS $$
   BEGIN
     BEGIN
       INSERT INTO audit_log(table_name, row_id, action)
       VALUES (TG_TABLE_NAME, NEW.id, TG_OP);
     EXCEPTION
       WHEN unique_violation THEN
         -- audit row already exists; ignore
         NULL;
     END;
     RETURN NEW;
   END;
   $$;
   

5. Use RAISE WARNING or RAISE NOTICE instead of RAISE EXCEPTION for non-fatal conditions. If the intent is to log a warning rather than abort the transaction, change the severity:

   RAISE WARNING 'Unexpected state for row %: %', NEW.id, NEW.status;
   -- execution continues; no exception is thrown
   

6. Use a savepoint to isolate trigger failures. If you cannot change the trigger but need to attempt an operation that might fail, wrap it in a savepoint:

   SAVEPOINT before_risky_insert;
   INSERT INTO orders (...) VALUES (...);
   -- if trigger raises, catch in application and:
   ROLLBACK TO SAVEPOINT before_risky_insert;
   

Additional Information

• SQLSTATE class 09 contains only 09000; there are no sub-conditions defined by the SQL standard or by PostgreSQL for this class.
• Related SQLSTATE codes from other trigger-adjacent error classes: P0001 (raise_exception) is what PL/pgSQL assigns to an explicit RAISE EXCEPTION when no custom SQLSTATE is given — clients may see either P0001 or 09000 depending on whether the exception is caught and re-raised at the trigger boundary.
• When using a custom SQLSTATE in RAISE EXCEPTION USING ERRCODE = '23505', the trigger propagates that SQLSTATE rather than 09000. The 09000 code appears only when no more specific SQLSTATE is set.
• Most PostgreSQL drivers (libpq, psycopg2, asyncpg, pgx, node-postgres) expose the SQLSTATE in the exception object. Look for .pgcode, .sqlstate, or .code fields to detect 09000 programmatically.
• ORM behavior: ActiveRecord (Rails), SQLAlchemy, and Hibernate typically wrap this as a generic database error and surface the PostgreSQL message string. Check the .orig or .cause attribute to extract the underlying SQLSTATE.
• Trigger exceptions always abort the current statement. If the trigger is an AFTER trigger on a table with deferred constraints, the exception still aborts immediately — deferred constraint checking and trigger execution are separate mechanisms.

Frequently Asked Questions

Why does my application see a different error code (P0001) instead of 09000?

P0001 (raise_exception) is the SQLSTATE that PL/pgSQL assigns to an explicit RAISE EXCEPTION statement with no custom ERRCODE. Whether your client sees P0001 or 09000 depends on how the exception propagates. If the trigger function itself raises with RAISE EXCEPTION 'msg', clients typically receive P0001. SQLSTATE 09000 more commonly appears when a non-PL/pgSQL error (e.g., a constraint violation) is uncaught inside the trigger and PostgreSQL re-reports it as a triggered action exception. Check the full error detail including the CONTEXT field — it identifies the trigger regardless of the SQLSTATE.

How do I find out which trigger fired and caused the error?

Look at the CONTEXT field of the error. PostgreSQL includes a stack trace showing each PL/pgSQL function name, the line number, and the type of statement. For example: CONTEXT: PL/pgSQL function enforce_balance_trigger() line 8 at RAISE. If multiple triggers are defined on the table, the context will show the one that failed. You can list all triggers on a table with SELECT * FROM information_schema.triggers WHERE event_object_table = 'your_table'.

Can I catch triggered_action_exception in a PL/pgSQL EXCEPTION block?

Yes. Use WHEN triggered_action_exception THEN or the SQLSTATE form WHEN SQLSTATE '09000' THEN in an outer PL/pgSQL block. However, catching it at the trigger level requires wrapping your trigger body logic in an inner BEGIN ... EXCEPTION ... END block, since the outer function boundary already constitutes the trigger execution context.

Does a BEFORE trigger raising an exception behave differently from an AFTER trigger?

Both abort the statement and mark the transaction as failed. The practical difference is timing: a BEFORE trigger raising an exception prevents the DML statement from ever modifying rows. An AFTER trigger raising an exception fires after the rows are tentatively written but before the statement completes — PostgreSQL rolls back those row changes as part of the statement-level abort. Either way, the result is the same from the client's perspective: the statement fails with 09000 (or the inner SQLSTATE), and the transaction must be rolled back.