CODEWITHSUNDEEP
mysqlsql
MasAdam
MasAdam

Reputation: 453

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!

Upvotes: 2

Views: 5677

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522471

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;

Upvotes: 5

Related Questions