Error when UPDATE all rows

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_LIGAC

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:

• 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.

• 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.

StackExchange Database Administrators Q#27366, answer score: 7