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