0

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.

  • 1
    Since you're using SSIS to load the data, take a look at Why aren't my triggers firing during an insert by SSIS?. That links points out that OLE DB Destination task uses a bulk insert, triggers are not fired by default., so perhaps that's your problem? – Scott Hodgin Sep 7 at 11:19
  • @ScottHodgin you should turn this into an answer, I don't think a better explanation is going to come along. – Erik Darling Sep 7 at 14:58
  • @ScottHodgin, I wonder why something so intrinsic would need to be hidden in AdvancedSettings, and without a "checkbox option", too? I just tried this, but it doesn't seem to have made a difference – Kulstad Sep 7 at 15:02
  • @Kulstad- IF you manually insert a row into the target table such that the trigger should fire, does the trigger fire and update the rows accordingly? If it does, then my suspicion is the problem lies in SSIS – Scott Hodgin Sep 7 at 15:06
  • @ScottHodgin - yes, I have tried a manual one-at-a-time approach, and it works fine. I did make one small modification to my SSIS data task, and now it appears to be kind of working, but still not as expected. I moved the record parsing (order file contains H01, D01, and S01 records for Header, Detail, and Summary) for the detail and summary away from the header parsing, and now the trigger fires, but every detail record gets updated, whether it contains a match or not (not sure if this fundamentally changes my question or not) – Kulstad Sep 7 at 15:44
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.