infinite trigger loop....by design(!). How to work-around?
sql, sql-server, sql-server-2008
Solution
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'd have to set the nested triggers option. This will fix your problem, but if anything else in the system relies on recursion then it won't 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 hate to give you this solution because you're ultimately taking a really crappy design and extending it... making even more of a mess than it is already instead of taking the time to understand what's going on and just fixing it. You should honestly just create another table to hold the values that need to be in sync between the two tables so the data is only in one place, and then relate those tables to that one through a key. But nonetheless...
You need a flag to set you're updating in one trigger so the other trigger can abort its operation if it sees it's true. Since (as far as I know) you can only have locally scoped variable, that means you'll need a table to store this flag value in and look it up from.
You can implement this solution with varying levels of complexity, but the easiest way is to just have all triggers set the flag to true when starting and false when ending. And before they start they check the flag and stop executing if it's true;
The problem with this is that there could be another update that isn't related to a trigger happening at the same time and it wouldn't get propogated to the next table. If you want to take this route, then I'll leave it up to you to figure out how to solve that problem.
Problem
I know I'm going to get flamed for this, but.... I have table ProductA, ProductB, and ProductC which have very similar schema but for 2 or 3 columns in each. Each table has an insert trigger which fires a duplicate row for each insert in A, B, or C to table Products, which is a consolidation of all products. In addition, update triggers on A,B, or C will likewise update their equivalent row in Table Products, as do delete triggers. All working flawlessly until.....we update, say, Table Products Column A, which also exists in Table A, B, and C. I'm looking to develop a trigger on Table Products that will propogate that update in Column A to Column A in each of tables A, B, and C, BUT, without invoking the update triggers on Tables A, B, and C. The desired behavior is for updates to work in both directions without incurring an endless loop.(Note, only 2 columns in table products need to be replicated BACK to tables A, B, and C) Options are: - redesign the schema so this situation doesn't exist (not in the cards, this is a quick solution, redesign can be done by someone else); - Manually disable the triggers when I update table products (this is all done at the application level, users won't have the ability to log into SSMA and disable triggers when they update table products); - Come to Stack Overflow and hope someone has already encountered this type of problem! Conceptually, how could this be done? 6/7 Update: Here is the trigger code on Table A (e.g): ``` 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 currently disabled update trigger on table products: ``` 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 keep with the posting text)