SQL Server Trigger loop

sql-server sql-server-2000 triggers infinite-loop
Roch picture Roch · Feb 10, 2010 · Viewed 12.5k times · Source

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

Aaronaught picture Aaronaught · Feb 10, 2010

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

Related questions

Pass a variable into a trigger

I have a trigger which deals with some data for logging purposes like so: CREATE TRIGGER trgDataUpdated ON tblData FOR UPDATE AS BEGIN INSERT INTO tblLog ( ParentID, OldValue, NewValue, UserID ) SELECT deleted.ParentID, deleted.Value, inserted.Value, @intUserID -- how …

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements. When you Google for the answer, you get so many different answers. Is there an official/backward …

Read More