January 22, 2010 at 9:52 am
hi I am working on a script that creates new Students. When I am trying to delete a student In my script
that I created in Student table, I receive the error message below:
Msg 8621, Level 17, State 2, Procedure Student_InsteadOf_Del_Trg, Line 308
The query processor ran out of stack space during query optimization. Please simplify the query.
I am trying to run a very simple query:
DELETE FROM Student WHERE StudentID = 8776555;
I disabled the trigger, but I did not work out. Can someone help me out why this is happening?
January 22, 2010 at 11:37 am
If you still get the same error that basically means that you the procedure "Student_InsteadOf_Del_Trg" is stilled called (maybe by another trigger?)
Just being curious (I noticed line #308 for the root cause of the error): What does that trigger actually do?
January 22, 2010 at 2:33 pm
yes you are right there are two triggers.
one is Student_InsteadOf_Del_Trg
INSTEAD OF Delete
NOT FOR REPLICATION
As.....
where line 308 is
DELETE Student FROM Students s INNER JOIN Deleted d ON s.StudentID = d.StudentID
then
another trigger
ALTER Trigger [dbo].[Student_Del_trg]
On [dbo].[Students]
For Delete
NOT FOR REPLICATION
As
If exists(Select Code From Deleted Where Code = 1)
Begin
ROLLBACK TRAN
RAISERROR ( 'Cannot remove Student', 16, 10 )
End
ELSE
Delete StudentGroup
Where StudentID in (Select StudentID From Deleted)
help!
January 22, 2010 at 2:41 pm
DBA-640728 (1/22/2010)
yes you are right there are two triggers.one is Student_InsteadOf_Del_Trg
INSTEAD OF Delete
NOT FOR REPLICATION
As.....
where line 308 is
DELETE Student FROM Students s INNER JOIN Deleted d ON s.StudentID = d.StudentID
then
another trigger
ALTER Trigger [dbo].[Student_Del_trg]
On [dbo].[Students]
For Delete
NOT FOR REPLICATION
As
If exists(Select Code From Deleted Where Code = 1)
Begin
ROLLBACK TRAN
RAISERROR ( 'Cannot remove Student', 16, 10 )
End
ELSE
Delete StudentGroup
Where StudentID in (Select StudentID From Deleted)
help!
What is the value of the Code column in the records for the students you are attempting to delete? (Run a simple select on the table of the ones you want to delete.)
January 22, 2010 at 2:43 pm
Also, is there a DELETE trigger on this table: StudentGroup?
January 22, 2010 at 2:59 pm
yes there is another trigger in StudentGroup for delete. But i don't have any records in this table.
i disabled the two triggers (delete and instead of delete) in my Student's table and when i try to delete now i get this:
Msg 8621, Level 17, State 2, Line 3
The query processor ran out of stack space during query optimization. Please simplify the query.
January 22, 2010 at 3:04 pm
DBA-640728 (1/22/2010)
yes there is another trigger in StudentGroup for delete. But i don't have any records in this table.i disabled the two triggers (delete and instead of delete) in my Student's table and when i try to delete now i get this:
Msg 8621, Level 17, State 2, Line 3
The query processor ran out of stack space during query optimization. Please simplify the query.
Doesn't matter if you have records in the other table, when the DELETE statment in the trigger on the Student table is executed the trigger on the StudentGroup table will still fire even if there are no records being deleted.
January 22, 2010 at 3:25 pm
ok. the Code for this student id is active
January 22, 2010 at 3:34 pm
DBA-640728 (1/22/2010)
ok. the Code for this student id is active
And that is what??
January 22, 2010 at 4:09 pm
which is 0 . i have seen that this table has 800 constraints in that table. I have another instance with the exact db and it does not give the error. The only difference i see is that the one that gives me the error is 32 bit and the one that doesn't is 64 bit, can this be the problem?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply