删除后触发超过1个记录

Question

当我删除所有记录时，存在问题。

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;

当我想删除1行时，它可以正常工作，但是对于多行，我会得到此错误：

msg 512，16级，状态1，过程update_stuins_ondelete，第13行
子查询返回超过1个值。当子查询跟随=，！=，＆lt;，＆lt; = ,,＆gt; =或当子查询用作表达式时。

对这个问题有任何建议吗？

Answer 1

如果逻辑中的语句语句，则有三个。 if（（（选择...为null），另一个if（（（选择...为null），if（不存在...）<（不存在...）< /code>

在前两个情况下，您正在比较选择语句（返回一组行）与null。您无法将整列与 > NULL 。

<代码

Answer 2

这起作用了。

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;