sql - 无限触发循环...设计使然(!)。如何解决？

Question

我知道我会因此而受到批评，但是......

我有表 ProductA、ProductB 和 ProductC，它们具有非常相似的架构，但每个表都有 2 或 3 列。每个表都有一个插入触发器，该触发器为 A、B 或 C 中的每次插入触发一个重复行到表 Products(所有产品的合并)。此外，A、B 或 C 上的更新触发器同样会更新表产品中的相应行，删除触发器也是如此。一切都完美地工作，直到……我们更新了表产品列 A，该列也存在于表 A、B 和 C 中。

我希望在表产品上开发一个触发器，它将 A 列中的更新传播到每个表 A、B 和 C 中的 A 列，但是，不会调用表 A、B 和表上的更新触发器C. 期望的行为是更新在两个方向上进行，而不会引发无限循环。(注意，表 products 中只有 2 列需要复制回表 A、B 和 C)

选项有:

1. 重新设计架构，这样这种情况就不存在(不在卡，这是一个快速的解决方案，可以由某人重新设计别的);
2. 更新表产品时手动禁用触发器(这都是在应用程序级别完成的，用户不会有能够登录 SSMA 并在更新表时禁用触发器产品)；
3. 来到 Stack Overflow，希望有人已经遇到过此类问题!

从概念上讲，这是如何做到的？

6/7 更新:

这是表 A 上的触发代码(例如):

    ALTER TRIGGER   [dbo].[GRSM_WETLANDS_Point_GIS_tbl_locations_update]
    ON  [dbo].[GRSM_WETLANDS_POINT]
    after update  
    AS   
    BEGIN   
      SET NOCOUNT ON;  

      update dbo.TBL_LOCATIONS
      set 
    X_Coord = i.X_Coord,
    Y_Coord = i.Y_Coord,
    PlaceName = i.PlaceName,
    FCSubtype = case
    when i.FCSubtype = 1 then 'Point: Too Small to Determin Boundary'
    when i.FCSubtype = 2 then 'Point: Boundary Determined by Contractor but not Surveyed' 
    when i.FCSubtype = 3 then 'Point: Wetland Reported but not yet Surveyed'
    end ,
    Landform = i.Landform

    from dbo.TBL_LOCATIONS
    Join inserted i
    on TBL_LOCATIONS.GIS_Location_ID = i.GIS_Location_ID
      end



GO

和

ALTER TRIGGER [dbo].[GRSM_WETLANDS_POINT_GIS_tbl_locations]
ON

[dbo].[GRSM_WETLANDS_POINT]
after INSERT
AS
BEGIN
SET NOCOUNT ON; 
INSERT dbo.TBL_LOCATIONS(
X_Coord, Y_Coord, 
PlaceName, 
FCSubtype, Landform
)

SELECT 
a.X_Coord, a.Y_Coord, 
a.PlaceName, 
a.FCSubtype, a.Landform

From
( 
SELECT 
X_Coord, Y_Coord, 
PlaceName, 
FCSubtype = case
when FCSubtype = 1 then 'Point: Too Small to Determin Boundary'
when FCSubtype = 2 then 'Point: Boundary Determined by Contractor but not Surveyed' 
when FCSubtype = 3 then 'Point: Wetland Reported but not yet Surveyed'
end , 
Landform

FROM inserted 
) AS a 

end

GO

这是表产品上当前禁用的更新触发器:

ALTER TRIGGER   [dbo].[tbl_locations_updateto_geo]
ON  [dbo].[TBL_LOCATIONS]
for update  
AS   

BEGIN 
--IF @@NESTLEVEL>1 RETURN  
  SET NOCOUNT ON;  
  update dbo.GRSM_Wetlands_Point 
  set 
X_Coord = i.X_Coord,
Y_Coord = i.Y_Coord,
PlaceName = i.PlaceName,
FCSubtype = i.FCSubtype,
Landform = i.Landform,
from dbo.TBL_LOCATIONS
Join inserted i
on TBL_LOCATIONS.GIS_Location_ID = i.GIS_Location_ID
where TBL_LOCATIONS.FCSubtype =  'Polygon: Determination Made by GPS Survey'
or TBL_LOCATIONS.FCSubtype =  'Polygon: Determination Derived from NWI' 
 or TBL_LOCATIONS.FCSubtype =  'Polygon: Determination Made by Other Means'
  or TBL_LOCATIONS.FCSubtype =  'Polygon: Legal Jurisdictional Determination';
  end
GO

(表格名称已更改，以与发布文本保持一致)

Answer 1

有两种类型的递归，直接和间接:http://msdn.microsoft.com/en-us/library/ms190739.aspx

您可以使用 RECURSIVE_TRIGGERS 选项来停止直接递归，但您的情况是间接递归，因此您必须设置嵌套触发器选项。这将解决您的问题，但如果系统中的其他任何内容依赖于递归，那么它不会是一个好的选择。

USE DatabaseName
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'nested triggers', 0
GO
RECONFIGURE
GO

编辑以回应您更新的帖子:

我几乎不想给你这个解决方案，因为你最终采用了一个非常蹩脚的设计并扩展了它......比现在更加困惑，而不是花时间了解正在发生的事情并修复它。老实说，您应该创建另一个表来保存两个表之间需要同步的值，以便数据仅位于一个位置，然后通过键将这些表与该表关联起来。但尽管如此...

您需要一个标志来设置您正在一个触发器中进行更新，以便另一个触发器在看到它为 true 时可以中止其操作。由于(据我所知)您只能拥有本地范围的变量，这意味着您需要一个表来存储此标志值并从中查找它。

您可以以不同的复杂程度来实现此解决方案，但最简单的方法是让所有触发器在开始时将标志设置为 true，在结束时将标志设置为 false。在开始之前，他们会检查标志，如果为真则停止执行；

这样做的问题是，可能存在与同时发生的触发器无关的另一个更新，并且它不会传播到下一个表。如果您想走这条路，那么我将让您自行决定如何解决该问题。