How to disable nesting of triggers at table or trigger level in SQLServer?

Question

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?

Sum

IMO, nested triggers are usually the wrong design. Hard (practically impossible) to debug, hard to maintain. Do you have an XY problem? – Erskine

Answer 1

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.

Mackinaw

Indeed, this works, is an elegant solution and even allows to control the depth of nested triggering if it's required. – Bainter

@SebastianZartner: indeed... See this post for a full testing scenario. – Mackinaw

More concise expression for the ID would be OBJECT_ID('my_trigger', 'TR' ) – Stepaniestepbrother

The best statement would IMHO be TRIGGER_NESTLEVEL(@@PROCID). No need to call any other function. – Stepaniestepbrother