Is there anything wrong with this mysql trigger?

I have a product, sales_order, and sales_order_product table.

The nature of the products dictate frequent changes to price and weight.

The customers often times save orders for a few days before submitting and paying for them.

Because of this lag and the pr开发者_如何学Cice/weight fluctuations, we allow them to freeze in the price/weight at the time of the original order.

We save the order amount (subtotal), sales tax, order weight, among a few other things in the sales_order table.

I know it's not recommended to have any business logic within the database layer though I see this as more of a means to maintain referential integrity.

Below is one of the triggers I use to calculate the aforementioned data. I've only started to test it out and so far so good. Performance wise, I haven't seen any issues thus far but my testing hasn't been very extensive.

Is there anything about this trigger that doesn't look right? I ask because though I've used triggers for things like time stamps I've never really used them in this capacity (and considering we are talking about money, I don't want to screw something up that could lose me my job).

I realize it's probably not a good idea to hard code the tax rate, and I'll probably change that when the time comes.

CREATE TRIGGER after_sales_order_product_update
AFTER UPDATE ON sales_order_product
FOR EACH ROW
BEGIN

    SET @product_amount = (SELECT SUM(quantity * unit_price) 
                           FROM sales_order_product 
                           WHERE sales_order_id = OLD.sales_order_id),
        @product_discount = (SELECT SUM(quantity * unit_discount) 
                             FROM sales_order_product 
                             WHERE sales_order_id = OLD.sales_order_id),
        @total_weight = (SELECT SUM(quantity * unit_weight) 
                         FROM sales_order_product 
                         WHERE sales_order_id = OLD.sales_order_id),
        @tax_amount = ROUND(@product_amount * 0.0975,2);

    UPDATE sales_order 
    SET product_amount = @product_amount,
        product_discount = @product_discount,
        tax_amount = @tax_amount,
        total_weight = @total_weight,
        product_total = product_amount - product_discount
    WHERE sales_order_id = OLD.sales_order_id;

END

---

Your design looks good; just a few thoughts:

1. Your trigger is only fired after an UPDATE. What about when a row is first INSERTed, or DELETEd later?
2. What about NULLs? If any of the core fields (quantity, unit_price, unit_discount, unit_weight) could be null, that will give you nulls in the derived fields which you might not be expecting.
3. No rows (NULLs again). Could a sales_order have no sales_order_products, say if the customer deletes the one row from their shopping cart? In that case you probably want the sales_order values to be zero, which might need special coding.
4. Order status. What happens after the order goes through? After the sale is finished, those values should be fixed no matter what else is going on (changes to tax rates etc), so you might need your trigger to check the order status before updating anything.

You can probably sort all these out by doing some storyboard/scenario-based testing.