Error and Transaction Handling in Triggers: EXCEPTION, RAISE

Sometimes triggers act weird, and that might be because of:

• A logic bug in the function attached to the trigger.
• Breaking database constraints (like unique violations or data type mismatches).
• Transaction issues, where the trigger causes a rollback because of an error.
• Recursion, if the trigger calls itself (usually by accident).

To avoid these problems, PostgreSQL lets you handle errors inside triggers and their functions. The tools for this are EXCEPTION blocks and the RAISE statement, which we'll break down with examples today.

Error Handling with the EXCEPTION Block

The EXCEPTION block lets you catch errors and run some code to handle them. It's kinda like using try-catch in programming languages like Python or Java.

You use the EXCEPTION block in PL/pgSQL functions like this:

BEGIN
    -- Main function code
EXCEPTION
    WHEN <error_type> THEN
        -- Error handling code
END;

Where <error_type> is the specific error or group of errors you want to handle (like unique_violation, division_by_zero, etc.).

Example: Logging Errors in Triggers

Imagine we have a logs table where we want to write errors that happen when inserting data into the students table. Here's an example:

Create a table for logs

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    error_message TEXT
);

Create a function with error handling

CREATE OR REPLACE FUNCTION track_insert_errors()
RETURNS TRIGGER AS $$
BEGIN
    -- Try to run the main code
    BEGIN
        -- Example of a "bad" action: division by 0
        PERFORM 1 / (NEW.some_value - NEW.some_value);
    EXCEPTION
        WHEN division_by_zero THEN
            -- If a division by zero error happens, log it
            INSERT INTO logs (error_message) VALUES ('Division by zero error when inserting into students');
    END;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Create a trigger

CREATE TRIGGER before_insert_students
BEFORE INSERT ON students
FOR EACH ROW
EXECUTE FUNCTION track_insert_errors();

Now, if a division by zero error happens when inserting into students, it'll get handled and info about it will be written to the logs table.

Using RAISE for Diagnostics and Debugging

The RAISE statement lets you output messages about warnings, errors, or debug info. It's super useful when you're trying to figure out how your trigger works (or why it doesn't!).

Types of RAISE messages:

1. DEBUG — debug message.
2. NOTICE — regular info message.
3. WARNING — warning.
4. EXCEPTION — error message that stops the function.

RAISE syntax:

RAISE <message_type> 'Message';

You can also pass variable values:

RAISE NOTICE 'Value of NEW.id = %', NEW.id;

Example: Debugging Values in a Trigger

Let's say you get an error when updating the students table, and you want to know what NEW and OLD values are causing the problem. Use RAISE for that:

CREATE OR REPLACE FUNCTION debug_student_update()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'OLD.id = %, NEW.id = %', OLD.id, NEW.id;

    -- Example of a condition that throws an error:
    IF NEW.some_field IS NULL THEN
        RAISE EXCEPTION 'Field some_field cannot be NULL';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_update_students
AFTER UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION debug_student_update();

Now every time you update a record, you'll see the OLD and NEW values, and you'll get a clear error message if something goes wrong.

Transactions in Triggers

Triggers run inside a transaction. That means if an error happens anywhere inside the trigger or its function, the whole transaction gets rolled back. This nicely protects your database from partial changes.

But sometimes this behavior causes headaches:

• If the error inside the trigger is about bad data, it might be nice to roll back just part of the actions.
• You gotta remember that rolling back the transaction includes not just the trigger, but the whole operation that fired the trigger.

Example: Using Transactions in a Trigger

To show this, imagine we want to do some business logic that includes two things: updating the students table and writing a log to logs. If either of these fails, the whole transaction rolls back.

CREATE OR REPLACE FUNCTION transactional_student_update()
RETURNS TRIGGER AS $$
BEGIN
    -- Log the update attempt
    INSERT INTO logs (error_message) VALUES ('Trying to update student with id ' || NEW.id);

    -- Check business rules
    IF NEW.some_value IS NULL THEN
        RAISE EXCEPTION 'Field some_value cannot be NULL';
    END IF;

    -- If all is good, return NEW
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_update_students
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION transactional_student_update();

Common Trigger Mistakes and How to Avoid Them

Common developer mistakes:

Recursive triggers. This happens when a trigger makes changes that fire itself again. How to fix: use a WHEN condition or add a flag to prevent repeated calls.

Rolling back the whole transaction because of errors. This is often not what you want if the trigger isn't directly tied to the main data. How to fix: use EXCEPTION blocks smartly.

Too much debug info. This clutters your logs and makes analysis harder. How to fix: use RAISE only during development and testing.

Performance drops. Heavy triggers can slow down INSERT, UPDATE, or DELETE operations. How to fix: keep trigger logic minimal and avoid heavy queries.