SQL Server Trigger loop

Question

I would like to know if there is anyway I can add a trigger on two tables that will replicate the data to the other.

For example:

• I have a two users tables, users_V1 and users_V2, When a user is updated with one of the V1 app, it activate a trigger updating it in users_V2 as well.

• If I want to add the same trigger on the V2 table in order to update the data in V1 when a user is updated in V2, will it go into an infinite loop? Is there any way to avoid that.

Answer 1

Try something like (I didn;t bother with thecreate trigger stuff as you clearly already know how to write that part):

update t
set field1 = i.field1
field2 = i.field2
from inserted i
join table1 t on i.id  = t.id
where field1 <> i.field1 OR field2 <> i.field2

Answer 2

I had the exact same problem. I tried using CONTEXT_INFO() but that is a session variable and so it works only the first time! Then next time a trigger fires during the session, this won't work. So I ended up with using a variable that returns Nest Level in each of the affected triggers to exit.

Example:

CREATE TRIGGER tr_Table1_Update
ON Table1
FOR UPDATE AS
BEGIN
      --Prevents Second Nested Call
      IF @@NESTLEVEL>1 RETURN 

      --Trigger logic goes here
END

Note: Or use @@NESTLEVEL>0 if you want to stop all nested calls

One other note -- There seems to be much confusion in this article about nested calls and recursive calls. The original poster was referring to a nested trigger where one trigger would cause another trigger to fire, which would cause the first trigger to fire again, and so on. This is Nested, but according to SQL Server, not recursive because the trigger is not calling/triggering itself directly. Recursion is NOT where "one trigger [is] calling another". That is nested, but not necessarily recursive. You can test this by enabling/disabling recursion and nesting with some settings mentioned here: blog post on nesting