Database Administrators Asked by Kulstad on January 5, 2021
I am loading hundreds of order files (positional flat files), via SSIS, into staging tables, and ultimately into production tables. As part of the staging table population process, I would like to update 2 fields in the staging table that are not part of the original flat file (NewListPrice and NewPriceVersion), however I need to use 3 columns of the newly-inserted record in my function (Company, CustomerNumber and SLIN/UPC). I have created both a table-valued function and an INSERT trigger on my staging table, but it is definitely not working as intended.
CREATE TRIGGER [Staging].[INSERT_New_Pricing_tg]
ON [Staging].[OrderDetail01_tb]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
UPDATE Staging.OrderDetail01_tb
SET NewListCost = A.CustomerPrice,
NewPriceVersion = A.NewPriceVersion
FROM (SELECT LU.CustomerPrice,
LU.NewPriceVersion
FROM inserted I
CROSS APPLY (SELECT * FROM dbo.Find_New_Price_fn(I.Company, I.CustomerNumber, I.UPC)) LU) A
END
CREATE FUNCTION [dbo].[Find_New_Price_fn](@COMPANY varchar(2) = NULL, @CUSTOMER_NUMBER varchar(6) = NULL, @SLIN varchar(20) = NULL)
RETURNS @NEW_PRICE_DETAILS TABLE (CustomerPrice varchar(10) NULL, NewPriceVersion varchar(50) NULL)
AS
BEGIN
IF (@COMPANY IS NULL) OR (@CUSTOMER_NUMBER IS NULL) OR (@SLIN IS NULL)
RETURN
ELSE
BEGIN
DECLARE @ITEM_NUMBER varchar(6),
@CARTON_UPC varchar(20)
-- look up the item number in the Cross-Reference table, using the SLIN
SELECT @ITEM_NUMBER = LTRIM(RTRIM(ItemCode))
FROM dbo.ItemXRef_tb IX
WHERE MarkerNumber = '8001' -- customer master group number
AND Company = @COMPANY
AND ReferenceItemCode = @SLIN
IF @ITEM_NUMBER IS NULL
RETURN
ELSE
-- find the item in the Items table, based on the item number returned
SELECT @CARTON_UPC = LTRIM(RTRIM(CartonUpc))
FROM Items_tb
WHERE Company = @COMPANY
AND ItemNumber = RIGHT('000000' + LTRIM(RTRIM(@ITEM_NUMBER)), 6)
IF @CARTON_UPC IS NULL
RETURN
ELSE
-- find the new price of the item using the CartonUPC of the item
INSERT INTO @NEW_PRICE_DETAILS(CustomerPrice, NewPriceVersion)
SELECT RetailerNetPrice,
PriceVersion
FROM PriceFile_tb PF
WHERE StoreID = CONCAT((SELECT CAST(ExternalPrefix as varchar) FROM Lookups.CompanyPrefixes_tb WHERE CompanyAbbreviation = @COMPANY), RIGHT('000000' + LTRIM(RTRIM(@CUSTOMER_NUMBER)), 6))
AND ProductUPCCode = LTRIM(RTRIM(@CARTON_UPC))
AND PriceEffDateFirst = FORMAT(GETDATE(), 'yyyyMMdd')
END
RETURN
END
I am fully aware, and completely expect, that not all records will return NewListPrice and NewPriceVersion values. I have tried calling the function directly with values I know will return a result, and values I know that won’t return a result, and the function works as expected in these cases, however it does not work when I put the function call in my trigger. I am not sure where I am going wrong with this.
With Scott Hodgin's help (see comment thread above), I was able to figure out the issue:
First issue: Since I certainly was using an OLE DB destination, I needed to add FIRE_TRIGGERS in the Advanced Editor (Microsoft, make this easier to find)
Second issue: once the INSERT trigger was firing, it was updating every record in the table, not just the ones where the function returned a value. Of course it was; there was no WHERE clause or INNER JOIN to limit what needed to be update. I modified my INSERT trigger to include the fields from the INSERTED table, and added a WHERE clause as follows:
CREATE TRIGGER [Staging].[INSERT_New_Pricing_tg]
ON [Staging].[OrderDetail01_tb]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
UPDATE Staging.OrderDetail01_tb
SET NewListCost = A.CustomerPrice,
NewPriceVersion = A.NewPriceVersion
FROM (SELECT I.Company,
I.CustomerNumber,
I.UPC,
LU.CustomerPrice,
LU.NewPriceVersion
FROM inserted I
CROSS APPLY (SELECT * FROM dbo.Find_New_Price_fn(I.Company, I.CustomerNumber, I.UPC)) LU) A
WHERE Company = A.Company
AND CustomerNumber = A.CustomerNumber
AND UPC = A.UPC
END
Thank you very much Scott for starting me on the right path. Without your help and that link, I never would have solved this.
Answered by Kulstad on January 5, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP