Nested Triggers
Yesterday a co-worker and I were having a SQL problem. We were in a circumstance where we wanted two distinct UPDATE triggers to fire on the same table. We could have put all of the trigger code into a single trigger, but the exisiting SQL was already lengthy and complex enough. We thought we'd achieve some clarity by putting the new (unrelated) SQL in a separate trigger.
We encountered an issue though where an UPDATE caused Trigger1 to fire, which in turn caused Trigger2 to fire. When Trigger1 returned, then Trigger2 fired again. After trying all manner of different ways of trying to prevent this cascade effect, I found this article (requires sqlservercentral.com membership) and this SQL:
EXEC sp_configure 'nested triggers', 0
GO
RECONFIGURE
GO