I have this trigger, and it throws this error:

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

I know the inserted and deleted may return multiple rows, but I can’t find a way to solve this.

USE [BIOAGRO]
GO

/****** Object:  Trigger [dbo].[SaldoDet]    Script Date: 9/11/2024 7:37:18 AM ******/
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

    BEGIN
        DECLARE @Action AS char(1);

        SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
                                 AND EXISTS(SELECT * FROM DELETED)
                                THEN 'U'  -- Set Action to Updated.
                            WHEN EXISTS(SELECT * FROM INSERTED)
                                THEN 'I'  -- Set Action to Insert.
                            WHEN EXISTS(SELECT * FROM DELETED)
                                THEN 'D'  -- Set Action to Deleted.
                       END)
        IF @Action = 'I' OR @Action = 'U'
            UPDATE cxc_transacciones_enc 
            SET saldo = dbo.saldofactura(i.cod_cliente, i.compania, 'RD', i.doc_tipo_aplica, i.doc_numero_aplica) 
            FROM cxc_transacciones_enc e 
            INNER JOIN inserted i ON e.Compania = i.Compania 
                                  AND e.Cod_Cliente = i.Cod_Cliente 
                                  AND e.Doc_Tipo = i.Doc_Tipo_Aplica 
                                  AND e.Doc_Numero = i.Doc_Numero_Aplica

        IF @Action = 'D'
            UPDATE cxc_transacciones_enc 
            SET saldo = dbo.saldofactura(d.cod_cliente, d.compania, 'RD', d.doc_tipo_aplica, d.doc_numero_aplica) 
            FROM cxc_transacciones_enc e 
            INNER JOIN deleted d ON e.Compania = d.Compania 
                                 AND e.Cod_Cliente = d.Cod_Cliente 
                                 AND e.Doc_Tipo = d.Doc_Tipo_Aplica 
                                 AND e.Doc_Numero = d.Doc_Numero_Aplica
    END

I update all the cxc_transacciones_det rows to update the column saldo in table cxc_transacciones_enc, and get the error.

I need to update the column saldo in table cxc_transacciones_enc when something happens in its detail table, cxc_transacciones_det

This is the function

USE [XXXXXX]
GO

/****** Object:  UserDefinedFunction [dbo].[Saldofactura]    Script Date: 9/11/2024 10:21:01 AM ******/
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 @Debito Money, @Credito Money, @Saldo money

    IF UPPER(@moneda) = 'RD'
    BEGIN
        SET @Saldo = (SELECT 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 CxC_Transacciones_Det
                      WHERE Compania = @cia 
                        AND cod_cliente = @cliente  
                        AND doc_tipo_aplica = @Tipo 
                        AND doc_numero_aplica = @Factura)
    END
    ELSE
    BEGIN
        SET @Saldo = (SELECT 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 CxCUS_Transacciones_Det
                      WHERE Compania = @cia 
                        AND cod_cliente = @cliente  
                        AND doc_tipo_aplica = @Tipo 
                        AND doc_numero_aplica = @Factura)
    END

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