SQL触发器报错：子查询返回多行值，寻求解决办法

问题根源 #

你遇到的错误并非函数本身的问题（函数中使用SUM聚合，必然返回单个值），而是触发器的逻辑缺陷：当批量操作明细行时，inserted/deleted表中会存在多条对应同一个cxc_transacciones_enc主表行的记录，导致触发器试图对同一主表行执行多次更新操作，进而引发报错。同时原触发器的@Action判断逻辑虽然能区分操作类型，但没必要分I/U和D分支处理——无论增删改，只要明细行关联的主表行发生变化，都需要重新计算余额。

修复方案 #

重构触发器逻辑：先提取所有受影响的主表唯一标识（去重），再统一调用函数更新主表余额，确保每个主表行只更新一次。

修改后的触发器代码 #

USE [BIOAGRO]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[SaldoDet]
ON [dbo].[CxC_Transacciones_Det]
AFTER INSERT, DELETE, UPDATE
AS
    SET NOCOUNT ON

    -- 收集所有受影响的主表唯一键，去重
    WITH AffectedHeaders AS (
        SELECT 
            Compania, 
            Cod_Cliente, 
            Doc_Tipo_Aplica AS Doc_Tipo, 
            Doc_Numero_Aplica AS Doc_Numero
        FROM INSERTED
        UNION -- 自动去重，避免同一主表行重复处理
        SELECT 
            Compania, 
            Cod_Cliente, 
            Doc_Tipo_Aplica AS Doc_Tipo, 
            Doc_Numero_Aplica AS Doc_Numero
        FROM DELETED
    )
    UPDATE e
    SET saldo = dbo.saldofactura(e.Cod_Cliente, e.Compania, 'RD', e.Doc_Tipo, e.Doc_Numero)
    FROM cxc_transacciones_enc e
    INNER JOIN AffectedHeaders ah 
        ON e.Compania = ah.Compania
        AND e.Cod_Cliente = ah.Cod_Cliente
        AND e.Doc_Tipo = ah.Doc_Tipo
        AND e.Doc_Numero = ah.Doc_Numero
GO

额外优化建议 #

你的Saldofactura函数可以做小幅优化，减少重复代码：

USE [XXXXXX]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[Saldofactura]
    (@Cliente nvarchar(12), 
     @Cia char(6), 
     @Moneda char(2), 
     @Tipo tinyint, 
     @Factura nvarchar(10))
RETURNS Money
AS
BEGIN
    DECLARE @Saldo money

    SELECT @Saldo = SUM(valor * (CASE WHEN doc_tipo IN (1,2) THEN 1 ELSE 0 END)) 
                   - SUM((valor + ISNULL(descuento, 0)) * (CASE WHEN doc_tipo IN (3,4,5) THEN 1 ELSE 0 END))
    FROM (
        -- 根据币种选择对应的明细表
        SELECT doc_tipo, valor, descuento
        FROM CxC_Transacciones_Det
        WHERE Compania = @cia 
          AND cod_cliente = @cliente  
          AND doc_tipo_aplica = @Tipo 
          AND doc_numero_aplica = @Factura
        UNION ALL
        SELECT doc_tipo, valor, descuento
        FROM CxCUS_Transacciones_Det
        WHERE Compania = @cia 
          AND cod_cliente = @cliente  
          AND doc_tipo_aplica = @Tipo 
          AND doc_numero_aplica = @Factura
          AND UPPER(@moneda) <> 'RD'
    ) AS CombinedData

    RETURN ISNULL(ROUND(@Saldo, 2), 0)
END
GO

说明 #

• 触发器中使用UNION合并inserted和deleted的数据并自动去重，确保每个主表行仅被处理一次，彻底解决批量操作时的多行问题。
• 优化后的函数通过子查询统一逻辑，减少重复代码，同时保持原有计算逻辑不变。

内容的提问来源于stack exchange，提问作者Rafael Mota Quintana