PostgreSQL - trigger after update column

Question

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.

Pavel Stehule · Accepted Answer · 2022-09-06 17:58:51Z

2

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.

answered Sep 6, 2022 at 17:58

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - trigger after update column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related