Hello,I work with an ASP.Net Project which uses a SQL Server 2008 database, but it was originally written for an SQL Server 2005 database. There is implemented a trigger for a certain Update-Statement. At runtime this Update-Statement always rolls back with the following error message:

INSTEAD OF triggers do not support direct recursion. The trigger execution failed.

Searching for this error I got some hints to cope with that, but they all failed to work. I tried the following:

EXEC sp_configure 'nested triggers', 1RECONFIGURE

EXEC sp_dboption '<name of db>', 'recursive triggers', 'true'

In addition I set the compatibility level of my DB to SQL Server 2005 (and tried to set it to 100 as well):

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 90

But the error message occurs nevertheless.Do you have any suggestions to me how I can handle this? I don’t want to edit the trigger, since it is a bit complex and I’m new to triggers in SQL.

So this is the expected behavior. What you want to find out is whether there really is a need for a recursive instead of trigger. If there is, you will need to dig through the logic to figure out how else the logic can be implemented.

But if you don't need to fire the recursive instead of triggers, then one option would be to disable recursive triggers. I would be very careful doing this if it that option is currently enabled for fear that you may break some existing functionality.

Read through that MSDN link that I posted - it has some very useful information about the two options (nested triggers and recursive triggers).