3

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?

  • Did you make any progress on this issue? If not, maybe you can reactivate this question by adding sample table DDL + trigger code that reproduces the behavior. – Gert Arnold Jan 26 at 18:44
  • Unfortunately not @GertArnold - our solution (from memory) given the time constraints - was to refactor our code to avoid using recursive triggers, which was a shame because it made our middleware logic a bit less elegant. – Boris Jan 28 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.