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

• Either use TRIGGER_NESTLEVEL() to restrict trigger recursion, or

• check the target table whether an UPDATE is necessary at all:

  IF (SELECT COUNT(1) 
  FROM users_V1 
  INNER JOIN inserted ON users_V1.ID = inserted.ID
  WHERE users_V1.field1 <> inserted.field1
  OR users_V1.field2 <> inserted.field2) > 0 BEGIN
  
  UPDATE users_V1 SET ...
  

Answer 2

Recursion in triggers, that is, one trigger calling another, is limited to 32 levels

In each trigger, just check if the row you wish to insert already exists.

Example

CREATE TRIGGER Table1_Synchronize_Update ON [Table1] FOR UPDATE AS
BEGIN
  UPDATE  Table2
  SET     LastName = i.LastName
          , FirstName = i.FirstName
          ,  ... -- Every relevant field that needs to stay in sync
  FROM    Table2 t2
          INNER JOIN Inserted i ON i.UserID = t2.UserID
  WHERE   i.LastName <> t2.LastName
          OR i.FirstName <> t2.FirstName
          OR ... -- Every relevant field that needs to stay in sync
END

CREATE TRIGGER Table1_Synchronize_Insert ON [Table1] FOR INSERT AS
BEGIN
  INSERT INTO Table2
  SELECT i.*
  FROM   Inserted i
         LEFT OUTER JOIN Table2 t2 ON t2.UserID = i.UserID
  WHERE  t2.UserID IS NULL
END

CREATE TRIGGER Table2_Synchronize_Update ON [Table2] FOR UPDATE AS
BEGIN
  UPDATE  Table1
  SET     LastName = i.LastName
          , FirstName = i.FirstName
          ,  ... -- Every relevant field that needs to stay in sync
  FROM    Table1 t1
          INNER JOIN Inserted i ON i.UserID = t1.UserID
  WHERE   i.LastName <> t1.LastName
          OR i.FirstName <> t1.FirstName
          OR ... -- Every relevant field that needs to stay in sync
END

CREATE TRIGGER Table2_Synchronize_Insert ON [Table2] FOR INSERT AS
BEGIN
  INSERT INTO Table1
  SELECT i.*
  FROM   Inserted i
         LEFT OUTER JOIN Table1 t1 ON t1.UserID = i.UserID
  WHERE  t1.UserID IS NULL
END

Answer 3

I don't recommend explicitly disabling the trigger during processing - this can cause strange side-effects.

The most reliable way to detect (and prevent) cycles in a trigger is to use CONTEXT_INFO().

Example:

CREATE TRIGGER tr_Table1_Update
ON Table1
FOR UPDATE AS

DECLARE @ctx VARBINARY(128) 
SELECT @ctx = CONTEXT_INFO() 
IF @ctx = 0xFF
    RETURN

SET @ctx = 0xFF

-- Trigger logic goes here

See this link for a more detailed example.

---

Note on CONTEXT_INFO() in SQL Server 2000:

Context info is supported but apparently the CONTEXT_INFO function is not. You have to use this instead:

SELECT @ctx = context_info
FROM master.dbo.sysprocesses
WHERE spid = @@SPID

Answer 4

You're going to have to create some sort of loopback detection within your trigger. Perhaps using an "if exists" statement to see if the record exists before entering it into the next table. It does sound like it will go into an infinite loop the way it's currently set up.

Answer 5

Avoid triggers like the plague .... use a stored procedure to add the user. If this requires some design changes then make them. Triggers are the EVIL.

Answer 6

I'm with the no triggers camp for this particular design scenario. Having said that, with the limited knowledge I have about what your app does and why it does it, here's my overall analysis:

Using a trigger on a table has an advantage of being able to act on all actions on the table. That's it, your main benefit in this case. But that would mean you have users with direct access to the table or multiple access points to the table. I tend to avoid that. Triggers have their place (I use them a lot), but it's one of the last database design tools I use because they tend to not know a lot about their context (generally, a strength) and when used in a place where they do need to know about different contexts and overall use cases, their benefits are weakened.

If both app versions need to trigger the same action, they should both call the same stored proc. The stored proc can ensure that all the appropriate work is done, and when your app no longer needs to support V1, then that part of the stored proc can be removed.

Calling two stored procs in your client code is a bad idea, because this is an abstraction layer of data services which the database can provide easily and consistently, without your application being worried about it.

I prefer to control the interface to the underlying tables more - with either views or UDFs or SPs. Users never get direct access to a table. Another point here is that you could present a single "users" VIEW or UDF coalescing the appropriate underlying tables without the user even knowing about - perhaps getting to the point where there is not even any "synchronization" necessary, since new attributes are in an EAV system if you need that kind of pathological flexibility or in some other different structure which can still be joined - say OUTER APPLY UDF etc.