SQL Server Trigger Best Practices: Why Triggers Are Invisible, and When to Avoid Them

Triggers are powerful precisely because they’re automatic — which is also exactly why they need to be used deliberately, not as a default habit. This closing lesson of the chapter is about judgment: having built both DML and DDL/logon triggers, here’s when that power is worth the tradeoff and when it isn’t.

Four Real Pitfalls

Recursive Triggers, Concretely

-- A trigger on Account that updates Account itself can re-fire the same trigger,
-- if RECURSIVE_TRIGGERS is on for the database (off by default):
ALTER DATABASE CURRENT SET RECURSIVE_TRIGGERS OFF; -- the safe default

-- Even with recursion off, an INDIRECT loop is still possible:
-- trigger on Account updates Order → trigger on Order updates Account → fires the first trigger again
-- RECURSIVE_TRIGGERS OFF only blocks DIRECT self-triggering, not this indirect cycle

The Biggest Philosophical Pitfall

A developer reading application code that runs a plain UPDATE has no way to know a trigger will also fire, unless they separately go check the database schema. A stored procedure call, by contrast, is a visible, greppable, explicit decision to invoke specific logic — anyone reading the call site immediately knows exactly what runs. This invisibility is triggers’ single biggest real-world cost, independent of performance.

-- A genuinely good way to discover what triggers exist on a table you've inherited:
SELECT name, is_disabled, OBJECT_DEFINITION(object_id) AS definition
FROM sys.triggers WHERE parent_id = OBJECT_ID('dbo.Account');

When Triggers Are Still the Right Call

Use triggers when you genuinely need guaranteed enforcement regardless of write path — auditing (Lesson 2’s DDL example), cross-table integrity that CHECK constraints can’t express (Chapter 5), or a rule that must apply even to ad-hoc scripts run directly by a DBA, bypassing any application or stored procedure entirely. Avoid them for things a stored procedure or application layer could handle just as reliably, and far more visibly, since “guaranteed no matter what” is the specific property that justifies accepting the invisibility tradeoff — don’t pay that cost for a rule nothing will ever actually bypass.