How do I solve "INSTEAD OF triggers do not support recursion"

My client has an ASP.NET website that is connected to an MSSQL 2005 database hosted on Godaddy. A few days or weeks ago, Godaddy changed the server to a different one and we had to modify the connection string to connect to the server. Today, the client noticed that he cannot view the orders online and instead he gets a message. I turned on message details, and was able to view the error (see below).

I know about basic MSSQL, but I have never heard of INSTEAD OF and I do not use triggers or know why they would suddenly fail to work.

Therefore, I am stuck:
1. I do not know in which procedure, function, or query this INSTEAD OF is found. I looked manually, but did not find anything (I can only connec via the GoDaddy MSSQL web interface).
2. Even if I find the procedure, function or query that contains the INSTEAD OF code, I do not know what to do to correct the situation.

If you have any advice on this situation, I would appreciate it.

Thank you.


Server Error in '/' Application.
INSTEAD OF triggers do not support direct recursion. The trigger execution failed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: INSTEAD OF triggers do not support direct recursion. The trigger execution failed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:
[SqlException (0x80131904): INSTEAD OF triggers do not support direct recursion. The trigger execution failed.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950906
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846891
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   Excis.Stam.Business.SqlHelper.ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters) +123
   Excis.Stam.Business.SqlHelper.ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, String commandText) +48
   Excis.Stam.Business.DataAccessor.ExecuteNonQuery(String procedureName) +177
   Excis.Stam.Business.Order.CleanOrders() +29
   Excis.Stam.Admin.orders.OnLoad(EventArgs e) +63
   System.Web.UI.Control.LoadRecursive() +50
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

Open in new window


Version Information: Microsoft .NET Framework Version:2.0.50727.3662; ASP.NET Version:2.0.50727.3668
Lev SeltzerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
we would need the trigger code, to see the issue eventually there.
what was the db compatibility before, and what is it now?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
BTW: the "instead of trigger" is attached to the view that the application is doing a UPDATE/INSERT/DELETE against
0
Deepak ChauhanSQL Server DBACommented:
You can find trigger using below query

use <databaseName>
go
-- This query will show you Instead Of Trigger.
select name as trigName, OBJECT_NAME(parent_id) as TableOrViewName, type, is_instead_of_trigger from sys.triggers where is_instead_of_trigger=1

Once you found the trigger name execute the below code to see the trigger action.

execute sp_helptext 'InsteadTrigger'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Lev SeltzerAuthor Commented:
I found two instances of the "instead of" trigger, as listed in the CSV file below:

trigName,TableOrViewName,type,is_instead_of_trigger
trig_insteadOfDel_orderDetail,orderDetail,TR,true
trig_insteadOfDel_orderHeader,orderHeader,TR,true

I then ran the query:
execute sp_helptext 'trig_insteadOfDel_orderDetail'  
execute sp_helptext 'trig_insteadOfDel_orderHeader'

Words were display after this. No errors.
I am not sure what to do.

Someone else programmed this, and until today, it was working (it is possible that the client did not go to the admin panel for a  month and that the error started when Godaddy moved servers. But it was definitely working last year when I last checked).
0
Lev SeltzerAuthor Commented:
the results of
execute sp_helptext 'trig_insteadOfDel_orderHeader'
are:
CREATE TRIGGER trig_insteadOfDel_orderHeader  
ON dbo.orderHeader INSTEAD OF DELETE  
AS  
SET NOCOUNT ON  
 
DELETE FROM dbo.orderDetail  
FROM dbo.orderDetail a  
INNER JOIN deleted b ON b.orderId=a.orderID  
 
DELETE FROM dbo.orderHeader  
FROM dbo.orderHeader a  
INNER JOIN deleted b ON b.orderId=a.orderID

Open in new window

0
Lev SeltzerAuthor Commented:
the results of
execute sp_helptext 'trig_insteadOfDel_orderDetail'  
are:
CREATE TRIGGER trig_insteadOfDel_orderDetail  
ON dbo.orderDetail INSTEAD OF DELETE  
AS  
SET NOCOUNT ON  
 
DELETE FROM dbo.OrderDetailTefillinBagOptions  
FROM dbo.OrderDetailTefillinBagOptions a  
INNER JOIN deleted b ON b.orderDetailId=a.orderDet (...) 
 
DELETE FROM dbo.orderDetailTefillinOptions  
FROM dbo.orderDetailTefillinOptions a  
INNER JOIN deleted b ON b.orderDetailId=a.orderDet (...) 
 
DELETE FROM dbo.orderDetailTorahCoverOptions  
FROM dbo.orderDetailTorahCoverOptions a  
INNER JOIN deleted b ON b.orderDetailId=a.orderDet (...) 
 
DELETE FROM dbo.orderDetailTorahRollerOptions  
FROM dbo.orderDetailTorahRollerOptions a  
INNER JOIN deleted b ON b.orderDetailId=a.orderDet (...) 
 
DELETE FROM dbo.orderDetail  
FROM dbo.orderDetail a  
INNER JOIN deleted b ON b.orderDetailId=a.parentOr (...) 
 
DELETE FROM dbo.orderDetail  
FROM dbo.orderDetail a  
INNER JOIN deleted b ON b.orderDetailId=a.orderDet (...)

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the first trigger is indeed deleting from the same "view" as the trigger is defined on, which is visibly the problem (at least at first looking.
However I also see that the syntax should be slightly different in any case, replacing the table name in the delete by the alias.

CREATE TRIGGER trig_insteadOfDel_orderHeader  
ON dbo.orderHeader INSTEAD OF DELETE  
AS  
SET NOCOUNT ON  
 
DELETE FROM a
FROM dbo.orderDetail a  
INNER JOIN deleted b ON b.orderId=a.orderID  
 
DELETE FROM a
FROM dbo.orderHeader a  
INNER JOIN deleted b ON b.orderId=a.orderID 

Open in new window


however, the main issue to me is that the instead of triggers are to be created on views, and should then run the action towards the tables.
please check and repor if orderHeader and orderDetail are tables or views
0
Faheem ShaikhCommented:
To Check the status of the recursive triggers:
EXEC sp_dboption '<name of db>', 'recursive triggers'

Use this command to turn them:
EXEC sp_dboption '<name of db>', 'recursive triggers', 'true'

Use this command to turn them off:
EXEC sp_dboption '<name of db>', 'recursive triggers', 'false'

It is important for you to understand the business logic you are trying to implement here which will help you to either re write the trigger logic completely or it may justify having the instead of triggers firing recursively.
Since you mentioned that your servers were changed, may be the option for recursive trigger was not set to true on the new server, so just by running above command your problem could be solved. But beware, its always better to understand the logic behind your code before making any changes. It might have adverse effects on other sections of your code.
0
Lev SeltzerAuthor Commented:
Thank you all for your advice. Here is what I have found:
1. The triggers are on the tables, not the views.
2. I could not easily put tables on views, as the views do not exist for these tables and this would also require modifying the ASP.NET code.
3. The recursive triggers were OFF. I turned them back on using faheem's command.j
4. The program did NOT work even after turning on recursive triggers. I still received an error "INSTEAD OF triggers do not support direct recursion. The trigger execution failed. "
5. I agree that it would be best to understand the logic of the code. But it is old code written by someone else, and has not been modified in years. I have tried to get the client to abandon it and purchase a shopping cart from a different supplier, but to no avail.

Any suggestions on how to proceed?

Thank you.
0
Faheem ShaikhCommented:
Are Nested Triggers enabled on your new server? By default they should be. But if not then you will need to enable them. The recursive Trigger setting does not take effect unless Nested Triggers are enabled. Also note this setting works at the server level unlike Recursive Trigger setting that works at DB level.

Check status of Nested Triggers:

EXEC sp_configure 'nested triggers'
If the run_value is 0 then the nested triggers are off, if the value is 1 then they are on.

Therefore, to turn on nested triggers, type the following command:

EXEC sp_configure 'nested triggers', 1
RECONFIGURE

To turn them off, type the following:

EXEC sp_configure 'nested triggers', 0
RECONFIGURE
0
Lev SeltzerAuthor Commented:
Nested triggers returns a "1" for the run value, so it seems to be on.
That is good, because your commands to turn it on failed due to my security level being too low.
But the program still fails when I try to look at orders...
0
Faheem ShaikhCommented:
Not sure if a restart will help. I don't have any further suggestions. :(
0
Lev SeltzerAuthor Commented:
Thank you for your help. I do not know the source of the problem, and we are now in the process of moving the database to a new server.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.