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.