Creating a trigger that sets a timestamp on UPDATE

user_13944528 0
Viewed 699

I have a table that has 3 columns. One is a record, one is created_time, 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.

triggers postgresql
1 Answers

You don't update the table. Rather, you write a BEFORE trigger that modifies the row that is about to be inserted 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();
user_64643080
Related