Nested Trigger and Nested Trigger Limit

Trigger:
A set of statements that executes automatically whenever any operation on a specified table is performed to modify data.
Nested Trigger:
A trigger can be nested or recursive in nature. When a statement in a trigger fires same or another trigger then a trigger is called a Nested/ Recursive trigger. A very simple example of a nested trigger is as:
Let's we have a simple table 'TableXYZ ' with following structure:
Id int
Description varchar(50)
And we have a simple 'after insert' trigger as:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:	<Author,,Name>

-- Create date: <Create Date,,>

-- Description:	<Description,,>

-- =============================================

CREATE TRIGGER trgNested 

   ON  TableXYZ 

   AFTER INSERT

AS 

BEGIN



	SET NOCOUNT ON;



	INSERT INTO TableXYZ(Description) SELECT Description FROM INSERTED



END

GO

Here the trigger fires after insert operation and a statement within trigger itself performs INSERT operation which leads it to infinite loop. SQL server supports nested trigger up to 32 levels so this trigger will fire with error message
'Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) .'

We can set the nesting of triggers on database level with the help of following statement:

ALTER DATABASE  nameofdatabase  SET RECURSIVE_TRIGGERS OFF

We can also restrict nesting/ recursion of trigger up to certain level with the help of following statement:

	IF ((SELECT TRIGGER_NESTLEVEL()) > 10 )

RETURN

Best Regards
Lakhwinder Ghuman