October 15, 2008 at 12:24 am
I have one table as follows :
CREATE TABLE [StaticSiteMenu] (
[Menu_Id] [smallint] IDENTITY (1, 1) NOT NULL ,
[MenuType_Id] [tinyint] NOT NULL ,
[BusinessGroup_Id] [tinyint] NULL ,
[Parent_Id] [smallint] NULL ,
[TopLevelMenuId] [smallint] NULL )
And on this [StaticSiteMenu] I have tirgger as follow :
CREATE TRIGGER [trg_StaticSiteMenuTopLevelMenu] ON [dbo].[StaticSiteMenu]
FOR INSERT, UPDATE, DELETE
AS
IF @@ROWCOUNT = 0 RETURN
update dbo.StaticSiteMenu set TopLevelMenuId = dbo.TopLevelMenuId(Menu_Id)
where Menu_Id in (select Menu_Id from inserted)
update dbo.StaticSiteMenu set TopLevelMenuId = dbo.TopLevelMenuId(Menu_Id)
where Parent_Id in (select Menu_Id from inserted)
Here dbo.TopLevelMenuId(Menu_Id) is a function which finds ParentId. This trigger gives error 'Maximum stored procedure, function, trigger, or view nesting level exceeded ( limit 32) ' after execution. My datatbase property RECURSIVE_TRIGGERS is ON and I want to keep it ON because some of other triggers I want to execute recursively.
Here this trigger I dont want to execute recursively. But as RECURSIVE_TRIGGERS property is on this trigger is also executing recursively and giving error.
If any record is updated I'm updating SAME record with proper value and because of that first @@ROWCOUNT = 0 condition is never satisfying.
Can anyone suggest how I can terminate this trigger after executing it only one time? Can we avoid recursive execution in this trigger?
=======================================
[font="Tahoma"]"Dont let someone be priority in your life if you are simply option for someone."[/font]
October 15, 2008 at 8:38 pm
There is a trick for you:
IF Object_ID('tempdb..#Inserted_trg_StaticSiteMenuTopLevelMenu') IS NOT NULL
RETURN
ELSE
CREATE TABLE #Inserted_trg_StaticSiteMenuTopLevelMenu (ID int)
Table will be created when the trigger is called 1st time.
On recursive call trigger will see existing table and quit immediately.
_____________
Code for TallyGenerator
October 16, 2008 at 6:57 am
I'm not positive but I believe you can use the @@NESTLEVEL function as well.
The first thing I'd do is re-evaluate my recursive triggers. There should be a way to avoid them. They are a performance killer and a bear to debug if there is a problem because they nest transactions so if the 22nd transaction fails it rolls it all back.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 9:10 pm
@@NESTLEVEL depends on where you call your INSERT from.
If it is from a procedure called by a procedure called by ....
You know.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply