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.SqlE
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:
Version Information: Microsoft .NET Framework Version:2.0.50727.3662; ASP.NET Version:2.0.50727.3668
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.SqlE
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) +627Version Information: Microsoft .NET Framework Version:2.0.50727.3662; ASP.NET Version:2.0.50727.3668
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the results of
execute sp_helptext 'trig_insteadOfDel_orderHe
are:
execute sp_helptext 'trig_insteadOfDel_orderHe
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.orderIDASKER
the results of
execute sp_helptext 'trig_insteadOfDel_orderDe
are:
execute sp_helptext 'trig_insteadOfDel_orderDe
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 (...)SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
trigName,TableOrViewName,t
trig_insteadOfDel_orderDet
trig_insteadOfDel_orderHea
I then ran the query:
execute sp_helptext 'trig_insteadOfDel_orderDe
execute sp_helptext 'trig_insteadOfDel_orderHe
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).