CODEWITHSUNDEEP
sql-servert-sqltriggers
泉泉 占柱
泉泉 占柱

Reputation: 133

Update average scores in a trigger?

I have student table and score table and subject table. Student table has 'student_id' and 'avg_score'. Subject table has 'subject_name' and 'subject_id'. Score has 'subject_id','student_id' and 'score' as decimal. I wrote this trigger to calculate the avg score. How can I get exactly score of each student with each subject name and calculate the average score?

CREATE TRIGGER caculateAVG ON dbo.dbo_score
AFTER INSERT,UPDATE,DELETE AS
BEGIN
  DECLARE @Math DECIMAL;
  DECLARE @Eng DECIMAL;
  DECLARE @Liter DECIMAL;

  UPDATE x
  SET x.avgScore = (@Eng + @Liter + @Math)/3
  FROM dbo.dbo_student x
  INNER JOIN dbo.dbo_score c ON c.student_id = x.student_id
  INNER JOIN dbo.dbo_subject j ON j.subject_id = c.subject_id

  SET @Math = (SELECT s.score FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j ON 
  j.subject_id = s.subject_id WHERE UPPER(j.subject_name) LIKE 'math')

  SET @Eng = (SELECT s.score FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j ON 
  j.subject_id = s.subject_id WHERE UPPER(j.subject_name) LIKE 'eng')

  SET @Liter = (SELECT s.score FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j 
  ON j.subject_id = s.subject_id WHERE UPPER(j.subject_name) LIKE 'literature')
END

Upvotes: 0

Views: 416

Answers (1)

Dale K
Dale K

Reputation: 27364

When working with SQL Server (or any relational database for that matter) you want to try and solve all problems using set based logic. So we can carry out the update you are attempting in a single query as follows, where I expect you want to restrict the update to only those students affected by the update using the Inserted and Deleted pseudo-tables..

UPDATE x
  SET x.avgScore = (
    (SELECT sum(s.score) FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j ON j.subject_id = s.subject_id WHERE j.subject_name = 'eng')
    + (SELECT sum(s.score) FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j ON j.subject_id = s.subject_id WHERE j.subject_name = 'literature')
    + (SELECT sum(s.score) FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j ON j.subject_id = s.subject_id WHERE j.subject_name = 'math')
  )/3
FROM dbo.dbo_student x
where x.student_id in (select student_id from Inserted union all select student_id from Deleted)

Also there is no need to use like if you aren't using wildcards in your search term. And there is no need to use upper because SQL Server is generally case insensitive (and you are comparing it to a lowercase word, which would fail if it was case sensitive).

Upvotes: 1

Related Questions