Update staging table records in SQL Server 2017 with INSERT trigger and table-valued function, using INSERTED records

Question

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.

Kulstad · Answer

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.

Update staging table records in SQL Server 2017 with INSERT trigger and table-valued function, using INSERTED records

One Answer

Add your own answers!

Ask a Question