Error when trying to delete

  • 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?

  • 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?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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!

  • 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.)

  • Also, is there a DELETE trigger on this table: StudentGroup?

  • 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.

  • 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.

  • ok. the Code for this student id is active

  • DBA-640728 (1/22/2010)


    ok. the Code for this student id is active

    And that is what??

  • 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