Avatar
Kellie Schmidt
9057 reputation
Posted on:14 Sep '16 - 10:37
25

Trigger not updating

I have a trigger that looks like this:

CREATE TRIGGER findAvg
AFTER INSERT ON RATING
FOR EACH ROW
UPDATE `PROFILE`
        SET userScore = (SELECT AVG(rScore) from RATING where `PROFILE`.`pID`=RATING.raterID)
    WHERE pID = NEW.pID;
where

PROFILE table - pID, name, userScore

RATING table - raterID, rScore, rDescription
It works at the first time I implemented this trigger, however yesterday I dropped all the data in both Profile and Rating table. Thus, I inserted a "freshly new" data for profile and rating. Now everytime I insert a "RATING", the it won't update the userScore. So right now, I have some PROFILE that have "0" in userScore, even though in RATING table the value is "6" for the rScore. I'm confused because I'm pretty sure based on syntax, it's correct already. Help me please. Thank you.

SQL

Answers

50
This answer is accepted

Try:

DELIMITER //

CREATE TRIGGER `findavg` AFTER INSERT ON `rating`
FOR EACH ROW
BEGIN
  UPDATE `profile`
  SET `userscore` = (SELECT AVG(`rscore`)
                     FROM `rating`
                     WHERE `raterid` = `pid`)
-- WHERE `pid` = NEW.`pid`;
  WHERE `pid` = NEW.`raterid`;
END//

DELIMITER ;

Avatar
Thijs Reeringh
11977 reputation
Posted on:14 Sep '16 - 11:33

Please login in order to answer a question