Right, so you’ve built a trigger. It fires every single time its triggering event happens. That’s its job. But what if you only want it to sometimes do its job? Do you really want to write a trigger that fires on every UPDATE to a million-row table, only for its first line to be IF (new.is_interesting IS FALSE) THEN RETURN NULL; END IF;? That’s like accepting a delivery job where you have to drive to every house in the city just to read the instructions on the box to see if it’s actually for you. It’s wasteful.

Enter the WHEN clause. This is your trigger’s bouncer, its VIP filter. It’s a condition that is checked before the trigger function is even called. If the condition evaluates to false (or NULL, which is effectively the same here), the entire trigger execution is skipped. No function call, no overhead, no nothing. It’s efficient, and it keeps your logic cleanly separated: the condition for execution is in the trigger definition, and the action is in the function.

The Anatomy of a WHEN Condition

The WHEN condition isn’t just any old WHERE clause you’d write in a query. It’s a special expression that has access to the row’s data using the OLD and NEW aliases, just like inside the trigger function itself. The key difference is its context: it’s evaluated against the proposed row data before any logic runs.

Here’s the classic example. Let’s say you only want to send a notification email when a user’s email field is actually updated, not just any other field.

The clunky way (without WHEN):

CREATE OR REPLACE FUNCTION notify_on_email_change()
RETURNS trigger AS $$
BEGIN
    IF OLD.email <> NEW.email THEN
        -- Actually send the email or queue a task
        RAISE NOTICE 'We would send an email to %!', NEW.email;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_email_change_trigger
    AFTER UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION notify_on_email_change();

This works, but the function is called for every single update, wasting cycles.

The elegant way (with WHEN):

CREATE TRIGGER user_email_change_trigger
    AFTER UPDATE ON users
    FOR EACH ROW
    WHEN (OLD.email IS DISTINCT FROM NEW.email)
    EXECUTE FUNCTION notify_on_email_change();

Now, the function only executes when the email has genuinely changed. Notice the use of IS DISTINCT FROM instead of <>. This is a critical best practice. It correctly handles NULL values. If OLD.email is NULL and NEW.email is also NULL, <> would return NULL (skipping the trigger), but IS DISTINCT FROM would return false (also skipping the trigger, correctly). If one is NULL and the other isn’t, <> would return NULL (incorrectly skipping the trigger), while IS DISTINCT FROM returns true (correctly firing it). Always use IS DISTINCT FROM in your WHEN conditions for comparisons.

Row-Level vs. Statement-Level Nuances

This is where the designers’ choices become, let’s say, “idiosyncratic.” The WHEN clause is only available for row-level triggers (FOR EACH ROW). There is no built-in WHEN clause for statement-level triggers (FOR EACH STATEMENT).

Why? Because the WHEN condition is evaluated for each candidate row, and a statement-level trigger fires once per statement, regardless of how many rows are affected. There’s no single row to reference OLD or NEW from. This feels like a missing feature, and it is. The workaround is to push your condition logic into the trigger function itself and have it return early if no rows meet your criteria, which is far less efficient.

What You Can (and Cannot) Do Inside WHEN

The WHEN clause is powerful, but it’s not a full-blown SQL query. Its expression must be immutable and self-contained. You cannot:

  • Reference other tables (no subqueries, no joins).
  • Call volatile functions like SELECT NOW() or nextval().
  • Reference columns that aren’t from the OLD or NEW row.

You can, however:

  • Use almost any operator and built-in function (length(), upper(), etc.).
  • Build complex boolean logic with AND, OR, and NOT.
  • Use parentheses for grouping.

A Common Pitfall: The Immutability Requirement

Let’s say you try to be clever. You have a configuration table settings and you only want your trigger to fire if a certain setting is enabled.

-- THIS WILL FAIL TO CREATE.
CREATE TRIGGER my_trigger
    AFTER UPDATE ON my_table
    FOR EACH ROW
    WHEN ( (SELECT value FROM settings WHERE name = 'enable_trigger') = 'true' )
    EXECUTE FUNCTION my_trigger_function();

PostgreSQL will reject this immediately. The WHEN condition must be immutable; it can’t depend on data in other tables. The correct, albeit less elegant, way to handle this is to check the setting inside your trigger function. The WHEN clause is for conditions based on the row’s own data, not external state.

In summary, use the WHEN clause relentlessly for row-level triggers. It’s your first and best line of defense against unnecessary work. It makes your intention clear in the trigger declaration itself and keeps your trigger functions focused on the action, not the preconditions. Just remember its limitations: IS DISTINCT FROM is your friend, and it’s useless for statement-level triggers.