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();
The 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 OLD.*
?
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'