postgresqltriggersddlboolean-expression

Trigger with multiple WHEN conditions


How do I include the columns I need to monitor? I.e. instead of one WHEN condition I want to have 3 WHEN conditions:

CREATE  TRIGGER freeradius.insert_into_day_summations 
     BEFORE INSERT ON freeradius.day_guiding_usage
     FOR EACH ROW 
     WHEN (OLD.col1 IS DISTINCT FROM NEW.col1)
     WHEN (OLD.col2 IS DISTINCT FROM NEW.col2)
     WHEN (OLD.col3 IS DISTINCT FROM NEW.col3)
EXECUTE procedure update_sessioninfo();

Solution

  • Form a single expression with OR or AND - depending on whether you want to trigger when all conditions are met or either one:

    CREATE TRIGGER update_day_summations  -- see below
    BEFORE UPDATE ON freeradius.day_guiding_usage
    FOR EACH ROW 
    WHEN (OLD.col1 IS DISTINCT FROM NEW.col1
       OR OLD.col2 IS DISTINCT FROM NEW.col2
       OR OLD.col3 IS DISTINCT FROM NEW.col3)
    EXECUTE FUNCTION update_sessioninfo();
    

    See:

    It's just a boolean expression, can involve all columns of the row.
    However, your expressions only make sense for UPDATE, not for INSERT. There is no OLD record for inserts. The manual on CREATE TRIGGER:

    condition

    A Boolean expression that determines whether the trigger function will actually be executed. If WHEN is specified, the function will only be called if the condition returns true. In FOR EACH ROW triggers, the WHEN condition can refer to columns of the old and/or new row values by writing OLD.column_name or NEW.column_name respectively. Of course, INSERT triggers cannot refer to OLD and DELETE triggers cannot refer to NEW.

    And the trigger name itself cannot be schema-qualified. Quoting the manual once more:

    name

    The name to give the new trigger. This must be distinct from the name of any other trigger for the same table. The name cannot be schema-qualified

    Bold emphasis mine.