Triggers - Pro SQL Server Internals - page 164

■ AFTER triggers do not fire in the case of constraints or index uniqueness violations. Note

Finally, we have the new inserted and deleted tables created and AFTER triggers fired. At this point, the new
row has already been inserted into the table and, if we need to rollback the changes, SQL Server would undo the insert
operation. In the example above, it would be more efficient to have the order status check implemented as part of
INSTEAD OF rather than the AFTER trigger.
As I already mentioned, triggers are running on a per-statement rather than a per-row basis. Our implementation
needs to work correctly when inserted and deleted tables have more than one row. For example, the
implementation in Listing 8-3 would fail with the exception that the subquery used in the set operator returned more
than one row if multiple rows were updated.

Listing 8-3. Triggers implementation: Incorrect implementation

create trigger Trg_OrderLineItems_AfterUpdate_Incorrect on dbo.OrderLineItems
after update
as
begin
-- Some code here
declare
@OrderId int
set @OrderId = (select OrderId from inserted)
-- Some code here
end

Error Message:
Msg 512, Level 16, State 1, Procedure Trg_OrderLineItems_AfterUpdate_Incorrect, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Alternatively, triggers would fire even if the DML statement does not change (insert, update, or delete) any data.
In that case, both inserted and deleted tables would be empty. In order to create an efficient implementation, you
need to have a few checks in the beginning of the trigger to prevent unnecessary code from being executed. Let's look
at our implementation again, as shown in Listing 8-4.

Listing 8-4. Triggers implementation: Preventing unnecessary code from being executed

create trigger trg_OrderLineItems_InsteadOfInsert on dbo.OrderLineItems
instead of insert
as
begin
if @@rowcount = 0
return
set nocount on
if not exists(select * from inserted)
return
-- Some code here
end