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();
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 thecondition
returnstrue
. InFOR EACH ROW
triggers, theWHEN
condition can refer to columns of the old and/or new row values by writingOLD.
column_name
orNEW.
column_name
respectively. Of course,INSERT
triggers cannot refer toOLD
andDELETE
triggers cannot refer toNEW
.
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.