Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
March 24th 2009 14:54
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
Problem
I am trying to resolve an issue with triggers. We use them for things like automatically setting the update date and time on some tables. Recently we added a second trigger to a table and now we are getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)". For the time being we dropped that latest trigger. Can you help us to resolve this?
Solution
As a first step let's review an example which will duplicate your problem. We'll create a single table with two triggers. We will use the following table to test our triggers:
CREATE TABLE dbo.tbl_TriggerTest
(
pk_TriggerTest INT IDENTITY
, Comment NVARCHAR(256)
, TriggerSample NVARCHAR(256)
, Created DATETIME DEFAULT GETDATE()
, Updated DATETIME DEFAULT GETDATE()
)
Now let's create a trigger that sets the Updated column in our table to the current date and time by using the GETDATE() function:
CREATE TRIGGER dbo.tr_TriggerTest_Footprint
ON dbo.tbl_TriggerTest
AFTER UPDATE
AS
UPDATE dbo.tbl_TriggerTest
SET Updated = GETDATE()
FROM dbo.tbl_TriggerTest t
INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest
The above trigger will execute whenever there is an update to the tbl_TriggerTest table. The trigger references the inserted table which is a pseudo-table available to a trigger. An update is really handled as a delete followed by an insert. There is also a deleted pseudo table that is available to a trigger. The deleted table contains the rows that were updated with their values before the update; the inserted table contains the rows that were updated with their new values.
To test the trigger, we will run the following script to insert a row into our sample table then perform an update:
INSERT dbo.tbl_TriggerTest (Comment)
VALUES ('X')
UPDATE dbo.tbl_TriggerTest
SET Comment= 'This is a comment'
The above script runs successfully and the trigger updates the Comment column value to 'This is a comment'.
Now we will create another trigger on our table to reproduce the trigger error:
CREATE TRIGGER dbo.tr_TriggerTest_Sample
ON dbo.tbl_TriggerTest
AFTER UPDATE
AS
UPDATE dbo.tbl_TriggerTest
SET TriggerSample = 'updated ' CAST(GETDATE() AS NVARCHAR(20))
FROM dbo.tbl_TriggerTest t
INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest
This trigger updates the TriggerSample column in our table. When we rerun the test script above we get the following error message:
Msg 217, Level 16, State 1, Procedure tr_TriggerTest_Sample, Line 5
Maximum stored procedure, function, trigger, or view nesting level
exceeded (limit 32).
Notice that both triggers are updating the table that causes the triggers to fire. Essentially what is happening is the update statements in the trigger are causing the triggers to fire again and this keeps going until the nesting level is exceeded. The resolution to this problem is to check the nesting level in the trigger and only execute the update statement one time. To do this you can use the function TRIGGER_NESTLEVEL.
Alter both of the triggers as follows:
ALTER TRIGGER dbo.tr_TriggerTest_Footprint
ON dbo.tbl_TriggerTest
AFTER UPDATE
AS
BEGIN
IF TRIGGER_NESTLEVEL() > 1
RETURN
UPDATE dbo.tbl_TriggerTest
SET Updated = GETDATE()
FROM dbo.tbl_TriggerTest t
INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest
END
GO
ALTER TRIGGER dbo.tr_TriggerTest_Sample
ON dbo.tbl_TriggerTest
AFTER UPDATE
AS
BEGIN
IF TRIGGER_NESTLEVEL() > 1
RETURN
UPDATE dbo.tbl_TriggerTest
SET TriggerSample = 'updated ' CAST(GETDATE() AS NVARCHAR(20))
FROM dbo.tbl_TriggerTest t
INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest
END
GO
If you run the above test script again, you will see that the error is now resolved. The change to the above triggers is to test the nesting level and only execute the code in the trigger when the nesting level is 1. The nesting level will be 1 the first time the trigger is called as a result of an update statement. Each successive time the trigger is called as a result of the same update statement, the nesting level will increment by 1.
There is another way to resolve this problem. The SQL Server configuration option "nested triggers" determines whether an action performed in a trigger can cause the trigger to fire again. The default value for "nested triggers" is 1, which allows the behavior we saw in our sample. You could change "nested triggers" to zero and disable trigger nesting.
Problem
I am trying to resolve an issue with triggers. We use them for things like automatically setting the update date and time on some tables. Recently we added a second trigger to a table and now we are getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)". For the time being we dropped that latest trigger. Can you help us to resolve this?
Solution
As a first step let's review an example which will duplicate your problem. We'll create a single table with two triggers. We will use the following table to test our triggers:
CREATE TABLE dbo.tbl_TriggerTest
(
pk_TriggerTest INT IDENTITY
, Comment NVARCHAR(256)
, TriggerSample NVARCHAR(256)
, Created DATETIME DEFAULT GETDATE()
, Updated DATETIME DEFAULT GETDATE()
)
Now let's create a trigger that sets the Updated column in our table to the current date and time by using the GETDATE() function:
CREATE TRIGGER dbo.tr_TriggerTest_Footprint
ON dbo.tbl_TriggerTest
AFTER UPDATE
AS
UPDATE dbo.tbl_TriggerTest
SET Updated = GETDATE()
FROM dbo.tbl_TriggerTest t
INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest
The above trigger will execute whenever there is an update to the tbl_TriggerTest table. The trigger references the inserted table which is a pseudo-table available to a trigger. An update is really handled as a delete followed by an insert. There is also a deleted pseudo table that is available to a trigger. The deleted table contains the rows that were updated with their values before the update; the inserted table contains the rows that were updated with their new values.
To test the trigger, we will run the following script to insert a row into our sample table then perform an update:
INSERT dbo.tbl_TriggerTest (Comment)
VALUES ('X')
UPDATE dbo.tbl_TriggerTest
SET Comment= 'This is a comment'
The above script runs successfully and the trigger updates the Comment column value to 'This is a comment'.
Now we will create another trigger on our table to reproduce the trigger error:
CREATE TRIGGER dbo.tr_TriggerTest_Sample
ON dbo.tbl_TriggerTest
AFTER UPDATE
AS
UPDATE dbo.tbl_TriggerTest
SET TriggerSample = 'updated ' CAST(GETDATE() AS NVARCHAR(20))
FROM dbo.tbl_TriggerTest t
INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest
This trigger updates the TriggerSample column in our table. When we rerun the test script above we get the following error message:
Msg 217, Level 16, State 1, Procedure tr_TriggerTest_Sample, Line 5
Maximum stored procedure, function, trigger, or view nesting level
exceeded (limit 32).
Notice that both triggers are updating the table that causes the triggers to fire. Essentially what is happening is the update statements in the trigger are causing the triggers to fire again and this keeps going until the nesting level is exceeded. The resolution to this problem is to check the nesting level in the trigger and only execute the update statement one time. To do this you can use the function TRIGGER_NESTLEVEL.
Alter both of the triggers as follows:
ALTER TRIGGER dbo.tr_TriggerTest_Footprint
ON dbo.tbl_TriggerTest
AFTER UPDATE
AS
BEGIN
IF TRIGGER_NESTLEVEL() > 1
RETURN
UPDATE dbo.tbl_TriggerTest
SET Updated = GETDATE()
FROM dbo.tbl_TriggerTest t
INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest
END
GO
ALTER TRIGGER dbo.tr_TriggerTest_Sample
ON dbo.tbl_TriggerTest
AFTER UPDATE
AS
BEGIN
IF TRIGGER_NESTLEVEL() > 1
RETURN
UPDATE dbo.tbl_TriggerTest
SET TriggerSample = 'updated ' CAST(GETDATE() AS NVARCHAR(20))
FROM dbo.tbl_TriggerTest t
INNER JOIN inserted i ON t.pk_TriggerTest = i.pk_TriggerTest
END
GO
If you run the above test script again, you will see that the error is now resolved. The change to the above triggers is to test the nesting level and only execute the code in the trigger when the nesting level is 1. The nesting level will be 1 the first time the trigger is called as a result of an update statement. Each successive time the trigger is called as a result of the same update statement, the nesting level will increment by 1.
There is another way to resolve this problem. The SQL Server configuration option "nested triggers" determines whether an action performed in a trigger can cause the trigger to fire again. The default value for "nested triggers" is 1, which allows the behavior we saw in our sample. You could change "nested triggers" to zero and disable trigger nesting.
| 61 |
| Vote |
Add To: 
