Microsoft SQL Server 2014 Unleashed

Using Recursive Triggers

Nested triggers allow an update on another table modified within a trigger to fire a trigger on that other table. However, if a trigger modifies records in the same table for which the trigger was created, the trigger does not fire again unless the recursive triggers option is turned on. recursive triggers is a database option that is turned off by default.

The first command in the following example checks the setting of recursive triggers for the AdventureWorks2012 database, and the second sets recursive triggers to TRUE:

Click here to view code image

select DATABASEPROPERTYEX ('AdventureWorks2012', 'IsRecursiveTriggersEnabled')ALTER DATABASE AdventureWorks2012 SET RECURSIVE_TRIGGERS ON

If you turn off nested triggers, ...