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 Friday, July 24, 2015 8:33 PM Better forum
Other recent topics
