NESTED_TRIGGERS

Question

Hello everyone,

I am trying to disable indirect recursion in my SQL Server database.
But I face some problems.

Indeed, I can execute this query without any problem:

ALTER DATABASE SRV  
SET RECURSIVE_TRIGGERS OFF  
GO  

But not :

ALTER DATABASE SRV  
SET NESTED_TRIGGERS OFF  
GO  

Because I get this error message:

Incorrect syntax near 'NESTED_TRIGGERS'.

Looking a little closer at the Microsoft help, I see that the CONTAINMENT option must be set to PARTIAL.

When I try to change this configuration :

ALTER DATABASE SRV_ARBER  
SET CONTAINMENT = PARTIAL  
GO  

I face this problem:

ALTER DATABASE statement failed; this functionality is not available in the current edition of SQL Server.

How to make my database not execute triggers inside triggers?

I thank you for your help and wish you a good day.

Note :

My @@Version : Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 29 2021 13:52:20 Copyright (C) 2019 Microsoft Corporation

Answer 1

The answer is that you need to check trigger_nestlevel in your triggers. That is, you need this all your triggers:

IF trigger_nestlevel() > 1
    RETURN

The configuration option "nested triggers" is a very old server configuration option. it is available for partially contained databases, since the idea with a contained databases is that you should be able to move it from one server to another without being dependent on server settings. Therefore they made server options that affect code execution database options, but only for contained databases.

Ironically, there is no type of database that is more contained than Azure SQL Database, but there they did not add these options to Azure SQL DB.

I would like you to think a second time if you really want to do this on a general level. I've certainly been in situations where I have had to stop triggers playing ping-pong with each other, but I have had a lot more cases where I needed triggers to nest.