SQL Server 2014中Delete语句执行失败问题排查求助

有一个部署在Azure Server 2012虚拟机上的Connectwise报表数据库，每日清晨执行的清理存储过程负责合并多条记录后删除冗余数据。因父表关联的子表未设置级联删除，存储过程会先删除子表关联记录，但自2月起脚本执行失败，报错如下：

Msg 512, Level 16, State 1, Procedure Alter_SR_Service_User_Defined_Field_Value, Line 53 [Batch Start Line 93]
子查询返回了多个值。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

执行的Delete语句：

delete
from    
    SR_Service_User_Defined_Field_Value
where
    SR_Service_RecID in (   select 
                                (s.SR_Service_RecID)
                            from
                                SR_Service s
                            join SR_Board sb on sb.SR_Board_RecID=s.SR_Board_RecID
                            where sb.Board_Name like 'Collections' 
                                and s.SR_Status_RecID != 511
                                and cast(trim(left(right(s.Summary, len(s.Summary) - charindex('#',s.Summary)),5))as int)=@invoiceNumber
                        )
    and SR_Service_RecID != (   select 
                                    min (s.SR_Service_RecID)
                                from
                                    SR_Service s
                                join SR_Board sb on sb.SR_Board_RecID=s.SR_Board_RecID
                                where sb.Board_Name like 'Collections' 
                                    and s.SR_Status_RecID != 511
                                    and cast(trim(left(right(s.Summary, len(s.Summary) - charindex('#',s.Summary)),5))as int)=@invoiceNumber
                            )
-- 注意：原语句末尾多了一个多余的右括号，需删除

SR_Service_User_Defined_Field_Value表结构（首列为主键，第二、三列为外键）：

已做排查：

• 简化Delete语句仅按SR_Service_RecID删除，仍失败
• 通过SR_Service_User_Defined_Field_Value_RecID用IN删除多条记录，失败
• 用IN删除单个RecID记录，成功
• 设置外键级联删除后，删除父表仍报相同错误

表上的触发器代码：

-- Code for delete 
if exists(select * from deleted) and not exists(Select * from inserted)
begin 
    INSERT INTO [TruCWHistorian].[dbo].[CWHistorian]( [Table], [Object_RecID], [FieldName], [Old_Value], [New_Value], [Date_Updated], [Updated_By])
    SELECT 
        'SR_Service_User_Defined_Field_Value',
        d.SR_Service_RecID,
        u.Caption,
        (select User_Defined_Field_Value FROM deleted), -- 问题出在这里
        '', -- no new value for deleted record case 
        getDate(),
        '' -- no record of who made change in this case 
    FROM    
        deleted d
    join User_Defined_Field u on u.User_Defined_Field_RecID=d.User_Defined_Field_RecID
end  

报错根源在触发器的SELECT语句中：(select User_Defined_Field_Value FROM deleted)是无关联的子查询，当删除多条记录时，这个子查询会返回所有被删除记录的User_Defined_Field_Value值（多个结果），但SELECT列表中作为字段表达式只能接受单个值，因此触发Msg512错误。

1. 修正触发器代码：将子查询替换为直接引用deleted表的字段d.User_Defined_Field_Value，每条被删除记录会对应自身的字段值，不会返回多值。修改后的触发器：

-- Code for delete 
if exists(select * from deleted) and not exists(Select * from inserted)
begin 
    INSERT INTO [TruCWHistorian].[dbo].[CWHistorian]( [Table], [Object_RecID], [FieldName], [Old_Value], [New_Value], [Date_Updated], [Updated_By])
    SELECT 
        'SR_Service_User_Defined_Field_Value',
        d.SR_Service_RecID,
        u.Caption,
        d.User_Defined_Field_Value, -- 替换原多值子查询
        '',
        getDate(),
        ''
    FROM    
        deleted d
    join User_Defined_Field u on u.User_Defined_Field_RecID=d.User_Defined_Field_RecID
end  

2. 修正Delete语句语法：删除原语句末尾多余的右括号，避免语法错误。

修改后执行Delete语句，或通过级联删除父表记录，触发器会正常插入历史记录，不会再触发Msg512错误。

内容的提问来源于stack exchange，提问作者Scot_C