My appologies if this is the wrong location for this, Mods, please feel free to relocate this post as appropriatewe have a SQL 2008 trigger that is not preforming as expected.instead of updating the package table by shipmentnumber summing the WEIGHT, FRIEGHTOVERIDE and FREIGHTCOST with common shpmentnumber from our UPS package table.

Our sys admin implemented this code into production before it was fully tested and approved for use and is now having to restore the package table as it has the same value for all records in the weight freightoverride and freightcost of the last id that was inserted into the table. I need to get this back on track and  resolve the original issue if possible. Any assistance would be appreciated.


[quote]

USE [e32TestCompany]
GO
/****** Object: Trigger [dbo].[UPSPackage_Package_I_TR] Script Date: 07/23/2015 15:33:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[UPSPackage_Package_I_TR]

ON [dbo].[UPSPackage]
FOR INSERT
AS

BEGIN
DECLARE @LastID INT = NULL

UPDATE Package
SET Package.TrackingNumber = Inserted.TrackingNumber,
Package.WEIGHT = CONVERT(REAL, Inserted.WEIGHT),
Package.ShipMethod = Inserted.ShipMethod,

Package.FreightOverrideAmount = (SELECT CASE WHEN Shipment.ThirdPartyBilling = '' AND LEFT(OrderHeader.OutsideOrderID,2) NOT IN ('IC','AF','RB','CM','DA') AND Package.PackageID = CONVERT(INT,Inserted.ShipmentNumber) THEN CONVERT(MONEY, Inserted.FreightOverrideAmount)
WHEN Shipment.ThirdPartyBilling = '' AND LEFT(OrderHeader.OutsideOrderID,2) IN ('IC','AF','RB','CM','DA') AND Package.PackageID = CONVERT(INT,Inserted.ShipmentNumber) THEN Package.FreightOverrideAmount ELSE 0 END
FROM Package
INNER JOIN Shipment ON Package.ShipmentNumber = Shipment.ShipmentNumber AND Package.PackageID = CONVERT(INT, Inserted.ShipmentNumber)
INNER JOIN OrderHeader ON Package.JobNumber = OrderHeader.JobNumber),

Package.FreightCost = CONVERT(MONEY, Inserted.FreightCost),
Package.ShipDate = CONVERT(DATETIME, Inserted.UPSDatetime),@LastID = CONVERT(INT, Inserted.ShipmentNumber)
FROM Package, Inserted

WHERE Package.PackageID = CONVERT(INT, Inserted.ShipmentNumber)



MERGE dbo.Package AS pkg
USING (SELECT ShipmentNumber, SUM(Cast (Weight as float)) Weight, SUM(Cast (FreightCost as float)) FreightCost, SUM(Cast (FreightOverrideAmount as float)) FreightOverrideAmount
FROM dbo.UPSPackage where ShipmentNumber = @LastID
GROUP BY ShipmentNumber) as ups1 ON ups1.ShipmentNumber = @LastID
When MATCHED THEN UPDATE SET Weight =ups1.Weight,FreightCost = ups1.FreightCost,FreightOverrideAmount = ups1.FreightOverrideAmount
When NOT MATCHED THEN INSERT (ShipmentNumber,Weight,FreightCost,FreightOverrideAmount) VALUES(ups1.ShipmentNumber, ups1.weight,ups1.FreightCost,ups1.FreightOverrideAmount);



END
[/quote]

.

• Moved by Naomi NModerator 10 hours 34 minutes ago Better forum

Can you post the tables structures (bare minimum) and what exactly do you want to achieve with this trigger?

The assignment of @LastId in the UPDATE looks very strange to me. You'd rather use OUTPUT clause of the UPDATE command.

In any case, it's hard to understand what this code is doing, so you may want to provide some sample data and desired result.

The code is not particularly readable, and we don't know the business rules. On top of all, you did not say what "performing as expected" means.

But as Naomi points out, this part in the UPDATE statement looks spooky:

@LastID = CONVERT(INT, Inserted.ShipmentNumber)

Or to be blunt: it looks wrong. What if multiple rows are inserted at the same time? Should we still only use one id in the MERGE statement? And then just any ID? I find that difficult to believe.

so the UPSpackage table has many duplicate shipment numbers on unique records that have each tracking number for that shipments weioght, freight cost, and the freight cost override. The package table has only unique shipment numbers that the trigger writes the weight freight cost and freight cost override to.

It was discovered that it was not written to SUM all records with the same shipment numbers and combine them into a single record in the package table and that instead an employee was manually correcting this. The project was assigned and delegated to rewrite the trigger to include the sum of each of those fields as part of writing the single unique record of the shipment number.

I've been here a month or two and have barely gotten together a basic project management plan together because of issues like this where they have fired off projects without allowing full testing (yes I am about to pull out my hair, my experience and use is limited with SQL to about 5 years ago and while I can follow along and spot issues they tend to be a little trigger happy leaving me to solve issues like this one)

Here is the UPS Package table sample data

ID	ShipMethod	JobNumber	Weight	TrackingNumber	ShipDate	FreightCost	PackageID	UPSDatetime	FreightOverrideAmount
161089	FEDEX	89231	13.8	3080969942	NULL	116.51	598501	2/25/2015	547.69
161097	FEDEX	89231	35	3080970082	NULL	142.69	598502	2/25/2015	670.74
161098	FEDEX	89231	28	3080971515	NULL	141.64	598503	2/25/2015	616.71
161099	FEDEX	89231	4	3080971644	NULL	107.97	598501	2/25/2015	502.79
161100	FEDEX	89231	12	3080971751	NULL	141.64	598505	2/25/2015	616.71
161101	FEDEX	89231	2.5	3080971913	NULL	107.97	598502	2/25/2015	415.42
161102	FEDEX	89231	1.4	3080972075	NULL	164.94	598510	2/25/2015	775.34
161103	FEDEX	89231	12.5	3080972204	NULL	107.97	598501	2/25/2015	422.49
161104	FEDEX	89231	4.3	3080972252	NULL	141.64	611627	2/25/2015	633.95
161106	FEDEX	89231	7	3080972440	NULL	141.64	598502	2/25/2015	594.22
161107	FEDEX	89231	8	3080972591	NULL	107.97	598520	2/25/2015	422.49
161108	FEDEX	89231	9.7	3080972672	NULL	107.97	611627	2/25/2015	428.63
161109	FEDEX	89231	6	3080972775	NULL	141.64	598501	2/25/2015	578.11
161110	FEDEX	89231	1.4	3080973022	NULL	141.64	598529	2/25/2015	638.7
161111	FEDEX	89231	4.4	3080973221	NULL	141.64	598534	2/25/2015	578.11
161114	FEDEX	89231	1.4	3080973280	NULL	111.61	611627	2/25/2015	524.64
161115	FEDEX	89231	2.7	3080973420	NULL	141.64	598501	2/25/2015	578.11
161116	FEDEX	89231	9.4	3080973534	NULL	108.44	598538	2/25/2015	509.76
161117	FEDEX	89231	6	3080973604	NULL	107.97	598539	2/25/2015	333.89
158749	FEDEX	cwo#2216 benjamin ma	1	3080402323	NULL	361.93	611627	2/11/2015	1704.6
158653	FEDEX	90854	2.9	3080318780	NULL	108.79	611783	2/9/2015	512.37
160986	FEDEX	91944	3.4	3080917674	NULL	100.78	611627	2/24/2015	313.7
161031	FEDEX	88756	2.75	3080944834	NULL	107.8	611627	2/24/2015	307.77
158911	FEDEX	15715&15718	13.6	3080541170	NULL	113.53	598501	2/13/2015	534.71

and here is the same data after it has been correctly updated in the package table.

ID	ShipMethod	JobNumber	Weight	TrackingNumber	ShipDate	FreightCost	ShipmentNumber	UPSDatetime	FreightOverrideAmount
158911	FEDEX	15715&15718	52.6	3080541170	NULL	729.26	598501	2/13/2015	3163.9
161106	FEDEX	89231	44.5	3080972440	NULL	392.3	598502	2/25/2015	1680.38
161098	FEDEX	89231	28	3080971515	NULL	141.64	598503	2/25/2015	616.71
161100	FEDEX	89231	12	3080971751	NULL	141.64	598505	2/25/2015	616.71
161102	FEDEX	89231	1.4	3080972075	NULL	164.94	598510	2/25/2015	775.34
161107	FEDEX	89231	8	3080972591	NULL	107.97	598520	2/25/2015	422.49
161110	FEDEX	89231	1.4	3080973022	NULL	141.64	598529	2/25/2015	638.7
161111	FEDEX	89231	4.4	3080973221	NULL	141.64	598534	2/25/2015	578.11
161116	FEDEX	89231	9.4	3080973534	NULL	108.44	598538	2/25/2015	509.76
161117	FEDEX	89231	6	3080973604	NULL	107.97	598539	2/25/2015	333.89
161031	FEDEX	88756	22.55	3080944834	NULL	931.73	611627	2/24/2015	3913.29
158653	FEDEX	90854	2.9	3080318780	NULL	108.79	611783	2/9/2015	512.37

and here is the code again better formated

USE [e32TestCompany]
GO
/****** Object: Trigger [dbo].[UPSPackage_Package_I_TR] Script Date: 07/23/2015 15:33:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[UPSPackage_Package_I_TR]

ON [dbo].[UPSPackage]
FOR INSERT
AS

BEGIN
DECLARE @LastID INT = NULL

UPDATE Package
SET Package.TrackingNumber = Inserted.TrackingNumber,
Package.WEIGHT = CONVERT(REAL, Inserted.WEIGHT),
Package.ShipMethod = Inserted.ShipMethod,

Package.FreightOverrideAmount = (SELECT CASE WHEN Shipment.ThirdPartyBilling = '' AND LEFT(OrderHeader.OutsideOrderID,2) NOT IN ('IC','AF','RB','CM','DA') AND Package.PackageID = CONVERT(INT,Inserted.ShipmentNumber) THEN CONVERT(MONEY, Inserted.FreightOverrideAmount)
WHEN Shipment.ThirdPartyBilling = '' AND LEFT(OrderHeader.OutsideOrderID,2) IN ('IC','AF','RB','CM','DA') AND Package.PackageID = CONVERT(INT,Inserted.ShipmentNumber) THEN Package.FreightOverrideAmount ELSE 0 END
FROM Package
INNER JOIN Shipment ON Package.ShipmentNumber = Shipment.ShipmentNumber AND Package.PackageID = CONVERT(INT, Inserted.ShipmentNumber)
INNER JOIN OrderHeader ON Package.JobNumber = OrderHeader.JobNumber),

Package.FreightCost = CONVERT(MONEY, Inserted.FreightCost),
Package.ShipDate = CONVERT(DATETIME, Inserted.UPSDatetime),@LastID = CONVERT(INT, Inserted.ShipmentNumber)
FROM Package, Inserted

WHERE Package.PackageID = CONVERT(INT, Inserted.ShipmentNumber)



MERGE dbo.Package AS pkg
USING (SELECT ShipmentNumber, SUM(Cast (Weight as float)) Weight, SUM(Cast (FreightCost as float)) FreightCost, SUM(Cast (FreightOverrideAmount as float)) FreightOverrideAmount
FROM dbo.UPSPackage where ShipmentNumber = @LastID
GROUP BY ShipmentNumber) as ups1 ON ups1.ShipmentNumber = @LastID
When MATCHED THEN UPDATE SET Weight =ups1.Weight,FreightCost = ups1.FreightCost,FreightOverrideAmount = ups1.FreightOverrideAmount
When NOT MATCHED THEN INSERT (ShipmentNumber,Weight,FreightCost,FreightOverrideAmount) VALUES(ups1.ShipmentNumber, ups1.weight,ups1.FreightCost,ups1.FreightOverrideAmount);

I asked the same questions that you did Earland and then came in this morning to find that they implemented this mess before I could review test and sign off on it....

• Edited by luke102280 8 hours 5 minutes ago Shipmentnumber is called packageid in the ups package table

My suggestion would be 

1. Write a select statement that gets information from the SPPackage table and produces desired result with correct calculation.

2. Once you get this select statement working correctly and tested, turn it into the merge command to update information for existing packages / insert new information.

3. Once this get tested (probably on a copy of the packages table), add also Audit table. In that audit table you would output the result of the merge command using OUTPUT clause along with the $action and current date and may be current user.

4. Finally, once all of these have been tested, turn the above into a trigger.

This way you will have correct trigger's code and also Audit of what exactly has been done when rows have been added into SPPackage table.