CODEWITHSUNDEEP
sqlpostgresqltriggersplpgsql
Yuri Sosnov
Yuri Sosnov

Reputation: 43

PostgreSQL trigger after update only on a updated row

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?

Upvotes: 4

Views: 8856

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

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.

Upvotes: 9

Related Questions