# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

## ravi

Hello,

I am running this query
  &#34;delete from ims_domains where id=61&#34;
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

----------


## MAK

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
  &#34;delete from ims_domains where id=61&#34;
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

----------


## ravi

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
  &#34;delete from ims_domains where id=61&#34;
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

----------


## MAK

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
  &#34;delete from ims_domains where id=61&#34;
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

----------


## Ravi

Mak,
No , those 8 table doesn&#39;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 (&#39;You can only delete one domain at a time.&#39;, 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
  &#34;delete from ims_domains where id=61&#34;
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

----------


## MAK

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&#39;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 (&#39;You can only delete one domain at a time.&#39;, 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
  &#34;delete from ims_domains where id=61&#34;
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

----------


## marumugam

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

----------


## rmiao

How many data rows involved in this process?

----------

