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

Tags: , ,

3 Responses to “Trigger recursion – Prevent triggers firing themselves in SQL Server 2008 R2”

  1. kilren says:

    you should use :
    IF trigger_nestlevel() > 1

  2. Stephen says:

    I disagree Kilren. It should be trigger_nestlevel() > 0 otherwise trigger will be called twice instead of once.

  3. Markus says:

    When no parameters are specified, TRIGGER_NESTLEVEL returns the total number of triggers on the call stack. This includes itself.
    http://msdn.microsoft.com/en-us/library/ms182737.aspx
    This means that trigger_nestlevel always returns at least 1 when called from a trigger. Thus, trigger_nestlevel() > 0 is always true inside a trigger which would cause it never to run.
    IF trigger_nestlevel() > 1
    is correct.

Leave a Reply