What is a trigger in SQL Server, and when should you avoid one?
Quick Answer
A trigger is code that runs automatically in response to INSERT, UPDATE, DELETE (DML triggers) or DDL/logon events. Inside, the inserted and deleted pseudo-tables hold the affected rows. Avoid them for business logic — they run hidden inside the caller's transaction, are set-based (fire once per statement, not per row), and are easy to forget.
A trigger is code SQL Server runs automatically when data changes — after or instead of an insert, update or delete — and inside it you get the inserted and deleted pseudo-tables. The big things to know: it's set-based, so a statement affecting 1000 rows fires the trigger once with all 1000 rows, and writing a cursor in there kills performance. And it runs inside the original transaction, so a slow trigger slows every write. I use them sparingly — audit logging is the usual case — because business logic hidden in a trigger is logic nobody remembers is there. For auditing I'd lean toward temporal tables or CDC now.
AFTERtriggers run after the DML completes;INSTEAD OFtriggers replace it (common on views).- Set-based: updating 1000 rows fires the trigger once with 1000 rows in
inserted— a per-row cursor inside a trigger is a performance disaster. - They run in the caller's transaction — a slow or failing trigger blocks or rolls back the original statement.
- Legitimate uses: audit trails, enforcing a complex invariant the schema can't, maintaining a denormalized column. Even then, many teams prefer temporal tables, change data capture, or application-layer logic for visibility.
- Nested/recursive triggers and multiple triggers per action make debugging hard.