9

Using SQL Server 2012, is it possible to have nested triggers enabled at the server/database level but to disable nesting on one specific table/trigger?

GMB
  • 216,147
  • 25
  • 84
  • 135
Jim
  • 323
  • 2
  • 12
  • IMO, nested triggers are usually the wrong design. Hard (practically impossible) to debug, hard to maintain. Do you have an XY problem? – Mitch Wheat Mar 03 '19 at 02:48

1 Answers1

11

I think that you should be able to achieve your purpose by using the TRIGGER_NESTLEVEL() function, which is available since SQL Server 2008:

Returns the number of triggers executed for the statement that fired the trigger. TRIGGER_NESTLEVEL is used in DML and DDL triggers to determine the current level of nesting.

You could alter the code of the relevant trigger to add the following statement just after the BEGIN keyword. The trick is to dynamically compute the object_id of the current trigger, which TRIGGER_NESTLEVEL() expects as first argument:

IF TRIGGER_NESTLEVEL(
    ( SELECT object_id FROM sys.triggers WHERE name = 'MyTrigger' )
) > 1 RETURN

This will actually prevent the given trigger to execute recursively.

Another option is to use TRIGGER_NESTLEVEL() without arguments. This returns the number of times all triggers have been executed for the statement. My understanding of your use case is that the first solution should be what you need.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Indeed, this works, is an elegant solution and even allows to control the depth of nested triggering if it's required. – Sebastian Zartner Mar 04 '19 at 08:23
  • 1
    @SebastianZartner: indeed... See [this post](https://www.google.fr/amp/s/clivesyabb.com/2014/08/06/avoiding-recursive-triggers-in-sql-vs-avoiding-nested-execution/amp/) for a full testing scenario. – GMB Mar 04 '19 at 08:27
  • More concise expression for the ID would be `OBJECT_ID('my_trigger', 'TR' )` – andowero May 28 '21 at 17:09
  • The best statement would IMHO be `TRIGGER_NESTLEVEL(@@PROCID)`. No need to call any other function. – andowero Dec 28 '21 at 14:10