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.

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 SmithOct 22 '12 at 13:05

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 SmithOct 22 '12 at 13:11

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 SmithOct 22 '12 at 13:24

1 Answer
1

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.

@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.
–
MarianNov 2 '12 at 9:16