Update counter trigger PostgreSql

Question

I have a table like this

-----------------------------------------------------------------------------------
id                                    | notification_sent | times_notification_sent
-----------------------------------------------------------------------------------
4a1717a2-6e1e-4af6-aa34-8262899aa060  | t                 | 0

Here notification sent is boolean field and times_notification_sent is int. I am trying to create trigger which will increment the times_notification_sent by 1 when notification_sent is changed from true to false.

I am using following function and triggers but it not working.

CREATE OR REPLACE FUNCTION update_sent_counter_for_watch()  RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$ BEGIN
IF OLD.notification_sent IS TRUE AND NEW.notification_sent IS FALSE THEN
   UPDATE "watch" SET "times_notification_sent" = "times_notification_sent" + 1 WHERE "id" = OLD."id";
END IF;
END;
$$;

CREATE TRIGGER "update_times_sent_counter" AFTER UPDATE OF "times_notification_sent" ON "public"."watch"
FOR EACH ROW
WHEN (OLD.notification_sent IS DISTINCT FROM NEW.notification_sent)
EXECUTE PROCEDURE "public"."update_sent_counter_for_watch"();

score 1 · Accepted Answer · 2016-03-09 06:39:10Z

1

You have two errors:

You don't need an update, just assign the new value:

CREATE OR REPLACE FUNCTION update_sent_counter_for_watch()  
  RETURNS TRIGGER 
  LANGUAGE plpgsql
AS 
$$ 
BEGIN
  IF OLD.notification_sent IS TRUE AND NEW.notification_sent IS FALSE THEN
    new.times_notification_sent := old.times_notification_sent + 1;
  END IF;
  RETURN new;
END;
$$;

And you can't change the value in an after update triger, you need to change it to a before trigger:

CREATE TRIGGER "update_times_sent_counter" BEFORE UPDATE OF "times_notification_sent" 
  ON "public"."watch"
  FOR EACH ROW
WHEN (OLD.notification_sent IS DISTINCT FROM NEW.notification_sent)
EXECUTE PROCEDURE "public"."update_sent_counter_for_watch"();

edited Mar 9, 2016 at 6:39

answered Mar 8, 2016 at 22:39

user330315

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

1 Comment

nicholasnet Over a year ago

Both of you were correct. Thank you very much for your help.

Collectives™ on Stack Overflow

Update counter trigger PostgreSql

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