Allow trigger to fire others
SQL Server allows nested triggers upto 32 levels, Usually nested triggers achieves to perform a series of tasks in a transaction. An event initiates a trigger which can start firing second trigger and so on. Because this trigger is handling within a transaction, a failure at any level causes entire transaction to be roll back. If any trigger causes infinite loop as a safe measure if it exceeds maximum nested level it will automatically terminated.
To Allow triggers to fire other triggers
On SSMS – Right click on server name – properties – Advanced – Allow trigger to fire others – True / False
Using T-SQL
EXEC sys.sp_configure 'nested triggers', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
No comments: