By: Ray Barley | Last Updated: 2009-03-23 | Comments (10) | Triggers
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?
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.
- Take a look at the TRIGGER_NESTLEVEL function in SQL Server Books Online to review the complete details on this useful function.
- There are a number of other MSSQLTips on this site related to triggers; search on TRIGGER and you will get all sorts of useful information.
- Disabling a Trigger for a Specific SQL Statement or Session
- Foreign Key vs. Trigger Referential Integrity in SQL Server
- Forcing Trigger Firing Order in SQL Server
- Trigger Alternatives in SQL Server - OUTPUT Clause
- Using the EventData() Function with DDL triggers in SQL Server 2005
- Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005
- SQL Server 2005 SysAdmin Login Auditing
- Many people are reluctant to use triggers. However, they are useful in some instances and can be used if you are careful.
- Take a look at the sp_configure system stored procedure in Books Online to get the details on changing the "nested trigger" configuration option.
Last Updated: 2009-03-23
About the author
View all my tips