Basic update statement problems

jcbuche · November 16, 2016, 11:27pm

I have this basic update statement that is zeroing out a separate field in the same table, but it is not being included in the update query, hope that makes sense. Can anyone help me figure out why that is

the statement is:
UPDATE partMaster SET partMaster.FAC= 'Default', partMaster.FPARTNO = 'LUG58824', partMaster.FDESCRIPT = 'T&B,#4AWG, 1/4'
WHERE partMaster.FPARTNO = '04-54106' AND partMaster.FAC= 'Default'

i've also tried to be more specific:
UPDATE partMaster SET partMaster .FAC= 'Default', partMaster .FPARTNO = 'LUG58824', partMaster .FDESCRIPT = 'T&B,#4AWG, 1/4', partMaster .fstdcost= '0.69', partMaster .fonhand= '178'
WHERE partMaster .FPARTNO = '04-54106' AND partMaster .FAC= 'Default' AND partMaster .fstdcost= '0.69' AND partMaster .fonhand= '178'

either one of these results in the FPARTNO and FDESCRIPT being updated as expected, but the partMaster.fonhand is set to zero for some reason.

Again, looking for a reason why that would be.

Thanks in advance for your time!

Kristen · November 17, 2016, 7:08am

Is there a Trigger on the [partMaster] table?

EXEC sp_HelpTrigger 'partMaster'

will list any triggers on that table

jcbuche · November 17, 2016, 2:39pm

Yes, two of them.

Another thing I need to mention. My update query is updating about 4,500 parts, this zeroing out issue is only affected 50-75 lines.

Thanks again

Kristen · November 17, 2016, 3:12pm

Presumably dependent on the SQL code in the Trigger

jcbuche · November 17, 2016, 5:45pm

i have another complication. There is a view that is also tied to this table. I'm not clear on how the table, views and triggers all interact. what's the recommendation to try to get these update statements to run?

Thanks again.

Kristen · November 17, 2016, 6:53pm

The TRIGGER is forcing the values. There is no doubt a good reason why it is doing that, so you'll need to find out why that "business rule" exists, and why the data you are providing does not conform.

It might be that the Business Rule is wrong (or "has now become wrong"), but do NOT start with that assumption and/or just fix it, make 100% that the trigger is definitely doing the wrong thing before you change it.

jcbuche · November 18, 2016, 1:30pm

Understood and agreed, I don't want to force data in the database.

I just don't know what the trigger is looking for, I don't understand why it's changing the values on select items.

Kristen · November 18, 2016, 2:35pm

I guess the only way to figure that out is to have a look at the code in the Trigger - by all means post it here if you would like us to comment

Various tools available to extract the code for the trigger, but if all else fails this should do the job:

EXEC sp_HelpText 'YourTriggerName '

jcbuche · November 18, 2016, 3:55pm

Yes, of course. Thanks.

USE [M2MDATA10]
GO
/****** Object: Trigger [dbo].[tu_INMASTX] Script Date: 11/18/2016 10:54:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/* Create Update Trigger */
ALTER TRIGGER [dbo].[tu_INMASTX]

ON [dbo].[inmastx]

FOR UPDATE

AS

SET NOCOUNT ON

/* Check whether DBR is running. If not, triggers don't matter. */
DECLARE @DBRIsUp bit
SELECT @DBRIsUp = flDBRIsUp FROM CSPROD

IF @DBRIsUp = 1
/* DBR is running */
BEGIN

DECLARE @DBRMod Int
SELECT @DBRMod = i.fnDBRMod FROM Inserted i

IF @DBRMod = 2
/* This Update was initiated by DBR, so we need to reset the field but not load staging table. */
BEGIN
    UPDATE INMASTX SET fnDBRMod=0 FROM INMASTX im, INSERTED i
		WHERE im.Identity_Column=i.Identity_Column AND im.fnDBRMod=2
END

ELSE
BEGIN
	/* If @DBRMod=3, then DBR initiated this Delete.  So, no other fields would have been modified. */
	IF @DBRMod <> 3
	/* This Update was not initiated by DBR, so we need to load staging table. */
	BEGIN
		DECLARE @ModFlds varchar(500)
		DECLARE @ModVals varchar(2048)
		DECLARE @NewModVals varchar(50)
		DECLARE @ModTypes varchar(50)
        DECLARE @lNeedAdd bit
        DECLARE @ReOpen char(1)

		SET @ModFlds = ''
		SET @ModVals = ''
		SET @NewModVals = ''
		SET @ModTypes = ''

		/* PartNo and Rev should not change. */
		IF UPDATE(fDescript)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = RTRIM(i.fDescript) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fDescript <> d.fDescript			-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = 'fDescript|'		-- Field Name with pipeline included
				SET @ModTypes = 'C'
				SET @ModVals = RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fMeasure)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = RTRIM(i.fMeasure) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fMeasure <> d.fMeasure		-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fMeasure|'		-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'C'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fSource)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = RTRIM(i.fSource) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fSource <> d.fSource	-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fSource|'		-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'C'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

        IF UPDATE(sFac)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
            SELECT @NewModVals = RTRIM(i.sFac) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
                AND i.sFac <> d.sFac  -- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
                SET @ModFlds = @ModFlds + 'sFac|'        -- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'C'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fLeadTime)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = LTRIM(STR(i.fLeadTime,10,1)) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
	 			AND i.fLeadTime <> d.fLeadTime		-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fLeadTime|'			-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'N'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fStdCost)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = LTRIM(STR(i.fStdCost,20,5)) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fStdCost <> d.fStdCost			-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fStdCost|'			-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'N'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fPrice)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = LTRIM(STR(i.fPrice,20,5)) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fPrice <> d.fPrice		-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fPrice|'			-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'N'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fSafety)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = LTRIM(STR(i.fSafety,20,5)) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fSafety <> d.fSafety		-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fSafety|'			-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'N'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fYield)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = LTRIM(STR(i.fYield,10,3)) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fYield <> d.fYield		-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fYield|'			-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'N'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fProdCl)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = RTRIM(i.fProdCl) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fProdCl <> d.fProdCl		-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fProdCl|'			-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'C'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fGroup)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = RTRIM(i.fGroup) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fGroup <> d.fGroup		-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fGroup|'			-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'C'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fABCcode)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = RTRIM(i.fABCcode) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fABCcode <> d.fABCcode		-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fABCcode|'			-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'C'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fBuyer)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = RTRIM(i.fBuyer) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fBuyer <> d.fBuyer		-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fBuyer|'			-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'C'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

		IF UPDATE(fcPurchase)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
			SELECT @NewModVals = RTRIM(i.fcPurchase) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
				AND i.fcPurchase <> d.fcPurchase		-- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
				SET @ModFlds = @ModFlds + 'fcPurchase|'			-- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'C'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

        IF UPDATE(fBulkIssue)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
            SELECT @NewModVals = RTRIM(i.fBulkIssue) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
                AND i.fBulkIssue <> d.fBulkIssue        -- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
                SET @ModFlds = @ModFlds + 'fBulkIssue|'         -- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'C'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END

        IF UPDATE(fdVenFence)
        BEGIN
                SET @NewModVals = ''                    -- Set parameter
                SELECT @NewModVals = RTRIM(CONVERT(CHAR,i.fdVenFence,120)) FROM Inserted i, Deleted d
                        WHERE i.identity_column=d.identity_column
                        AND i.fdVenFence <> d.fdVenFence        -- Be sure that field did change

                IF LEN(@NewModVals) > 0
                /* Field did change */
                BEGIN
                        SET @ModFlds = @ModFlds + 'fdVenFence|'           -- Field Name with pipeline included
                        SET @ModTypes = @ModTypes + 'D'             -- Numeric DataType
                        SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'      -- Concatenate string and tack on a pipeline
                END
        END

        IF UPDATE(SchedDate)
        BEGIN
                SET @NewModVals = ''                    -- Set parameter
                SELECT @NewModVals = RTRIM(CONVERT(CHAR,i.SchedDate,120)) FROM Inserted i, Deleted d
                        WHERE i.identity_column=d.identity_column
                        AND i.SchedDate <> d.SchedDate        -- Be sure that field did change

                IF LEN(@NewModVals) > 0
                /* Field did change */
                BEGIN
                        SET @ModFlds = @ModFlds + 'SchedDate|'           -- Field Name with pipeline included
                        SET @ModTypes = @ModTypes + 'D'             -- Numeric DataType
                        SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'      -- Concatenate string and tack on a pipeline
                END
        END

        IF UPDATE(DockTime)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
            SELECT @NewModVals = LTRIM(STR(i.DockTime,2,0)) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
                AND i.DockTime <> d.DockTime      -- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
                SET @ModFlds = @ModFlds + 'DockTime|'          -- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'N'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
		END


        IF UPDATE(fnIFTtime)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
            SELECT @NewModVals = LTRIM(STR(i.fnIFTtime,7,1)) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
                AND i.fnIFTtime <> d.fnIFTtime      -- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
                SET @ModFlds = @ModFlds + 'fnIFTtime|'          -- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'N'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
			END
        END



        IF UPDATE(fcStsCode)
		BEGIN
			SET @NewModVals = ''					-- Set parameter
            SELECT @NewModVals = RTRIM(i.fcStsCode) FROM Inserted i, Deleted d
				WHERE i.Identity_Column=d.Identity_Column
                AND i.fcStsCode <> d.fcStsCode        -- Be sure that field did change

			IF LEN(@NewModVals) > 0
			/* Field did change */
			BEGIN
                SET @ModFlds = @ModFlds + 'fcStsCode|'         -- Field Name with pipeline included
				SET @ModTypes = @ModTypes + 'C'
				SET @ModVals = @ModVals + RTRIM(@NewModVals) + '|'	-- Concatenate string and tack on a pipeline
                SET @lNeedAdd = 0
                IF (SELECT fcStsCode FROM Inserted i) IN ('A') AND (SELECT fcStsCode FROM Deleted d) NOT IN ('A')
                    /* CR 148171. The Add or Modify evaluation code was moved under the Status field change section where
                    we set a logical variable lNeedAdd.
                    If the Part status is changed from non "Active" to "Active" , we need to send it and it's
                    dependent data to P&S. If the status is changed from Active to non Active, we need to send
                    Only the status change to P&S */
                BEGIN
                  SET @lNeedAdd = 1
                END
			END
		END

		/* Strip off leading or trailing pipelines */
		IF LEFT(@ModFlds, 1) = '|' SET @ModFlds = SUBSTRING(@ModFlds, 2, LEN(@ModFlds))
		IF LEFT(@ModVals, 1) = '|' SET @ModVals = SUBSTRING(@ModVals, 2, LEN(@ModVals))

		IF RIGHT(@ModFlds, 1) = '|' SET @ModFlds = SUBSTRING(@ModFlds, 1, LEN(@ModFlds) - 1)
		IF RIGHT(@ModVals, 1) = '|' SET @ModVals = SUBSTRING(@ModVals, 1, LEN(@ModVals) - 1)

		/* Insert record into staging table*/
		IF LEN(@ModFlds) > 0
		/* At least one of the DBR relevant fields changed */
		BEGIN
            IF @lNeedAdd = 1    /* see related CR 148171 */
                /* User changed the part status to a visible status (Active) from non visible (Started or Obsolete).
                   Send this to P&S as an ADD transaction. */
			BEGIN
            /* Insert INMASTX record into staging table*/
                INSERT INTO DBRstage (UpdateType, ObjectType, KeyFlds, KeyVals, KeyTypes, ModFlds, ModVals, ModTypes)
                SELECT 'ADD', 'INMASTX', 'fac|fPartNo|fRev',
                RTRIM(i.fac) + '|' + RTRIM(i.fpartno) + '|' + RTRIM(i.frev), 'CCC',
                'fDescript|fMeasure|fSource|sFac|fLeadTime|fStdCost|fPrice|fSafety|fYield|'
                + 'fProdCl|fGroup|fAbcCode|fBuyer|fcPurchase|fcStsCode|fBulkIssue|'
                + 'fdVenFence|SchedDate|DockTime|fnIFTtime', -- End of ModFlds; next line begins ModVals
                RTRIM(i.fDescript) + '|' + RTRIM(i.fMeasure) + '|' + RTRIM(i.fSource) + '|' + RTRIM(i.sFac) + '|' + LTRIM(STR(i.fLeadTime,10,1)) + '|'
                + LTRIM(STR(i.fStdCost,20,5)) + '|' + LTRIM(STR(i.fPrice,20,5)) + '|' + LTRIM(STR(i.fSafety,20,5)) + '|'
                + LTRIM(STR(i.fYield,10,3)) + '|'
                + RTRIM(i.fProdCl) + '|' + RTRIM(i.fGroup) + '|' + RTRIM(i.fABCcode) + '|'
                + RTRIM(i.fBuyer) + '|' + RTRIM(i.fcPurchase) + '|' + RTRIM(i.fcStsCode)+ '|' + RTRIM(i.fBulkIssue) + '|'
                + RTRIM(CONVERT(CHAR,i.fdVenFence,120))+ '|' + RTRIM(CONVERT(CHAR,i.SchedDate,120)) + '|'
                + LTRIM(STR(i.DockTime,2,0)) + '|' + LTRIM(STR(i.fnIFTtime,7,1)),          -- End of ModVals
                'CCCCNNNNNCCCCCCCDDNN'             -- ModTypes
                 FROM inserted i

            /* Insert PRINX record into staging table*/
                INSERT INTO DBRstage (UpdateType, ObjectType, KeyFlds, KeyVals, KeyTypes, ModFlds, ModVals, ModTypes)
                SELECT 'ADD', 'PRINX', 'fac|fcPartNo|fcPartRv',
                RTRIM(j.fac) + '|' + RTRIM(j.fcpartno) + '|' + RTRIM(j.fcpartrv), 'CCC',
                'fnJoeTol|fnJolTol|fnDaySup|fnMinOrd|flMinOrd|fnMaxOrd|flMaxOrd|fnMltOrd|flMltOrd|flManPlan', -- End of ModFlds; next line begins ModVals
                LTRIM(STR(j.fnJoeTol)) + '|' + LTRIM(STR(j.fnJolTol)) + '|' + LTRIM(STR(j.fnDaySup)) + '|'
                + LTRIM(STR(j.fnMinOrd,15,4)) + '|' + RTRIM(CAST(j.flMinOrd AS CHAR)) + '|' + LTRIM(STR(j.fnMaxOrd,15,4)) + '|'
                + RTRIM(CAST(j.flMaxOrd AS CHAR)) + '|' + LTRIM(STR(j.fnMltOrd,15,4)) + '|'
                + RTRIM(CAST(j.flMltOrd AS CHAR)) + '|' + RTRIM(CAST(j.flManPlan AS CHAR)),
                'IIINLNLNLL'                               -- ModTypes
                FROM prinx j
                JOIN inserted i
                ON i.fac = j.fac AND i.fPartNo = j.fcPartNo AND i.fRev = j.fcPartRv

            /* Insert INRTC record into staging table*/
                INSERT INTO DBRstage (UpdateType, ObjectType, KeyFlds, KeyVals, KeyTypes, ModFlds, ModVals, ModTypes)
                SELECT 'ADD', 'INRTGC', 'fac|fPartNo|fcPartRev',
                RTRIM(j.fac) + '|' + RTRIM(j.fpartno) + '|' + RTRIM(j.fcpartrev), 'CCC',
                'fSPQ',                            -- End of ModFlds; next line begins ModVals
                LTRIM(STR(j.fSPQ,20,5)),
                'N'                                -- ModTypes
                FROM inrtgc j
                JOIN inserted i
                ON i.fac = j.fac AND i.fPartNo = j.fPartNo AND i.fRev = j.fcPartRev

            /* Insert INRTGS record into staging table*/
                INSERT INTO DBRstage (UpdateType, ObjectType, KeyFlds, KeyVals, KeyTypes, ModFlds, ModVals, ModTypes)
                SELECT 'ADD', 'INRTGS', 'fac|fPartNo|fcPartRev|fOperNo',
                RTRIM(j.fac) + '|' + RTRIM(j.fpartno) + '|' + RTRIM(j.fcpartrev) + '|' + LTRIM(STR(j.foperno)), 'CCCI',
                'fSetupTime|fUProdTime|fMoveTime|fElpsTime|fOperQty|flSchedule|fPro_Id|fcCharCode|fnSimulOps|fYield|fSetYield', -- End of ModFlds; next line begins ModVals
                LTRIM(STR(j.fSetupTime,10,2)) + '|' + LTRIM(STR(j.fUProdTime,20,10)) + '|' + LTRIM(STR(j.fMoveTime,10,2)) + '|'
                + LTRIM(STR(j.fElpsTime,15,5)) + '|' + LTRIM(STR(j.fOperQty,20,5)) + '|'
                + RTRIM(CAST(j.flSchedule AS CHAR)) + '|' + RTRIM(j.fPro_Id) + '|' + RTRIM(j.fcCharCode) + '|' +
                LTRIM(STR(j.fnSimulOps)) + '|' + LTRIM(STR(j.fYield,12,5)) + '|' + LTRIM(STR(j.fSetYield,12,5)),
                'NNNNNLCCINN'                             -- ModTypes
                FROM inrtgs j
                JOIN inserted i
                ON i.fac = j.fac AND i.fPartNo = j.fPartNo AND i.fRev = j.fcPartRev

            /* Insert INRTGR record into staging table*/
                INSERT INTO DBRstage (UpdateType, ObjectType, KeyFlds, KeyVals, KeyTypes, ModFlds, ModVals, ModTypes)
                SELECT 'ADD', 'INRTGR', 'fac|fPartNo|fcPartRev|fOperNo|fcRes_Id',
                RTRIM(j.fac) + '|' + RTRIM(j.fpartno) + '|' + RTRIM(j.fcpartrev) + '|' + LTRIM(STR(j.foperno)) + '|'
                + RTRIM(j.fcres_id), 'CCCIC',
                'fnSimulOps',  -- End of ModFlds; next line begins ModVals
                RTRIM(j.fnSimulOps),
                'N'                                -- ModTypes
                FROM inrtgr j
                JOIN inserted i
                ON i.fac = j.fac AND i.fPartNo = j.fPartNo AND i.fRev = j.fcPartRev

             /* Insert INRTGA record into staging table*/
                INSERT INTO DBRstage (UpdateType, ObjectType, KeyFlds, KeyVals, KeyTypes, ModFlds, ModVals, ModTypes)
                SELECT 'ADD', 'INRTGA', 'fac|fPartNo|fcPartRev|fOperNo|fcRes_ID',
                RTRIM(j.fac) + '|' + RTRIM(j.fpartno) + '|' + RTRIM(j.fcpartrev) + '|' + LTRIM(STR(j.foperno)) + '|' + RTRIM(j.fcRes_ID), 'CCCIC',
                'fSetupTime|fProdTime|fMoveTime|fnSimulOps|fElpsTime', -- End of ModFlds; next line begins ModVals
                LTRIM(STR(j.fSetupTime,7,2)) + '|' + LTRIM(STR(j.fProdTime,16,10)) + '|' + LTRIM(STR(j.fMoveTime,8,2)) + '|'
                + LTRIM(STR(j.fnSimulOps)) + '|' + LTRIM(STR(j.fElpsTime, 15,2)),
                'NNNIN'                             -- ModTypes
                FROM inrtga j
                JOIN inserted i
                ON i.fac = j.fac AND i.fPartNo = j.fPartNo AND i.fRev = j.fcPartRev

            /* Insert INBOMS record into staging table*/
                INSERT INTO DBRstage (UpdateType, ObjectType, KeyFlds, KeyVals, KeyTypes, ModFlds, ModVals, ModTypes)
                SELECT 'ADD', 'INBOMS', 'PFacilityID|fParent|fParentRev|fnOperNo|CFacilityID|fComponent|fCompRev|identity_column',
                RTRIM(j.PFacilityID) + '|' + RTRIM(j.fparent) + '|' + RTRIM(j.fparentrev) + '|' + LTRIM(STR(j.fnoperno))
                + '|' + RTRIM(j.CFacilityID) + '|' + RTRIM(j.fcomponent) + '|' + RTRIM(j.fcomprev) + '|' +
                LTRIM(STR(j.identity_column)),'CCCICCCI',
                'fSt_Ef_Dt|fEnd_Ef_Dt|fQty|flTooling|flExtend|fItem',    -- End of ModFlds; next line begins ModVals
                RTRIM(CONVERT(CHAR,j.fSt_Ef_Dt,120)) + '|' + RTRIM(CONVERT(CHAR,j.fEnd_Ef_Dt,120)) + '|'
                + LTRIM(STR(j.fQty,15,5)) + '|' + RTRIM(CAST(j.flTooling AS CHAR)) + '|' + RTRIM(CAST(j.flExtend AS CHAR))
                + '|' + RTRIM(j.fitem),
                'DDNLLC'                                -- ModTypes
                FROM inboms j
                JOIN inserted i
                ON i.fac = j.pFacilityID AND i.fPartNo = j.fParent AND i.fRev = j.fParentRev

            /* Insert PRPLN record into staging table*/
                INSERT INTO DBRstage (UpdateType, ObjectType, KeyFlds, KeyVals, KeyTypes, ModFlds, ModVals, ModTypes)
                SELECT 'ADD', 'PRPLN', 'fac|fcPartNo|fcPartRev|fdTxnDate',
                RTRIM(j.fac) + '|' + RTRIM(j.fcpartno) + '|' + RTRIM(j.fcpartrev) +  '|' + RTRIM(CONVERT(CHAR,j.fdtxndate,120)), 'CCCD',
                'fnTxnQty|fcTxnType|SchedDate',              -- End of ModFlds; next line begins ModVals
                LTRIM(STR(j.fnTxnQty,20,4)) + '|' + RTRIM(j.fcTxnType)+ '|' + RTRIM(CONVERT(CHAR,j.SchedDate,120)),
                'NCD'                               -- ModTypes
                FROM prpln j
                JOIN inserted i
                ON i.fac = j.fac AND i.fPartNo = j.fcPartNo AND i.fRev = j.fcPartRev

            /* Send Sync Server a Modify txn about ReOpen */
                INSERT INTO DBRstage (UpdateType, ObjectType, KeyFlds, KeyVals, KeyTypes, ModFlds, ModVals, ModTypes)
                SELECT 'MODIFY', 'INMASTX', 'fac|fPartNo|fRev',
                 RTRIM(i.fac) + '|' + RTRIM(i.fpartno) + '|' + RTRIM(i.frev), 'CCC',
                 'ReOpen', '1', 'L'
                 FROM inserted i
            END
            ELSE
                BEGIN
                INSERT INTO DBRstage (UpdateType, ObjectType, KeyFlds, KeyVals, KeyTypes, ModFlds, ModVals, ModTypes)
                SELECT 'MODIFY', 'INMASTX', 'fac|fPartNo|fRev',
                 RTRIM(d.fac) + '|' + RTRIM(d.fpartno) + '|' + RTRIM(d.frev), 'CCC',
				 @ModFlds, @ModVals, @ModTypes
				 FROM inserted i, deleted d
				 WHERE i.Identity_Column=d.Identity_Column
            END
		END
	END
END

END
SET NOCOUNT OFF

Kristen · November 18, 2016, 4:29pm

The trigger assumes that only one row is being updated. That's an erroneous, and hugely serious, mistake.

Also, that trigger is on the table [inmastx] and NOT on [partMaster]

Here are my comments related to the trigger, but as it is not related to [partMaster] it probably has nothing to do with your problem.

If fnDBRMod in your update is 3 then the trigger will assemble some sort of list of changes and insert them into [DBRstage]. IF that is a staging table for some other process then you need to be very careful making changes to this table. That will DEFINITELY not work for a multiple-row update

Its also a horrible, inefficient way to log changes.

The trigger needs fixing so that it handles multiple rows.

Then only change that THIS trigger makes to the original table is to SET fnDBRMod=0 as follows:

DECLARE @DBRMod Int
SELECT @DBRMod = i.fnDBRMod FROM Inserted i

IF @DBRMod = 2
/* This Update was initiated by DBR, so we need to reset the field but not load staging table. */
BEGIN
    UPDATE INMASTX SET fnDBRMod=0 FROM INMASTX im, INSERTED i
		WHERE im.Identity_Column=i.Identity_Column AND im.fnDBRMod=2
END

You said earlier that there were two triggers, those are the ones that you need to review.