Trigger recursion – Prevent triggers firing themselves in SQL Server 2008 R2
Trigger recursion can be the cause of very hard to find bugs. Luckily it is easily prevented.
So you’ve written a trigger to fire after an insert or update. Lets take the following example
ALTER TRIGGER trigger1 ON [dbo].[logTable] after INSERT, UPDATE AS BEGIN INSERT [dbo].[logTable] (messageTime) VALUES (getdate()) END
It just logs a date in a table. Unfortunately if we were to add this it would run indefinitely since each execution of the insert statement would cause the trigger to be fired again. Suppose we only want this to fire once? What do we do.
Luckily there is a function built into SQL Server to allow us to restrict recursive calls like this, the trigger_nestlevel(). Simply it tells us the nest level of the call, if the nest level is greater than 0 the call is as a result of recursion.
So lets use this function and add it to our trigger to cause the trigger to exit when it is fired by itself:
ALTER TRIGGER trigger1 ON [dbo].[logTable] after INSERT, UPDATE AS BEGIN IF trigger_nestlevel() > 0 RETURN INSERT [dbo].[logTable] (messageTime) VALUES (getdate()) END