We have a recursive trigger on a table that executes AFTER INSERT. There is a check to ensure the trigger does not recurse more than 32 levels deep:
IF(SELECT TRIGGER_NESTLEVEL()) >= 31 RETURN
When I insert a row into this table using SSMS, the statement appears to execute as a batch and the trigger is executed successfully. All of the changes the trigger performed were correct.
However, when I insert a row in the table using a web app (the ORM we are using is called Dapper) the statement gets parameterized and executed via sp_executesql and the following exception is thrown:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Both statements were executed using the same data and on the same machine (local instance of SQL Server 2012 Express). Recursive triggers have been enabled on the target database. I can also verify that my test data is nowhere near 32 levels deep.
From what I have read here https://stackoverflow.com/questions/21265979/dapper-calls-sp-executesql-when-i-have-parameters-is-there-a-way-around-that it appears that the call to sp_executesql is an ADO.NET behaviour and not directly controlled by Dapper. I can verify that I get the same error when using LINQ 2 SQL and EF to insert a row in my table.
I have performed the same tests and received the same results on an instance of SQL Azure.
Would anybody have an idea why the trigger would throw an error when called using sp_executesql?