Database trigger for saving discount amounts

[markh76]

I need to run two reports from the PS database, first is an invoice summary report from orders, 2nd is an item detail report from order_details. 

 

I noticed that if a cart rule is used for a discount, the discount amount is saved in the orders table but does not migrate down to the product level. I was able to solve this by adding after insert trigger on order_cart_rule to update order_detail to place a discount amount for each line item. 

 

Now trying to do it for catalog price rules. With catalog price rules, the discount saves to the order_detail table but doesn't roll up the the orders table.

 

I do a lot of mysql work but not much php and can't find a good table to trigger off of when an order is saved to migrate the line item order_details discount total to the orders table. 

 

Anyone have any suggestions on where the order save sequence is in the php? I want to grab the last table that is updated that has id_order. I don't want to use after insert on order_detail because the trigger would be called for each line item. Only want to call it once. 

 

Unless there is something I'm missing about why these values don't get saved in both tables? There also doesn't seem to be a way to back track on catalog discounts to see which is performing best via order history, is that true?

 

Thanks..

[markh76]

While not perfect, works for simple cart rules that apply a global discount to the cart so discount flows to order_detail level.

 

DELIMITER $$

 

DROP TRIGGER  `order_rule`$$

 

CREATE

    TRIGGER `order_rule` AFTER INSERT ON `order_cart_rule` 

    FOR EACH ROW 

    BEGIN

    

    -- determine if an order has a cart rule

    -- determine value of cart rule

    -- save discount amount to field in order_detail

    

SET @reduction=0;

SET @cartrule=0;

  

SET @cartrule=new.id_cart_rule;

    

    -- determine if cart has a rule

 

IF @cartrule>0 THEN

BEGIN

SELECT reduction_percent FROM `cart_rule` WHERE id_cart_rule=@cartrule INTO @reduction;

 

IF @reduction>0 THEN

BEGIN

UPDATE order_detail SET reduction_amount=(unit_price_tax_excl*(@reduction/100))*product_quantity WHERE order_detail.id_order=new.id_order;

END;

END IF;

END;

END IF;

END;

$$

 

DELIMITER ;