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)
