MasAdam MasAdam - 6 months ago 29
MySQL Question

SQL Trigger for Average

I have a question regarding trigger.

Let's say I have two tables, one called 'studentData' and the other 'studentStanding'.


'studentData' is a table consist of teacherID, studentID and rating.
So different teacher may give different rating to a single student


(ex: teacherID: 1 gives rating: 5 to studentID: 3 AND teacherID: 7 gives rating: 10 to studentID: 3)


studentStanding is table consist of studentID, studentName and averageRating.


My intention is to create a TRIGGER;

Every time a new teacher (example teacherID: 120) gives rating to studentID: 3,
this TRIGGER will recalculate the average Rating of this student 3.

I had tried to write the trigger but I'm pretty sure it is completely wrong.
Here's how it looks:

CREATE TRIGGER updateAVG
AFTER INSERT ON studentData
REFERENCING NEW AS N_ROW
FOR EACH ROW
UPDATE studentStanding SET Rating = (
Select AVG(Rating)
from studentData
)


I'm looking forward to anybody who could help me. Feel free to put suggestions on my trigger too. Thank you.

Note: This is NOT a school assignment, I promise!

Answer
CREATE TRIGGER computeAvg
AFTER INSERT ON studentData
FOR EACH ROW
    UPDATE studentStanding
    SET averageRating = (SELECT AVG(rating) from studentData where studentStanding.studentID=studentData.studentID)
    WHERE studentID = NEW.studentID;
Comments