triggered action exception
SQLSTATE 09000 is raised when a trigger function fails or raises an unhandled exception. The error aborts the triggering statement and rolls back any changes made by the trigger.
- 1A trigger function raises an unhandled exception using RAISE EXCEPTION
- 2A trigger function calls a function that errors out
- 3Business rule validation in a trigger detects a violation
An INSERT trigger enforcing a business rule rejects the row.
CREATE OR REPLACE FUNCTION check_balance() RETURNS TRIGGER AS $
BEGIN
IF NEW.balance < 0 THEN
RAISE EXCEPTION 'Balance cannot be negative';
END IF;
RETURN NEW;
END;
$ LANGUAGE plpgsql;expected output
ERROR: Balance cannot be negative
Fix 1
Handle the trigger exception in application code
WHEN When trigger rejections are expected business logic.
Why this works
Catch the error in the application, inspect the message, and present a user-friendly error without retrying the invalid operation.
Fix 2
Use RAISE EXCEPTION with a custom SQLSTATE for precise catching
WHEN When the trigger violation needs to be distinguishable from other errors.
RAISE EXCEPTION 'Balance cannot be negative' USING ERRCODE = 'P0001';
Why this works
A custom SQLSTATE lets the application layer catch trigger errors by code rather than by parsing the message string.
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev