Trigger after delete more than 1 record-CodePudding

There is a problem when I delete all records.

CREATE OR ALTER TRIGGER update_StuIns_OnDelete 
ON Class
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Instructor_ID TABLE (id BIGINT);

    INSERT INTO @Instructor_ID 
        SELECT prevC.c_InstructorID 
        FROM deleted prevC

    DECLARE @Institute_ID TABLE (id BIGINT);

    INSERT INTO @Institute_ID 
        SELECT prevC.c_InstituteID 
        FROM deleted prevC

    DECLARE @Instrument_ID TABLE (id BIGINT);

    INSERT INTO @Instrument_ID 
        SELECT prevC.c_InstrumentID 
        FROM deleted prevC

    DECLARE @Student_Id TABLE (id BIGINT);

    INSERT INTO @Student_Id 
        SELECT prevC.c_StudentID 
        FROM deleted prevC

    IF ((SELECT Learning_Time FROM Is_Learning
         WHERE l_StudentID IN (SELECT id FROM @Student_Id) 
           AND l_InstrumentID IN (SELECT id FROM @Instrument_ID)) IS NULL)
    BEGIN
        DELETE Is_Learning
        WHERE l_StudentID IN (SELECT id FROM @Student_Id) 
            AND l_InstrumentID IN (SELECT id FROM @Instrument_ID)
    END

    IF((SELECT Teaching_Time FROM Teaches
        WHERE t_InstructorID IN (SELECT id FROM @Instructor_ID)
          AND t_InstrumentID IN (SELECT id FROM @Instrument_ID)) IS NULL)
    BEGIN
        DELETE Teaches
        WHERE t_InstructorID IN (SELECT id FROM @Instructor_ID)
            AND t_InstrumentID IN (SELECT id FROM @Instrument_ID)
    END

    IF(NOT EXISTS(SELECT * FROM Class C 
                  WHERE C.c_InstructorID IN (SELECT id FROM @Instructor_ID) 
                    AND C.c_InstituteID IN (SELECT id FROM @Institute_ID)))
    BEGIN
        DELETE Is_MemberOf
        WHERE m_InstructorID IN (SELECT id FROM @Instructor_ID)
            AND m_InstituteID IN (SELECT id FROM @Institute_ID)
    END
END;

When I want to delete 1 row, it works correctly, but for multiple rows, I get this error:

Msg 512, Level 16, State 1, Procedure update_StuIns_OnDelete, Line 13
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any suggestion for this problem?

CodePudding user response：

You have three if statements in your logic. if ((select ... is null), another if ((select ... is null, and an if (not exists ...)

In the first two cases, you are comparing the result of a select statement (which returns a set of rows) against null. But null is a single scalar. You can't compare a whole column against null.

Change those two to match the not exists construction of the third if.

CodePudding user response：

This worked correctly.

CREATE OR ALTER TRIGGER update_StuIns_OnDelete ON Class
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    IF(EXISTS(SELECT Learning_Time FROM Is_Learning INNER JOIN
        deleted d ON l_StudentID = d.c_StudentID AND l_InstrumentID = d.c_InstrumentID 
        WHERE Learning_Time IS NULL))
    BEGIN
        DELETE Is_Learning
        FROM Is_Learning INNER JOIN
        deleted d ON l_StudentID = d.c_StudentID AND l_InstrumentID = d.c_InstrumentID 
    END
    IF(EXISTS(SELECT Teaching_Time FROM Teaches INNER JOIN
        deleted d ON t_InstructorID = d.c_InstructorID AND t_InstrumentID = d.c_InstrumentID 
        WHERE Teaching_Time IS NULL))
    BEGIN
        DELETE Teaches
        FROM Teaches INNER JOIN
        deleted d ON t_InstructorID = d.c_InstructorID AND t_InstrumentID = d.c_InstrumentID 
    END
    IF(NOT EXISTS(SELECT * FROM Class C INNER JOIN deleted d
    ON C.c_InstructorID = d.c_InstructorID AND C.c_InstituteID = d.c_InstituteID))
    BEGIN
        DELETE Is_MemberOf
        FROM Is_MemberOf INNER JOIN
        deleted d ON m_InstructorID = d.c_InstructorID AND m_InstituteID = d.c_InstituteID 
    END
END;