CODEWITHSUNDEEP
postgresqltriggers
Arnav Malviya
Arnav Malviya

Reputation: 139

Creating a trigger that sets a timestamp on UPDATE

I have a table that has 3 columns. One is a record, one is created_at, and the last one is updated_at. I want to create a stored procedure that changes updated_at to NOW() when a record is UPDATED.

Each row also has an id called id.

I don't have any initial code to show as I am slightly confused on how triggers work. I understand that my trigger would be an update or record, but how do I tell the table to update its corresponding updated_at value to NOW().

I created my table as such:

CREATE TABLE keyvalue ( 
  id id,
  record VARCHAR(128) UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY(id)
);

Any help would be appreciated.

Upvotes: 6

Views: 4828

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

You don't update the table. Rather, you write a BEFORE trigger that modifies the row that is about to be updated by changing and returning the NEW variable.

CREATE FUNCTION upd_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   NEW.updated_at := current_timestamp;
   RETURN NEW;
END;$$;

CREATE TRIGGER upd_trig BEFORE UPDATE ON keyvalue
   FOR EACH ROW EXECUTE PROCEDURE upd_trig();

Upvotes: 7

Related Questions