SQL Trigger giving error. Subquery returned more than 1 value-CodePudding

I have this database and I've created a function to update the GPA of a student.

CREATE OR ALTER FUNCTION fGPA
    (@Mno varchar(9))
RETURNS DECIMAL(10,4)
BEGIN
    DECLARE @gpa decimal(10,4);

    SET @gpa = (SELECT 
                    (SUM(CASE e.Grade 
                             WHEN 'A' THEN 4.0
                             WHEN 'B' THEN 3.0
                             WHEN 'C' THEN 2.0
                             WHEN 'D' THEN 1.0
                             WHEN 'F' THEN 0.0 
                         END * c.Credit) / SUM(c.Credit)) 
                FROM Enroll AS e
                INNER JOIN Section AS s ON s.Sid = e.Sid
                INNER JOIN Course AS c ON c.Cno = s.Cno
                WHERE @Mno = e.Mno);
    
    RETURN @gpa;
END

I am now trying to create a trigger so that whenever a grade is updated in Enroll, the fGPA function is called and updates the GPA as well. Here is what I have:

CREATE OR ALTER TRIGGER update_enroll
ON Enroll
AFTER UPDATE
AS
    IF (UPDATE(Grade))
    BEGIN
        DECLARE @updateGPA DECIMAL(10,4);

        SET @updateGPA = (SELECT dbo.fGPA(e.Mno) 
                          FROM Enroll as e
                          INNER JOIN inserted i ON i.Mno = e.Mno);

        UPDATE Student 
        SET student.GPA = @updateGPA;
    END

The error I get is:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

This happens when I try to perform this statement

UPDATE ENROLL 
SET Grade = 'B' 
WHERE Sid = '17504';

CodePudding user response：

…
BEGIN
/*
DECLARE @updateGPA DECIMAL(10,4);

SET @updateGPA = (SELECT dbo.fGPA(e.Mno) 
                  FROM Enroll as e
                  INNER JOIN inserted i ON i.Mno = e.Mno);
*/

UPDATE Student 
SET GPA = dbo.fGPA(Mno)
WHERE Mno IN (SELECT i.Mno FROM inserted AS i)

END
…

CodePudding user response：

You are making a very common mistake with triggers: the inserted table may have multiple (or zero) rows. So you should do this as a joined update.

Note that the UPDATE() function only tells you if the column is present in the UPDATE statement, not if it was actually updated. You should also join the deleted table to exclude such rows.

This leads on to another common issue: using scalar User-Defined Functions. They are slow, and should generally be avoided. Instead, use an inline Table Valued Function.

You are also updating the entire Student table, you are not joining by anything.

CREATE OR ALTER FUNCTION fGPA
    (@Mno varchar(9))
RETURNS TABLE
AS RETURN

    SELECT 
        GPA = SUM(CASE e.Grade 
                 WHEN 'A' THEN 4.0
                 WHEN 'B' THEN 3.0
                 WHEN 'C' THEN 2.0
                 WHEN 'D' THEN 1.0
                 WHEN 'F' THEN 0.0 
             END * c.Credit) / SUM(c.Credit)
    FROM Enroll AS e
    INNER JOIN Section AS s ON s.Sid = e.Sid
    INNER JOIN Course AS c ON c.Cno = s.Cno
    WHERE @Mno = e.Mno;

Then you simply CROSS APPLY it.

CREATE OR ALTER TRIGGER update_enroll
ON Enroll
AFTER UPDATE
AS
    SET NOCOUNT ON;
    IF (NOT UPDATE(Grade) OR NOT EXISTS (SELECT 1 FROM inserted))  -- bail out for no rows
        RETURN;

    UPDATE s
    SET s.GPA = gpa.GPA
    FROM inserted i
    JOIN Student s ON s.Mno = i.Mno
    CROSS APPLY dbo.fGPA(i.Mno) gpa;

I will say that I don't think triggers are really the right answer to this problem.

Ideally you should not keep two copies of the same information in two places. Instead create a view that has this calculation in it.

CREATE VIEW vGPA
AS

    SELECT
        s.Mno,
        GPA = SUM(CASE e.Grade 
                 WHEN 'A' THEN 4.0
                 WHEN 'B' THEN 3.0
                 WHEN 'C' THEN 2.0
                 WHEN 'D' THEN 1.0
                 WHEN 'F' THEN 0.0 
             END * c.Credit) / SUM(c.Credit)
    FROM dbo.Student AS s
    INNER JOIN dbo.Enroll AS e ON e.Mno = s.Mno
    INNER JOIN dbo.Section AS s ON s.Sid = e.Sid
    INNER JOIN dbo.Course AS c ON c.Cno = s.Cno
    GROUP BY
        s.Mno;

If you are worried about performance, you can index the view to support that. The server will ensure the index always remains in sync with the base table.

Unfortunately, you cannot index a calculated expression on aggregates (ie SUM / SUM). Instead, create a base view which has the two SUMs, then another standard view which selects the value from it.

CREATE VIEW vGPA_indexed
WITH SCHEMABINDING  -- needs schema binding
AS

    SELECT
        s.Mno,
        SumGrade = SUM(CASE e.Grade 
                 WHEN 'A' THEN 4.0
                 WHEN 'B' THEN 3.0
                 WHEN 'C' THEN 2.0
                 WHEN 'D' THEN 1.0
                 WHEN 'F' THEN 0.0 
             END * c.Credit),
        SumCredit = SUM(c.Credit),
        TotalRows = COUNT_BIG(*)  -- needs count in grouped view
    FROM dbo.Student AS s
    INNER JOIN dbo.Enroll AS e ON e.Mno = s.Mno
    INNER JOIN dbo.Section AS s ON s.Sid = e.Sid
    INNER JOIN dbo.Course AS c ON c.Cno = s.Cno
    GROUP BY
        s.Mno;

CREATE UNIQUE CLUSTERED INDEX CX_vGPA_indexed on vGPA_indexed (Mno);

CREATE VIEW dbo.vGPA
AS

    SELECT
        s.Mno,
        GPA = SumGrade / SumCredit
    FROM dbo.vGPA_indexed WITH (NOEXPAND);  -- needs NOEXPAND in standard edition, and anyway better for performance