-- dynamic sql doesn't work in store procedure or trigger.
drop trigger if exists item_review_after_insert;
DELIMITER // 
create trigger item_review_after_insert after insert on pro_item_review
    for each row begin
        if new.rating_score = 1 then
            update pro_item_revsum set score1_cnt=score1_cnt+1  where id = new.item_id;
        elseif new.rating_score = 2 then
            update pro_item_revsum set score2_cnt=score2_cnt+1  where id = new.item_id;
        elseif new.rating_score = 3 then
            update pro_item_revsum set score3_cnt=score3_cnt+1  where id = new.item_id;
        elseif new.rating_score = 4 then
            update pro_item_revsum set score4_cnt=score4_cnt+1  where id = new.item_id;
        elseif new.rating_score = 5 then
            update pro_item_revsum set score5_cnt=score5_cnt+1  where id = new.item_id;
        end if;
    end
//
DELIMITER ;