PostgreSQL trigger after update only on a updated row

Question

I have a small table for news. I want to make a trigger which sets the update date and update time in the row (only for the rows that were updated)

I tried making the following:

CREATE FUNCTION add_update_dates()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF (OLD.news_name IS DISTINCT FROM NEW.news_name OR
  OLD.news_description IS DISTINCT FROM NEW.news_description OR
  OLD.news_text IS DISTINCT FROM NEW.news_text) THEN
  UPDATE news SET news_update_date = current_date, news_update_time = current_time;
END IF;
RETURN new;
END
$$;

CREATE TRIGGER update_news_dates
AFTER UPDATE ON news
FOR EACH ROW
EXECUTE PROCEDURE add_update_dates();

But the trigger updates each row in my table (even those that are not updated), when I want only the updated ones. What am I doing wrong?

Is the update table statement on same table, inside the trigger, allowed in Postgresql?. I mean, In oracle, we would have received a mutating table error. — Kaushik Nayak
– Kaushik Nayak, Commented Dec 11, 2017 at 18:02
@KaushikNayak: I think it's allowed in an AFTER UPDATE trigger, but not in a BEFORE UPDATE trigger — Nick Barnes
– Nick Barnes, Commented Dec 11, 2017 at 21:29

Gordon Linoff · Accepted Answer · 2017-12-11 17:58:38Z

9

Your update statement is updating all the rows in the table! It has no where clause.

Just use assignment:

CREATE FUNCTION add_update_dates()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF (OLD.news_name IS DISTINCT FROM NEW.news_name OR
      OLD.news_description IS DISTINCT FROM NEW.news_description OR
      OLD.news_text IS DISTINCT FROM NEW.news_text
     ) THEN
    NEW.news_update_date := current_date;
    NEW.news_update_time := current_time;
  END IF;
  RETURN new;
END;
$$;

As an aside, storing date/time in separate columns makes no sense to me.

answered Dec 11, 2017 at 17:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Nick Barnes Over a year ago

You'll also need to change the trigger definition to BEFORE UPDATE

Collectives™ on Stack Overflow

PostgreSQL trigger after update only on a updated row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related