All 7 Triggers questions
More in Triggers
How do you access the old and new values of a row that was just updated from inside a trigger? Why should triggers generally be designed to handle multiple affected rows at once rather than assuming only a single row was changed? What are some alternatives to using a trigger for tasks like auditing or enforcing business rules, and when might those alternatives be preferable? What are the performance implications of using triggers heavily on a busy, high transaction volume table?
Advertisement
Back to results for “Error Handling”
SQL Server › Triggers

How do nested and recursive triggers work in SQL Server, and what precautions should be taken to avoid unexpected behavior or infinite loops?

Advanced

Answer

A trigger can cause another trigger to fire, called nesting, and a trigger can even cause itself to fire again indirectly, called recursion, both of which are controlled by server level configuration settings, and without careful design, these situations can lead to infinite loops or deeply nested, hard to debug chains of triggers firing across multiple tables.
-- Check current nested trigger settings
SELECT SERVERPROPERTY('NestedTriggers');

-- Recursive triggers must be explicitly enabled
ALTER DATABASE MyDatabase SET RECURSIVE_TRIGGERS ON;
Real-world example A team debugging an unexpected infinite loop discovers a trigger on one table was updating a second table, which in turn had its own trigger updating back to the first table, creating an unintended recursive chain that needed to be redesigned.

Common follow-ups: How do you safely test for potential infinite trigger loops before deploying to production?;What is a reasonable maximum nesting level SQL Server allows for triggers?

Error Handling with TRY CATCH;Query Optimization & Plans
All 7 Triggers questions