SQL Server Trigger loop

I would like to know if there is 开发者_StackOverflow社区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.

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

• 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 ...
  

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

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.

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.

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.

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

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