Erro na execução de procedure: This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

Question

Olá pessoal, gostaria de help. Tenho um job que executa uma proc diariamente, no qual passou a retornar o erro abaixo.

Msg 50000, Level 16, State 1, Procedure P_PROC_NAME, Line 403 [Batch Start Line 0]
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Analisando o conteúdo da procedure identifiquei que o erro estava sendo gerado pelo UPDATE abaixo. 

UPDATE P
SET    VALOR = I.valor
FROM   #tmp_valor I
       INNER JOIN table_a P
               ON P.cod = I.cod
                  AND P.cod_prod = I.cod_prod; 
Ao testar o UPDATE de forma isolada é gerado o mesmo erro, porém apontando para uma trigger existente na tabela table_a. 

Msg 512, Level 16, State 1, Procedure TRG_TRIGGER_NAME, Line 12 [Batch Start Line 387]
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

CREATE TRIGGER [dbo].[TRG_TRIGGER_NAME]
ON [dbo].[table_a]
after UPDATE, INSERT
AS
  BEGIN
      DECLARE @COD_PROD INT
      DECLARE @COD_FILIAL INT
      DECLARE @STATUS_PROD_NEW INT
      DECLARE @STATUS_PROD_OLD INT

      SELECT @COD_PROD = cod_prod,
             @COD_FILIAL = cod_filial,
             @STATUS_PROD_NEW = status_prod
      FROM   inserted

      SET @STATUS_PROD_OLD = (SELECT status_prod
                              FROM   deleted);

      IF ( @STATUS_PROD_NEW IS NOT NULL /*EXISTE AÇÃO NO CAMPO ATUAL*/
           AND ( ( @STATUS_PROD_NEW <> @STATUS_PROD_OLD )
                  OR ( @STATUS_PROD_OLD IS NULL ) )/*OU É UPDATE OU INSERT*/
          )
        BEGIN
            IF NOT EXISTS (SELECT 1
                           FROM   prod_sinc (nolock) E
                           WHERE  E.cod_prod = @COD_PROD
                                  AND E.cod_filial = @COD_FILIAL)
              BEGIN
                  INSERT INTO prod_sinc
                  SELECT cod_emp,
                         cod_filial,
                         cod_prod,
                         Getdate()
                  FROM   inserted
              END
            ELSE
              BEGIN
                  UPDATE prod_sinc
                  SET    data_ult_alt = Getdate()
                  WHERE  cod_prod = @COD_PROD
                         AND cod_filial = @COD_FILIAL
              END
        END
  END 
Gostaria de uma apoio para entender. Creio que o problema está no UPDATE da trigger. Quando desativa a trigger a procedure executa sem erro.

Grato desde já!

Answer 1

The error is originating from the trigger, specifically the part where you are assigning values to the variables from the inserted and deleted tables. These tables might have more than one row, especially if the UPDATE operation in the main procedure affects multiple rows. Since the subquery is directly assigning values to the scalar variables, it's expecting only one row, but multiple rows are causing the error.

CREATE TRIGGER [dbo].[TRG_TRIGGER_NAME]
ON [dbo].[table_a]
AFTER UPDATE, INSERT
AS
BEGIN
    -- Inserting new rows into prod_sinc for rows that don't exist
    INSERT INTO prod_sinc (cod_emp, cod_filial, cod_prod, data_ult_alt)
    SELECT i.cod_emp,
           i.cod_filial,
           i.cod_prod,
           GETDATE()
    FROM inserted i
    LEFT JOIN deleted d ON i.cod_prod = d.cod_prod AND i.cod_filial = d.cod_filial
    LEFT JOIN prod_sinc ps ON i.cod_prod = ps.cod_prod AND i.cod_filial = ps.cod_filial
    WHERE (i.status_prod <> d.status_prod OR d.status_prod IS NULL)
          AND ps.cod_prod IS NULL;

    -- Updating existing rows in prod_sinc
    UPDATE ps
    SET data_ult_alt = GETDATE()
    FROM prod_sinc ps
    JOIN inserted i ON i.cod_prod = ps.cod_prod AND i.cod_filial = ps.cod_filial
    LEFT JOIN deleted d ON i.cod_prod = d.cod_prod AND i.cod_filial = d.cod_filial
    WHERE (i.status_prod <> d.status_prod OR d.status_prod IS NULL);
END