SQL Server触发循环 [英] SQL Server Trigger loop

问题描述

我想知道是否可以在两个表上添加一个触发器，以将数据复制到另一个表。

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.

例如：

• 我有两个用户表，users_V1和users_V2，当用V1应用程序之一更新用户时，它会激活触发器更新

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

如果我想在V2表上添加相同的触发器，以便在更新用户时更新V1中的数据。 V2，会陷入无限循环吗？有什么方法可以避免这种情况。

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.

检测（并防止）触发器中的周期最可靠的方法是使用 CONTEXT_INFO（）。

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

示例：

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.

关于 CONTEXT_INFO（）的注释在SQL Server 2000中：

Note on CONTEXT_INFO() in SQL Server 2000:

支持上下文信息，但显然 CONTEXT_INFO 功能不是。您必须改用以下命令：

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

这篇关于SQL Server触发循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！