Prevent Trigger Firing from anothe Trigger

Question

I have two triggers on the same tables for insert and update. Now the issue is that one of the trigger is again updating the table and thus, firing another trigger again. I want to prevent the trigger call from another trigger. I tried the below command but didn't work:

ALTER DATABASE MyDatabase SET RECURSIVE_TRIGGERS OFF;

Can anybody please suggest something?

Add more details. what is your trigger like? we need to see the code to help you. — Yeou, Jun 21 '19 at 07:19
I could suggest something, but you probably disagree because it involves a thorough system redesign: avoid triggers altogether and use stored procedures instead. (I have had similar issues with triggers as well, and I try to avoid them as much as possible. And with success. In the last 15 years, I made only 2 or 3 triggers, and only for temporary analysis in testing environments.) — Bart Hofland, Jun 21 '19 at 07:30

score 0 · Accepted Answer · answered Jun 21 '19 at 09:12

0

From this answer How do I prevent a database trigger from recursing?

   IF TRIGGER_NESTLEVEL(OBJECT_ID('dbo.mytrigger')) > 1
         BEGIN
             PRINT 'mytrigger exiting because TRIGGER_NESTLEVEL > 1 ';
             RETURN;
     END;

answered Jun 21 '19 at 09:12

Jaques

2,215
1
18
35

Prevent Trigger Firing from anothe Trigger

1 Answers1