SQL Server › Triggers

Why should triggers generally be designed to handle multiple affected rows at once rather than assuming only a single row was changed?

Intermediate

Answer

A single statement, such as an UPDATE with a broad WHERE clause or none at all, can affect many rows simultaneously, and a trigger fires only once for that entire statement regardless of how many rows were changed, so if your trigger logic assumes only one row exists in the inserted or deleted tables, it will behave incorrectly and potentially cause serious bugs when a multi row operation actually occurs.

-- Correctly handles multiple rows using a set based approach
CREATE TRIGGER trg_UpdateInventory
ON OrderItems
AFTER INSERT
AS
BEGIN
  UPDATE p
  SET p.Stock = p.Stock - i.Quantity
  FROM Products p
  JOIN inserted i ON p.ProductId = i.ProductId;
END;

Real-world example A developer fixes a buggy trigger that had assumed only one row would ever be inserted at a time, causing it to silently fail to update inventory correctly during a legitimate bulk order import that inserted hundreds of rows at once.

Common follow-ups: How do you test a trigger to confirm it correctly handles a multi row operation?;What common mistakes lead to triggers that only work correctly for single row changes?

Data Types & Schema Design;Error Handling with TRY CATCH