3

Let me try to simplify the issue...

I have a table with two columns (A and B)

When A is updated, B will have to be updated (using A's new value to perform some calculation) When B is updated, A will have to be updated (using B's new value to perform some calculation)

I'm trying to write a trigger to do perform this update, but I'm afraid that the condition will fall into a infinite loop, as every time A updates, it will try to update B; as b in being updated, it will try to update A and so on...

Any suggestion?

Thanks!

Porkispin
  • 31
  • 1
  • A flag in a third column? If `UPDATE` ColA from `EXTERNAL` then `FLAG=1`; If `UPDATE` ColB from `EXTERNAL` then `FLAG=2`; Then a process If 'FLAG=1 UPDATE ColB & SET FLAG=0' and if `FLAG=2 UPDATE ColA & SET FLAG=0` It's not pretty and there may be better ways, but it's the first thing that came to mind – M O'Connell Nov 08 '16 at 22:05
  • @user3240704 I need to work this out without creating any more unnecessary information/columns. I didn't considerate this as an alternative as this is a work related task... I guess it would be fine at a personal/small project tho. I really believe that might be some workaround using just the trigger itself, but I'm failing to find the solution. Thanks for your input! – Porkispin Nov 08 '16 at 22:18
  • I use flags & timestamp columns in production environments when required. I'm interested in how to overcome this without a flag, this information will be valuable & the very best of luck – M O'Connell Nov 08 '16 at 22:23
  • Thanks, I will update this once I have the solution... deadline is coming lol – Porkispin Nov 08 '16 at 22:28
  • You will find the same advice [here on SO](http://stackoverflow.com/a/430836/243373) in another similar question. – TT. Nov 09 '16 at 07:38
  • @TT. Thanks for the hint. That answer indeed helped me to solve the issue. IF TRIGGER_NESTLEVEL() <= 1/*this update is not coming from some other trigger*/ – Porkispin Nov 09 '16 at 16:41

1 Answers1

0

The msdn documentation has a section about recursion. For your use case indirect recursion applies:

Recursive Triggers SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE. Recursive triggers enable the following types of recursion to occur:

Indirect recursion With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.

Direct recursion With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

Erik
  • 2,888
  • 2
  • 18
  • 35