Thursday, May 8, 2014

Allow trigger to fire others in SQL Server

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'


No comments: