CODEWITHSUNDEEP
phpmysql
MasAdam
MasAdam

Reputation: 453

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 trigger 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.

Upvotes: 0

Views: 71

Answers (1)

wchiquito
wchiquito

Reputation: 16551

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 ;

SQL Fiddle demo

Upvotes: 1

Related Questions