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

Question

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

Krasavita

SSC-Insane

Points: 23138
More actions
April 26, 2011 at 2:01 pm

#389386

I get the following error on the trigger:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
How can I corect it?
Here is my trigger:
ALTER TRIGGER [Name]
ON [dbo].[Employee]
FOR INSERT,Update as
BEGIN
Update dbo.Employee
SET First_Name = ins.Nick_Name
from Employee e, inserted ins
Where e.Emp_Id=ins.Emp_Id AND e.Nick_Name IS NOT NULL
END

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Is that the entirety of the trigger?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

This is what I have on my trigger, I just created, so maybe I didn't created properly

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Is the employee object a view or table?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Krasavita SSC-Insane Points: 23138 More actions · Answer 4

Sorry, I didn't put corectly:

ALTER TRIGGER [Name]

ON [dbo].[Employee]

FOR INSERT,Update as

BEGIN

Update dbo.Employee

SET First_Name = ins.Nick_Name

from Employee e, inserted ins

Where e.Emp_Id=ins.Emp_Id AND e.Nick_Name IS NOT NULL

END

Employee is table

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

do you have other triggers on the employee table?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Krasavita SSC-Insane Points: 23138 More actions · Answer 6

Krasavita

SSC-Insane

Points: 23138

April 26, 2011 at 3:54 pm

#1316287

Yes

3 triggers

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Do those triggers act on other tables that also have triggers?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 8

Is this thread anyhow connected to your previous thread?

If so, why do you post in a SQL2000 forum as well as in a SQ2005 forum? What version do you use?

Would it be an option to add a persisted calculated column to reflect the condition you're looking for or a INSTEAD OF trigger to use the columns of the INSERTED table and a CASE statement?

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]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

I'm wondering if there is a trigger loop occurring. Update employees which updates another table via trigger which comes back to the first table and updates it again - causing the max to be reached because it keeps going around in circles.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 10

Quite possible.

That's one of the reasons I suggested to replace the trigger with a computed column (based on the requirement posted in the other thread). The INSTEAD OF option not resolve the trigger loop though...

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]

Krasavita SSC-Insane Points: 23138 More actions · Answer 11

Guys,

I was able to fix it, there is another trigger which does update and insert on another column and I added my updated statment to that trigger and it worked.

Krasavita SSC-Insane Points: 23138 More actions · Answer 12

Also,

Would it be an option to add a persisted calculated column to reflect the condition you're looking for or a INSTEAD OF trigger to use the columns of the INSERTED table and a CASE statement?

Can you explain how to do it?

Thank you

sachnam SSCrazy Points: 2317 More actions · Answer 13

Your trigger logic is wrong .

If you see you have created a trigger for update on Employee table.

Which means a trigger will fire every time an update occurs on (Employee)

But in the trigger body also you have written an statement which is updating the employee table

So, In this case the trigger will keep on calling itself makes it a recursive trigger with no end limit

or limit to exit hence it will run endlessly breaching the nesting level with 32 (Maximum)

I hope you understood what i am trying to pin point here....

Krasavita SSC-Insane Points: 23138 More actions · Answer 14

This what I have now for my logic for one trigger

UPDATE Employee SET date = i.edate

FROM inserted i

JOIN Employee ON i.id = Employee.id

where i.date is NULL

UPDATE Employee SET name = i.uName

FROM inserted i

JOIN Employee ON i.id = Employee.id

where i.uName IS NOT NULL