CODEWITHSUNDEEP
postgresql
vkatsou
vkatsou

Reputation: 83

Trigger to update a count number based on another table records

I have these two tables Review & Listing

| listing_id |          review_id         |       comment      |
|------------|----------------------------|--------------------|
|  5629709   |              123           |      Beautiful     |
|  4156372   |              231           |      Wonderful     |
|  4156372   |              432           |      Very Good     |
|  4156372   |              649           |      Excellent     |



| listing_id |      number_of_reviews     |
|------------|----------------------------|
|  5629709   |               1            |
|  4156372   |               2            |

Is there a way to create an trigger function that when the Review table has an update (insert or delete) then the number_of_reviews column in the Listing table updates also(+1 or -1)?

Upvotes: 0

Views: 551

Answers (2)

vkatsou
vkatsou

Reputation: 83

CREATE OR REPLACE FUNCTION function_number_of_reviews() RETURNS TRIGGER AS
$BODY$
BEGIN
    if TG_OP='INSERT' then
       Update public."Listing" set number_of_reviews = number_of_reviews + 1 where id = new.listing_id;
    end if;
    if TG_OP='DELETE' then
        Update public."Listing" set number_of_reviews = number_of_reviews - 1 where id = old.listing_id;
    end if;
    RETURN new;
END;
$BODY$
language plpgsql;

CREATE TRIGGER trig_number_of_reviews
     AFTER INSERT OR DELETE ON public."Review"
     FOR EACH ROW
     EXECUTE PROCEDURE function_number_of_reviews();

This is the wright way

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Materializing values that can be calculated from other values bears a severe risk for inconsistencies. If possible avoid that.

In your case drop the number_of_reviews column in listing and create a view calculating the numbers for that column instead.

CREATE VIEW listing_with_number_of_reviews
AS
SELECT l.listing_id,
       count(r.review_id) number_of_reviews
       FROM listing l
            LEFT JOIN review r
                      ON r.listing_id = l.listing_id
       GROUP BY l.listing_id;

Upvotes: 0

Related Questions