asked on

SQL SERVER / Recursive Trigger how i stop firing in specific level / SQL SERVER

hi all
i have direct recursive trigger what i need that i want to stop firing trigger in a specific level , i try to use @@nestlevel Function but i dosnt work as i want
how can i do

create trigger ins_PRD
On JVS
For Update
As
Declare @JVid nvarchar(10)
Declare @PRD nvarchar(6)
Select @JVid =Jv_ID from inserted
Seelct @PRD=Period from inserted
Update JVS set Period = @PRD Where JV_id = @JVid

note that update statemnt in the last line will fire same triger again and again till 32 level
so i want trigger to be fired only one time how can i do
by the way i try to use the following technique but also didnt work

Update JVS set Period = @PRD Where JV_id = @JVid and @@Nestlevel=1

may somebody tell make recursive_trigger option off !! ?unfortunaltly i need it ON in other tables so icant make it off

HuyBD

Could you try this

create trigger ins_PRD
On JVS
For Update
As
Declare @JVid nvarchar(10)
Declare @PRD nvarchar(6)
Select @JVid =Jv_ID from inserted
Select @PRD=Period from inserted
Update JVS set Period = @PRD Where JV_id = @JVid
if @@Nestlevel>1
rollback

HuyBD

change rollback to return

create trigger ins_PRD
On JVS
For Update
As
Declare @JVid nvarchar(10)
Declare @PRD nvarchar(6)
Select @JVid =Jv_ID from inserted
Select @PRD=Period from inserted
Update JVS set Period = @PRD Where JV_id = @JVid
if @@Nestlevel>1
return

ASKER CERTIFIED SOLUTION

HuyBD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial