Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I update a single row, it works fine. But when I update all rows with:

 UPDATE cad_bilhetes
 SET ligacao_acobrar = 'False'

I get the following error:

Msg 512, Level 16, State 1, Procedure TG_CAD_BILHETES_UPDATE, Line 34 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

In line 34 of TG_CAD_BILHETES_UPDATE there is:

DECLARE @VALOR_LIGACOES_DDI_ATUAL DECIMAL(7,2)

The beginning of the trigger:

ALTER TRIGGER [dbo].[TG_CAD_BILHETES_UPDATE]
ON [dbo].[CAD_BILHETES]
INSTEAD OF UPDATE
AS
BEGIN
DECLARE @ID INT
DECLARE @VALOR_BILHETE_NOVO DECIMAL(7,2)
DECLARE @VALOR_BILHETE_ATUAL DECIMAL(7,2)
DECLARE @ENCONTROU INT
DECLARE @ID_CONTRATACAO INT
DECLARE @TIPO VARCHAR(2)
DECLARE @TIPO_APARELHO VARCHAR(1)
DECLARE @ID_COMPETENCIA INT
DECLARE @ID_CONTRATACAO_ATUAL INT
DECLARE @ID_CONTRATACAO_NOVO INT
DECLARE @VALOR_LIGACOES_DDD_FIXO_NOVO DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_DDD_MOVEL_NOVO DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_LOCAL_MOVEL_NOVO DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_LOCAL_FIXO_NOVO DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_DDI_NOVO DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_VOIP_NOVO DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_TOM_REMOTO_NOVO DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_DDD_FIXO_ATUAL DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_DDD_MOVEL_ATUAL DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_LOCAL_MOVEL_ATUAL DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_LOCAL_FIXO_ATUAL DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_DDI_ATUAL DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_VOIP_ATUAL DECIMAL(7,2)
DECLARE @VALOR_LIGACOES_TOM_REMOTO_ATUAL DECIMAL(7,2)
DECLARE @VALOR_OS_NOVO DECIMAL(7,2)
DECLARE @FOI_CANCELADO_NOVO BIT

SET @ENCONTROU = 0
SET @ID = (SELECT ID FROM INSERTED)
SET @ID_COMPETENCIA = (SELECT ID_COMPETENCIA FROM INSERTED)
SET @VALOR_LIGACOES_DDD_FIXO_NOVO = 0
SET @VALOR_LIGACOES_DDD_MOVEL_NOVO = 0
SET @VALOR_LIGACOES_LOCAL_MOVEL_NOVO = 0
SET @VALOR_LIGACOES_LOCAL_FIXO_NOVO = 0
SET @VALOR_LIGACOES_DDI_NOVO = 0
SET @VALOR_LIGACOES_VOIP_NOVO = 0
SET @VALOR_LIGACOES_TOM_REMOTO_NOVO = 0
SET @VALOR_LIGACOES_DDD_FIXO_ATUAL = 0
SET @VALOR_LIGACOES_DDD_MOVEL_ATUAL = 0
SET @VALOR_LIGACOES_LOCAL_MOVEL_ATUAL = 0
SET @VALOR_LIGACOES_LOCAL_FIXO_ATUAL = 0
SET @VALOR_LIGACOES_DDI_ATUAL = 0
SET @VALOR_LIGACOES_VOIP_ATUAL = 0
SET @VALOR_LIGACOES_TOM_REMOTO_ATUAL = 0

SELECT TOP (1)
       @TIPO = SUBSTRING(B.TIPO, 2, 2), @TIPO_APARELHO = SUBSTRING(B.TIPO, 4, 1),
       @VALOR_BILHETE_ATUAL = B.VALOR,
       @ID_CONTRATACAO_ATUAL = C.ID
FROM CAD_BILHETES AS B
JOIN CAD_CONTRATACAO AS C
ON B.ID_PRODUTO = C.ID_PRODUTO AND (B.DATA_HORA >= C.DATA_INICIO AND (C.DATA_FIM IS NULL OR B.DATA_HORA <= C.DATA_FIM))  
WHERE B.ID = @ID and 
      C.FOI_CANCELADA= 'FALSE'

IF @ID_CONTRATACAO_ATUAL IS NOT NULL
BEGIN
    IF(@TIPO = 'IV')
        BEGIN
            SET @VALOR_LIGACOES_VOIP_ATUAL = @VALOR_BILHETE_ATUAL
        END
    ELSE
    IF(@TIPO = 'IT')
        BEGIN
            SET @VALOR_LIGACOES_TOM_REMOTO_ATUAL = @VALOR_BILHETE_ATUAL
        END
    ELSE
    IF(@TIPO = 'EL')
        BEGIN
            IF(@TIPO_APARELHO = 'F')
            BEGIN
                SET @VALOR_LIGACOES_LOCAL_FIXO_ATUAL = @VALOR_BILHETE_ATUAL
            END
            ELSE
            IF(@TIPO_APARELHO = 'M')
            BEGIN
                SET @VALOR_LIGACOES_LOCAL_MOVEL_ATUAL = @VALOR_BILHETE_ATUAL
            END
        END
    ELSE
    IF(@TIPO = 'EN')
        BEGIN
            IF(@TIPO_APARELHO = 'F')
            BEGIN
                SET @VALOR_LIGACOES_DDD_FIXO_ATUAL = @VALOR_BILHETE_ATUAL
            END
            ELSE
            IF(@TIPO_APARELHO = 'M')
            BEGIN
                SET @VALOR_LIGACOES_DDD_MOVEL_ATUAL = @VALOR_BILHETE_ATUAL
            END
        END
    ELSE
    IF(@TIPO = 'EI')
        BEGIN
            SET @VALOR_LIGACOES_DDI_ATUAL = @VALOR_BILHETE_ATUAL
        END
END

SELECT @TIPO = SUBSTRING(I.TIPO, 2, 2), @TIPO_APARELHO = SUBSTRING(I.TIPO, 4, 1),
       @VALOR_BILHETE_NOVO = I.VALOR,
       @ID_CONTRATACAO_NOVO = C.ID,
       @FOI_CANCELADO_NOVO = I.FOI_CANCELADO,   
       @ID_COMPETENCIA = I.ID_COMPETENCIA   
FROM INSERTED AS I
JOIN CAD_CONTRATACAO AS C 
ON I.ID_PRODUTO = C.ID_PRODUTO AND (I.DATA_HORA >= C.DATA_INICIO AND (I.DATA_HORA <= C.DATA_FIM  OR C.DATA_FIM IS NULL)) 
WHERE C.FOI_CANCELADA= 'FALSE'

IF @ID_CONTRATACAO_NOVO IS NOT NULL
BEGIN
    IF(@TIPO = 'IV')
        BEGIN
            SET @VALOR_LIGACOES_VOIP_NOVO = @VALOR_BILHETE_NOVO
        END
    ELSE
    IF(@TIPO = 'IT')
        BEGIN
            SET @VALOR_LIGACOES_TOM_REMOTO_NOVO = @VALOR_BILHETE_NOVO       
        END
    ELSE
    IF(@TIPO = 'EL')
        BEGIN
            IF(@TIPO_APARELHO = 'F')
            BEGIN
                SET @VALOR_LIGACOES_LOCAL_FIXO_NOVO = @VALOR_BILHETE_NOVO
            END
            ELSE
            IF(@TIPO_APARELHO = 'M')
            BEGIN
                SET @VALOR_LIGACOES_LOCAL_MOVEL_NOVO = @VALOR_BILHETE_NOVO  
            END
        END
    ELSE
    IF(@TIPO = 'EN')
        BEGIN
            IF(@TIPO_APARELHO = 'F')
            BEGIN
                SET @VALOR_LIGACOES_DDD_FIXO_NOVO = @VALOR_BILHETE_NOVO
            END
            ELSE
            IF(@TIPO_APARELHO = 'M')
            BEGIN
                SET @VALOR_LIGACOES_DDD_MOVEL_NOVO = @VALOR_BILHETE_NOVO
            END
        END
    ELSE
    IF(@TIPO = 'EI')
        BEGIN
            SET @VALOR_LIGACOES_DDI_NOVO = @VALOR_BILHETE_NOVO
        END

SELECT @ENCONTROU = 1
FROM   CAD_CUSTO_PRODUTO
WHERE ID_CONTRATACAO = @ID_CONTRATACAO_NOVO AND
ID_COMPETENCIA=@ID_COMPETENCIA

IF(@ENCONTROU = 0)
    BEGIN
        INSERT INTO CAD_CUSTO_PRODUTO
        VALUES (@ID_CONTRATACAO_NOVO, @ID_COMPETENCIA, NULL, 0, 0, 0, @VALOR_LIGACOES_DDD_FIXO_NOVO, 
                @VALOR_LIGACOES_DDD_MOVEL_NOVO, @VALOR_LIGACOES_LOCAL_MOVEL_NOVO, @VALOR_LIGACOES_LOCAL_FIXO_NOVO, 
                @VALOR_LIGACOES_DDI_NOVO, @VALOR_LIGACOES_VOIP_NOVO, @VALOR_LIGACOES_TOM_REMOTO_NOVO)
    END
ELSE
BEGIN
    IF (@FOI_CANCELADO_NOVO = 1)
    BEGIN
        UPDATE CAD_CUSTO_PRODUTO
        SET VALOR_LIGACOES_DDD_FIXO = VALOR_LIGACOES_DDD_FIXO - @VALOR_LIGACOES_DDD_FIXO_ATUAL,
            VALOR_LIGACOES_DDD_MOVEL = VALOR_LIGACOES_DDD_MOVEL - @VALOR_LIGACOES_DDD_MOVEL_ATUAL,
            VALOR_LIGACOES_LOCAL_MOVEL = VALOR_LIGACOES_LOCAL_MOVEL - @VALOR_LIGACOES_LOCAL_MOVEL_ATUAL,
            VALOR_LIGACOES_LOCAL_FIXO = VALOR_LIGACOES_LOCAL_FIXO - @VALOR_LIGACOES_LOCAL_FIXO_ATUAL,
            VALOR_LIGACOES_DDI = VALOR_LIGACOES_DDI - @VALOR_LIGACOES_DDI_ATUAL,
            VALOR_LIGACOES_VOIP = VALOR_LIGACOES_VOIP - @VALOR_LIGACOES_VOIP_ATUAL,
            VALOR_LIGACOES_TOM_REMOTO = VALOR_LIGACOES_TOM_REMOTO - @VALOR_LIGACOES_TOM_REMOTO_ATUAL
        WHERE ID_CONTRATACAO = @ID_CONTRATACAO_ATUAL AND ID_COMPETENCIA = @ID_COMPETENCIA
    END
    ELSE
    BEGIN
        UPDATE CAD_CUSTO_PRODUTO
        SET VALOR_LIGACOES_DDD_FIXO = VALOR_LIGACOES_DDD_FIXO + (@VALOR_LIGACOES_DDD_FIXO_NOVO - @VALOR_LIGACOES_DDD_FIXO_ATUAL),
            VALOR_LIGACOES_DDD_MOVEL = VALOR_LIGACOES_DDD_MOVEL + (@VALOR_LIGACOES_DDD_MOVEL_NOVO - @VALOR_LIGACOES_DDD_MOVEL_ATUAL),
            VALOR_LIGACOES_LOCAL_MOVEL = VALOR_LIGACOES_LOCAL_MOVEL + (@VALOR_LIGACOES_LOCAL_MOVEL_NOVO - @VALOR_LIGACOES_LOCAL_MOVEL_ATUAL),
            VALOR_LIGACOES_LOCAL_FIXO = VALOR_LIGACOES_LOCAL_FIXO + (@VALOR_LIGACOES_LOCAL_FIXO_NOVO - @VALOR_LIGACOES_LOCAL_FIXO_ATUAL),
            VALOR_LIGACOES_DDI = VALOR_LIGACOES_DDI + (@VALOR_LIGACOES_DDI_NOVO - @VALOR_LIGACOES_DDI_ATUAL),
            VALOR_LIGACOES_VOIP = VALOR_LIGACOES_VOIP + (@VALOR_LIGACOES_VOIP_NOVO - @VALOR_LIGACOES_VOIP_ATUAL),
            VALOR_LIGACOES_TOM_REMOTO = VALOR_LIGACOES_TOM_REMOTO + (@VALOR_LIGACOES_TOM_REMOTO_NOVO - @VALOR_LIGACOES_TOM_REMOTO_ATUAL)
        WHERE ID_CONTRATACAO = @ID_CONTRATACAO_ATUAL AND ID_COMPETENCIA = @ID_COMPETENCIA
    END
END
END

UPDATE CAD_BILHETES
SET ID_PREFIXO = I.ID_PREFIXO,
    ID_PRODUTO = I.ID_PRODUTO,  
    ID_COMPETENCIA = I.ID_COMPETENCIA,  
    DATA_HORA = I.DATA_HORA,
    DURACAO = I.DURACAO,
    TIPO = I.TIPO,
    NUMERO_EXTERNO = I.NUMERO_EXTERNO,
    RAMAL = I.RAMAL,
    SENHA_AUTORIZACAO = I.SENHA_AUTORIZACAO,
    CODIGO_DEPENDENCIA = I.CODIGO_DEPENDENCIA,
    CRITICA = I.CRITICA,
    [STATUS] = I.STATUS,
    VALOR = I.VALOR,
    SEQUENCIAL = I.SEQUENCIAL,
    FOI_TARIFADO = I.FOI_TARIFADO,
    FOI_CANCELADO = I.FOI_CANCELADO,
    DURACAO_TARIFACAO = I.DURACAO_TARIFACAO
FROM INSERTED AS I
WHERE CAD_BILHETES.ID = I.ID
END
share|improve this question
5  
Probably your trigger assumes that inserted or deleted tables only has one row and is treating a select from one or other of them as a scalar. The line number presumably refers to a different line than the one you have identified. I can't remember if it includes the CREATE TRIGGER header. –  Martin Smith Oct 22 '12 at 13:05
    
I'll put my trigger, the beginning. –  Vinicius Ottoni Oct 22 '12 at 13:07
5  
The error is coming from SET @ID = (SELECT ID FROM INSERTED). You need to rewrite (at least part of) the trigger to take into account that inserted can contain multiple (or zero) rows not just exactly one. –  Martin Smith Oct 22 '12 at 13:11
4  
No. INSERTED is a pseudo table containing all rows affected by the update statement. –  Martin Smith Oct 22 '12 at 13:13
7  
No that won't solve the problem it will just hide it. Whatever your trigger is supposed to be doing won't happen for arbitrary rows. You need to rewrite it to work against a set of rows. e.g. join onto the inserted table rather than doing an = seek on a scalar variable. –  Martin Smith Oct 22 '12 at 13:24
show 2 more comments

1 Answer 1

up vote 7 down vote accepted

SET @ID = (SELECT ID FROM INSERTED)
SET @ID_COMPETENCIA = (SELECT ID_COMPETENCIA FROM INSERTED)

It's a common misconception that triggers fire once per row affected, when in fact a trigger body is only fired once per DML statement **.

In the trigger body, the inserted and deleted tables contain all the rows affected by the operation. This is done for (at least) two reasons:

  1. Logical operations. Performing a task within the set of affected rows would be extremely painful if only a single row was available to the trigger body.
  2. Performance. Efficient set-based operations can be used to process all of the affected rows at once.

Single-row trigger code can go unnoticed for a long time because it may legitimately work for your use cases, and only break when someone decides to affect more than a single row. Even worse, it's possible that the trigger code may actually succeed, and end up doing the wrong thing with no error.

Unfortunately, there are no safeties to tell you this information when writing triggers, so consider yourself lucky to have found this bug!

** For people reading this with SQL Server 2008+ in mind, MERGE fires the 3 different types separately, as it's really just a transactional "macro" to doing INSERT + UPDATE + DELETE in the same T-SQL statement.

share|improve this answer
    
So, how can I know which row to update? (Get the data of current row that is updated, and proceed to update others rows correctly). –  Vinicius Ottoni Oct 22 '12 at 18:28
    
@ViniciusOttoni: The logic should be written to do a JOIN to inserted and/or deleted. From the code you've provided, it's unclear what all needs to be changed. –  Jon Seigel Oct 22 '12 at 18:34
    
I'll put the entire trigger. –  Vinicius Ottoni Oct 22 '12 at 18:40
3  
@JonSeigel: It's not really a misconception, it's a feature found in other DBMS systems (eg Oracle, IBM DB2). You can choose for what to fire a trigger (each row or complete statement). SQL Server doesn't have it, so it's easy for guys used to other technologies to miss it. Otherwise your answer is well worth the points. –  Marian Nov 2 '12 at 9:16
    
@Marian: Interesting. I didn't know that. Thanks. –  Jon Seigel Nov 2 '12 at 13:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.