PostgreSQL - trigger after update column-CodePudding

I try to create a generic trigger function which is used to update "updated_at" column value from table when it is updating it.

My trigger function:

CREATE OR REPLACE FUNCTION update_created_at_column() RETURNS TRIGGER AS $$
BEGIN
    NEW.created_at = NOW();
    return NEW;
END;
$$ LANGUAGE plpgsql;

Trigger assignment to the concerned table:

CREATE TRIGGER type_trigger_updated_at_column AFTER UPDATE ON "type"
FOR EACH ROW EXECUTE FUNCTION update_created_at_column();

Currently, I have no error when I execute them, but when I update a field on my table, "created_at" column is not updated.

CodePudding user response：

The value can be changed only in BEFORE triggers.

The Postgres knows more types of triggers - BEFORE triggers are executed before the value is stored to table, AFTER triggers are executed when the value is visible in table. You should to use BEFORE trigger - you need to modify row before it is stored to the table.