Triggers Part 2: Facts and Frequently-Occuring-Problems (FOP)

This is part of a 3 part epic mini-series on triggers, view Part 1 and Part 3.

When are triggers fired, and how many times?

For your standard missionary position trigger, they are fired once per batch. So if you have an update statement that affects 57 rows, then the DML AFTER trigger fires *once* but the deleted and inserted magic tables have 57 rows in them. One common anti-pattern is not handling multiple values in the inserted/deleted tables. Code to prove the firing behavior of triggers:

The inserted and deleted tables hold the date being changed. Depending on what you are doing they hold different data:

Insert

Inserted – holds the new data

Deleted – is empty

Update

Inserted – holds new data

Deleted – holds old data

Delete

Deleted – data being deleted

Inserted – is empty

The inserted and deleted tables are not indexed, so take care in querying them in the wrong way. Given the logic above, if possible it is normally cleaner to not combine an insert/update trigger. If you have to, the below if my template for doing so: