SQL Server 触发器循环

Question

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

例如：

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

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

Answer 1

我不建议在处理过程中显式禁用触发器 - 这可能会导致奇怪的副作用。

检测（和防止）触发器中的循环的最可靠方法是使用 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

有关更详细的示例，请参阅此链接。

---

关于 SQL Server 2000 中 CONTEXT_INFO() 的注意事项：

支持上下文信息，但显然不支持 CONTEXT_INFO 函数。你必须改用这个：

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

Answer 2

• 使用 TRIGGER_NESTLEVEL() 限制触发器递归，或者 < /p>

• < p>检查目标表是否需要更新：

  IF（选择计数（1） 
  来自用户_V1 
  内连接插入用户_V1.ID = 插入.ID
  WHERE users_V1.field1 <>插入字段1
  或 users_V1.field2 <>插入.field2) > 0 开始
  
  更新 users_V1 SET ...
  

Answer 3

我遇到了完全相同的问题。我尝试使用 CONTEXT_INFO() 但这是一个会话变量，因此它仅在第一次有效！然后下次在会话期间触发触发器时，这将不起作用。因此，我最终使用了一个变量，该变量在每个受影响的触发器中返回嵌套级别以退出。

示例：

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

      --Trigger logic goes here
END

注意：或者如果您想停止所有嵌套调用，请使用 @@NESTLEVEL>0

另请注意——本文中关于嵌套调用和递归调用似乎存在很多混淆。最初的海报指的是嵌套触发器，其中一个触发器将导致另一个触发器触发，这将导致第一个触发器再次触发，依此类推。这是嵌套的，但根据 SQL Server，不是递归的，因为触发器不直接调用/触发自身。递归并不是“一个触发器调用另一个触发器”。这是嵌套的，但不一定是递归的。您可以通过启用/禁用递归和嵌套来测试这里提到的一些设置：有关嵌套的博客文章

Answer 4

对于这个特定的设计场景，我属于无触发阵营。话虽如此，由于我对您的应用程序的功能和原因了解有限，以下是我的总体分析：

在表上使用触发器的优点是能够对表上的所有操作执行操作。这就是您在这种情况下的主要好处。但这意味着您拥有可以直接访问该表的用户或对该表的多个访问点。我倾向于避免这种情况。触发器有其用武之地（我经常使用它们），但它是我最后使用的数据库设计工具之一，因为它们往往不太了解其上下文（通常是优势）以及何时在需要的地方使用了解不同的背景和总体用例，它们的好处就会被削弱。

如果两个应用版本都需要触发相同的操作，则它们都应该调用相同存储过程。存储过程可以确保完成所有适当的工作，并且当您的应用程序不再需要支持V1时，可以删除该部分存储过程。

在客户端代码中调用两个存储过程是一个坏主意，因为这是数据库可以轻松一致地提供的数据服务的抽象层，而无需您的应用程序担心它。

我更喜欢更多地控制底层表的接口 - 使用视图、UDF 或 SP。用户永远无法直接访问表。这里的另一点是，您可以呈现单个“用户”VIEW 或 UDF 合并适当的基础表，而用户甚至不知道 - 也许甚至不需要任何“同步”，因为新属性位于EAV 系统，如果您需要那种病态的灵活性或仍然可以连接的其他不同结构 - 例如 OUTER APPLY UDF 等。

Answer 5

您必须在触发器中创建某种环回检测。也许在将记录输入下一个表之前使用“如果存在”语句来查看该记录是否存在。听起来确实会按照当前设置的方式进入无限循环。

Answer 6

避免像瘟疫这样的触发器......使用存储过程来添加用户。如果这需要一些设计更改，那么就进行更改。触发因素是邪恶。

Answer 7

尝试类似的东西（我没有打扰创建触发器的东西，因为你显然已经知道如何编写该部分）：

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 8

触发器中的递归，即一个触发器调用另一个触发器，仅限于 32 层

在每个触发器中，只需检查您要插入的行是否已存在。

示例

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