Search code examples
postgresqltriggersddl

Postgres trigger to only execute procedure when NEW.column = true

Using PostgreSQL - I'm trying to fire a procedure that runs a trigger only when the NEW (inserted or updated) row has the is_default column value set to boolean true.

This is what I've tried but I get a syntax error on my WHEN line:

CREATE TRIGGER trigger_price_group_default_price_handler
    BEFORE UPDATE OR INSERT
    ON price_groups
    WHEN (NEW.is_default = true)
    EXECUTE PROCEDURE clear_default_price_group();

Any ideas?

Solution

  • The most likely reason for apparently inexpliciable syntax errors in what looks like correct code is that the server version you're using doesn't understand the syntax.

    For example, conditional triggers were introduced in PostgreSQL 9.0, and are not present in 8.4 or older so an 8.4 or older server will not have any idea what WHEN ... means there.

    I've tried to make the argument on the pgsql-docs mailing list that we should include "Since [version]" alongside feature descriptions, but everyone just seems to prefer to direct people to read the docs for their version. So I haven't got far. At least PostgreSQL has quick links to other versions' docs, unlike too many projects (cough Hibernate cough).

    (This also means that you're on an old and unsupported PostgreSQL version and should upgrade).