Infinite trigger loop .... by design (!). How to work?

Question

I know that I will glow for it, but ....

I have a ProductA, ProductB, and ProductC table that have a very similar layout, but for 2 or 3 columns in each. Each table has an insert trigger that fires a repeating row for each insert in A, B, or C in Table Products, which is the union of all products. In addition, update triggers on A, B or C will also update their equivalent row in Table Products, as well as deleting triggers. All work flawlessly until ..... we update, say, the Products Column A table, which also exists in Tables A, B, and C.

I want to develop a trigger for Table products that will propagate this update in column A to column A in each of tables A, B, and C, but not trigger update triggers in tables A, B, and C. The required behavior is that updates work in both directions without an endless loop. (Note that only 2 columns in table products should be replicated BACK to tables A, B, and C)

Parameters:

• redesign the scheme so that this situation does not exist (not into cards, this is a quick decision, redesign can be done by someone else);
• Manually disable triggers when updating table products (all this is done at the application level, users will not be able to log into SSMA and disable triggers when updating the table of products);
• Come to the stack overflow and hope that someone has already encountered this problem!

Conceptually, how can this be done?

Update 6/7:

Here is the startup code in table A (for example):

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 

AND

 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 

And here is the update trigger for desktop products that are currently disabled:

 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 

(tbl names changed to save publication text)

Answer 1

There are two types of recursion, direct and indirect: http://msdn.microsoft.com/en-us/library/ms190739.aspx

You can use the RECURSIVE_TRIGGERS option to stop direct recursion, but your case is indirect recursion, so you need to set the option of nested triggers. This will fix your problem, but if something else in the system depends on recursion, then this will not be a good option.

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

EDIT in response to your updated post:

I almost don't want to give you this solution, because you end up doing really shitty design and expanding it ... making more mess than this, instead of wasting time figuring out what’s happening and just fixing It. You just have to honestly create another table to store the values ​​that need to be synchronized between the two tables so that the data is in only one place and then link these tables to this key. But nonetheless...

You need a flag to set what you update for one trigger so that another trigger can abort its action if it sees it true. Since (as far as I know), you can only have a local variable, that is, you need a table to store this flag value and search from it.

You can implement this solution with varying degrees of difficulty, but the easiest way is to simply set all triggers to set the flag to true at startup and false at completion. And before they start, they check the flag and stop execution if it is true;

The problem is that there may be another update that is not related to the trigger happening at the same time, and it will not propagate to the following table. If you want to go this route, I will leave it to you to figure out how to solve this problem.