Help with trigger

[tonys]

Hi Mike,

I want to create a trigger to update the in_sellingprice.forwardprice1 field based on a calcuation from a customsettings inventory maintenance.
I have created a custom setting with the ID of P1GP.

Also the contents of the customsetting may need to be converted to a number, although it is entered as a number in Jiwa already.


I have the following, but it does not seem to work. Could you please help.

update in_sellingprices
set ForwardPrice1 =

(SELECT In_Main.LCost * (1+ IN_CustomSettingValues.Contents/100)
FROM IN_SellingPrices, IN_Main INNER JOIN
IN_Main ON IN_SellingPrices.InventoryID = IN_Main.InventoryID INNER JOIN
IN_CustomSettingValues ON IN_Main.InventoryID = IN_CustomSettingValues.InventoryID and
IN_CustomSettingValues.SettingID = 'P1GP')

[Mike.Sheen]

Hi Tony,

Try this:

Code: Select all

UPDATE IN_SellingPrices
SET ForwardPrice1 =
   COALESCE((SELECT IN_Main.LCost *  (1 + CAST((IN_CustomSettingValues.Contents) AS DECIMAL(19,6)) / 100)
    FROM IN_Main
    JOIN IN_CustomSettingValues ON IN_Main.InventoryID = IN_CustomSettingValues.InventoryID AND IN_CustomSettingValues.SettingID = 'P1GP'
    WHERE IN_SellingPrices.InventoryID = IN_Main.InventoryID), 0)

[tonys]

Hi Mike,

nearly.

I tried that but it updated 10,000 + rows where I was only expecting 1 as there is only one part no with a value in the in_customsettings table at this point.
I tried to add the in_customesettings.inventoryID = in_main.inventoryID to the where clause but that did not work for me. I must have the syntax wrong

[Mike.Sheen]

Hi Tony,

If this is to be used in a trigger, what table is the trigger on, and what event (insert / update / delete) ?

The reserved table inserted should be joined inside the trigger to isolate the set to be only the rows related to the trigger event.

Mike

[tonys]

Hi Mike,

I want it to be on an update of the in_main table.

Regards
Tony

[Mike.Sheen]

Hi Tony,

This should do what you want.

Code: Select all

CREATE TRIGGER IN_Main_Update_SetSellingPrice ON IN_Main AFTER UPDATE
AS
   SET NOCOUNT ON

   UPDATE IN_SellingPrices
   SET ForwardPrice1 = COALESCE(i.LCost *  (1 + CAST((IN_CustomSettingValues.Contents) AS DECIMAL(19,6)) / 100), 0)
   FROM inserted i   
   JOIN IN_CustomSettingValues ON IN_CustomSettingValues.InventoryID = i.InventoryID
   WHERE IN_CustomSettingValues.SettingID = 'P1GP'   
   AND IN_SellingPrices.InventoryID = i.InventoryID
GO

[Mike.Sheen]

I should add that you can selectively only update the ForwardPrice1 based on what field was updated in IN_Main - so you could only perform the update if IN_Main.LCost changed - which I think is what you were trying to do.

It may or may not make a material difference in performance, depending on the number of Inventory items and the nature of the workloads imposed - but it is a something to consider.

To do so, inside the trigger simply wrap the update in a conditional check:

Code: Select all

CREATE TRIGGER IN_Main_Update_SetSellingPrice ON IN_Main AFTER UPDATE
AS
   SET NOCOUNT ON

    IF UPDATE (LCost)
    BEGIN
       UPDATE IN_SellingPrices
       SET ForwardPrice1 = COALESCE(i.LCost *  (1 + CAST((IN_CustomSettingValues.Contents) AS DECIMAL(19,6)) / 100), 0)
       FROM inserted i   
       JOIN IN_CustomSettingValues ON IN_CustomSettingValues.InventoryID = i.InventoryID
       WHERE IN_CustomSettingValues.SettingID = 'P1GP'   
       AND IN_SellingPrices.InventoryID = i.InventoryID
    END
GO

PS: I don't like triggers.

[tonys]

Thanks Mike, that worked a treat on my test system. I will test if there are blank custom field values.
This will be a intrim messure as when this client updates to V7, we can drop the triggers and re progam as a plugin and then charge them for it as well.

I think triggers are painful as cause issues with update etc to tables when you di Jiwa Updates, but at least will help for a couple of months until we migrate.

[tonys]

works fine if it is a zero so thats great and returns an error if you create a new part and leve the custom settings as null so that is fine as well as it will force them to setup correctly.

[tonys]

I added a Cat5ID so I could restrict the function for items that do not has such pricing like Postage, machine Charges etc.
Included the following criteria.



and I.Catagory5ID = '0000000000000000010R'