AFTER UPDATE TRIGGER to keep Total Credits up to date

In my University database, I have Student, Takes and Course tables:

• Student (ID, Name, DepartmentName, TotalCredits)
• Takes (StudentId, CourseId, SectionId, Semester, Year, Grade)
• Course (CourseId, Title, DepartmentName, Credits)

I want to write a trigger to automatically update TotalCredits of a student in which gets an acceptable grade (when an instructor updates the grade of a course taken by a student).

I wrote this trigger and executed it:

CREATE TRIGGER [dbo].[credits_earned]  
ON [dbo].[Takes] 
AFTER UPDATE
AS
BEGIN
    IF UPDATE(grade)
    BEGIN
        DECLARE @new_grade varchar(2)

        SELECT @new_grade = I.grade
        FROM inserted I 

        DECLARE @old_grade varchar(2)

        SELECT @old_grade = T.grade
        FROM Takes T

        IF @new_grade<>'F' AND @new_grade IS NOT NULL
             AND (@old_grade = 'F' OR @old_grade IS NULL)
        BEGIN
            UPDATE Student
            SET tot_cred = tot_cred + 
                        (SELECT credits
                         FROM Course C
                         WHERE C.course_id = (SELECT I.course_id 
                                              FROM inserted I))
            WHERE Student.id = (SELECT I.id FROM inserted I)
        END
    END
END

And the commands completed successfully.

But when I execute a procedure to update grade of a course of a student (first grade is null in takes table), the trigger has no effect on TotalCredits of that student.

I would appreciate any answer you write.

This type of trigger is a very bad idea and should be avoided if at all possible, because it is prone to issues. If the change detection logic is ever wrong, you get wrong data. If there is ever an error in your trigger, you get out-of-sync data. Ideally you would always compute this information when you need it, e.g. with a view, rather than storing a calculated value.

However, if you have to use a trigger, when writing trigger logic, its still SQL, and therefore should still be set-based not procedure based (because SQL Server is optimised for set based operations).

Now I think the following works. (I am making an assumption on what uniquely identifies a Take in order to join Inserted onto Deleted (which is how you detect changes).

And then we need to pre-aggregate, because your aggregation would have added credits for all Takes regardless of whether the grade changed.

Also note I tend to invert the UPDATE check so its clear its an exit early option.

    IF NOT UPDATE(Grade) RETURN;

    UPDATE s SET
        tot_cred = tot_cred + c.Credits
    FROM Student s
    JOIN (
        SELECT i.StudentId, SUM(c.Credits) Credits
        FROM Inserted i
        JOIN Deleted d ON d.StudentId = i.StudentId
            AND d.CourseId = i.CourseId
            AND d.SectionId = i.SectionId
            AND d.Semester = i.Semester
            AND d.Year = i.Year
        JOIN Course c ON c.CourseId = i.CourseId
        WHERE i.Grade <> 'F' AND i.Grade IS NOT NULL
        AND (d.Grade = 'F' OR d.Grade IS NULL)
        GROUP BY i.StudentId
    ) c ON c.Student = s.Id;

Further, having your trigger update an incremental value is the more risky approach, because if the total is ever incorrect then it will stay incorrect. Its usually better to calculate the total from scratch each time, for example:

    UPDATE s SET
        tot_cred = (
            -- Whatever logic gives the accurate credits at a point in time
            SELECT SUM(c.Credits)
            FROM Takes t
            JOIN Courses c on c.CourseId = t.CourseId
            WHERE t.StudentId = s.Id
            AND t.Grade <> 'F'
        )
    FROM Student s
    WHERE s.id IN (
        SELECT i.StudentId
        FROM Inserted i
        JOIN Deleted d ON d.StudentId = i.StudentId
            AND d.CourseId = i.CourseId
            AND d.SectionId = i.SectionId
            AND d.Semester = i.Semester
            AND d.Year = i.Year
        WHERE i.Grade <> 'F' AND i.Grade IS NOT NULL
        AND (d.Grade = 'F' OR d.Grade IS NULL)
    )

Which can probably be further simplified as follows - which might update the odd student where nothing has changed - but thats a small price to pay for simplicity and speed:

    UPDATE s SET
        tot_cred = (
            -- Whatever logic gives the accurate credits at a point in time
            SELECT SUM(c.Credits)
            FROM Takes t
            JOIN Courses c on c.CourseId = t.CourseId
            WHERE t.StudentId = s.Id
            AND t.Grade <> 'F'
        )
    FROM Student s
    WHERE s.id IN (
        SELECT i.StudentId
        FROM Inserted i
        WHERE i.Grade <> 'F'
    )