Assume I have the following table:
CREATE TABLE data ( key TEXT primary key, some_interesting_value TEXT, inserted TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), last_seen TIMESTAMP WITH TIME ZONE );
Then I can create a trigger to set the "updated" column like this:
CREATE TRIGGER set_updated_col AFTER UPDATE ON data FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE set_updated_timestamp();
last_seen column in this case contains a timestamp which is updated every time a process is run (with some conditions). However, this value is not interesting to track table updates. In my case I have many such tables. They are all contain information about physical network devices, and the "last_seen" value is important for each entity.
The trigger above will set the "updated" timestamp even if only the "last_seen" value is modified.
In my case I want to avoid this.
I could manually name all columns in the
WHEN clause, but then, if I add a column to the table, there is a risk that this gets forgotten in he trigger.
Is there a way to "remove" the
last_seen column from
As code example:
-- This should NOT trigger a change to `updated` UPDATE data SET last_seen=NOW() WHERE key='foobar'; -- this SHOULD trigger a change to `updated` UPDATE data SET some_interesting_value='foo', last_seen=NOW() WHERE key='foobar'