• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 820
  • Last Modified:

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
0
ali_alannah
Asked:
ali_alannah
  • 3
1 Solution
 
HuyBDCommented:
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
0
 
HuyBDCommented:
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
0
 
HuyBDCommented:
Ok
Try to use 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
if @@Nestlevel<10
begin
Update JVS set  Period = @PRD  Where JV_id = @JVid
end
return
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now