Results 1 to 8 of 8

Thread: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

  1. #1
    ravi Guest

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

    Hello,

    I am running this query
    "delete from ims_domains where id=61"
    and got the error
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)


    Please let me know what should be the reason?
    Thanks,
    Ravi

  2. #2
    MAK Guest

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) (reply)

    Do u have a delete trigger on that table thats deletes record from another table which inturn fires this table again or something like that?

    -MAK


    ------------
    ravi at 5/29/2002 2:48:38 PM

    Hello,

    I am running this query
    "delete from ims_domains where id=61"
    and got the error
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)


    Please let me know what should be the reason?
    Thanks,
    Ravi

  3. #3
    ravi Guest

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) (reply)

    Mak,
    I do have delete trigger. But this table PK is refering only 8 table. As we know the nested trigger allowed maximum 32 only. How I can sovle this prbolem?

    ravi



    ------------
    MAK at 5/29/2002 2:55:39 PM

    Do u have a delete trigger on that table thats deletes record from another table which inturn fires this table again or something like that?

    -MAK


    ------------
    ravi at 5/29/2002 2:48:38 PM

    Hello,

    I am running this query
    "delete from ims_domains where id=61"
    and got the error
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)


    Please let me know what should be the reason?
    Thanks,
    Ravi

  4. #4
    MAK Guest

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) (reply)

    r those 8 table has triggers on it which fires many triggers?


    ------------
    ravi at 5/29/2002 3:10:38 PM

    Mak,
    I do have delete trigger. But this table PK is refering only 8 table. As we know the nested trigger allowed maximum 32 only. How I can sovle this prbolem?

    ravi



    ------------
    MAK at 5/29/2002 2:55:39 PM

    Do u have a delete trigger on that table thats deletes record from another table which inturn fires this table again or something like that?

    -MAK


    ------------
    ravi at 5/29/2002 2:48:38 PM

    Hello,

    I am running this query
    "delete from ims_domains where id=61"
    and got the error
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)


    Please let me know what should be the reason?
    Thanks,
    Ravi

  5. #5
    Ravi Guest

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) (reply)

    Mak,
    No , those 8 table doesn't have any trigger. Only the primaru ims_domain has trigger and the scrip is as follows. It was saying msg in line
    (SELECT @DomainID = ID )


    REATE TRIGGER [tDeleteDomain] ON dbo.iMS_Domains
    FOR DELETE
    AS
    IF @@RowCount > 1
    BEGIN
    ROLLBACK TRAN
    RAISERROR ('You can only delete one domain at a time.', 16, 10)
    END

    DECLARE @DomainID int
    SELECT @DomainID = ID
    FROM DELETED
    DELETE FROM iMS_Domains
    WHERE AliasFor = @DomainID



    ------------
    MAK at 5/29/2002 3:43:47 PM

    r those 8 table has triggers on it which fires many triggers?


    ------------
    ravi at 5/29/2002 3:10:38 PM

    Mak,
    I do have delete trigger. But this table PK is refering only 8 table. As we know the nested trigger allowed maximum 32 only. How I can sovle this prbolem?

    ravi



    ------------
    MAK at 5/29/2002 2:55:39 PM

    Do u have a delete trigger on that table thats deletes record from another table which inturn fires this table again or something like that?

    -MAK


    ------------
    ravi at 5/29/2002 2:48:38 PM

    Hello,

    I am running this query
    "delete from ims_domains where id=61"
    and got the error
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)


    Please let me know what should be the reason?
    Thanks,
    Ravi

  6. #6
    MAK Guest

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) (reply)

    u r deleting rows from the same table in your delete trigger. thats why,

    either disable nested triggers or handle this delete differently.


    ------------
    Ravi at 5/29/2002 4:20:35 PM

    Mak,
    No , those 8 table doesn't have any trigger. Only the primaru ims_domain has trigger and the scrip is as follows. It was saying msg in line
    (SELECT @DomainID = ID )


    REATE TRIGGER [tDeleteDomain] ON dbo.iMS_Domains
    FOR DELETE
    AS
    IF @@RowCount > 1
    BEGIN
    ROLLBACK TRAN
    RAISERROR ('You can only delete one domain at a time.', 16, 10)
    END

    DECLARE @DomainID int
    SELECT @DomainID = ID
    FROM DELETED
    DELETE FROM iMS_Domains
    WHERE AliasFor = @DomainID



    ------------
    MAK at 5/29/2002 3:43:47 PM

    r those 8 table has triggers on it which fires many triggers?


    ------------
    ravi at 5/29/2002 3:10:38 PM

    Mak,
    I do have delete trigger. But this table PK is refering only 8 table. As we know the nested trigger allowed maximum 32 only. How I can sovle this prbolem?

    ravi



    ------------
    MAK at 5/29/2002 2:55:39 PM

    Do u have a delete trigger on that table thats deletes record from another table which inturn fires this table again or something like that?

    -MAK


    ------------
    ravi at 5/29/2002 2:48:38 PM

    Hello,

    I am running this query
    "delete from ims_domains where id=61"
    and got the error
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)


    Please let me know what should be the reason?
    Thanks,
    Ravi

  7. #7
    Join Date
    Aug 2005
    Posts
    4

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32

    I have writen a Function which call's the same function it self. I'm getting the error as below.

    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    Can any one give me a solution for this problem I have attached the function also.

    CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
    RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
    AS
    BEGIN
    IF (@IncludeParent=1)
    BEGIN
    INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
    END
    DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
    DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
    SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
    OPEN RetrieveReports
    FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
    INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
    FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
    END
    CLOSE RetrieveReports
    DEALLOCATE RetrieveReports

    RETURN
    END

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    How many data rows involved in this process?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •