面试题：MySQL触发器在复杂业务逻辑中的应用

Question

假设存在`orders`表（包含订单信息）、`order_items`表（包含订单详情）和`products`表（包含产品信息）。当在`order_items`表插入一条新记录时，需要更新`products`表中对应产品的库存数量，同时根据订单总金额（`order_items`表中数量与`products`表中价格计算得出）更新`orders`表中的总金额字段。请写出实现此业务逻辑的MySQL触发器代码。

Answer 1

-- 创建插入order_items表后的触发器
DELIMITER //

CREATE TRIGGER after_order_items_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    -- 更新products表中的库存数量
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;

    -- 更新orders表中的总金额字段
    UPDATE orders
    SET total_amount = (SELECT SUM(oi.quantity * p.price)
                        FROM order_items oi
                        JOIN products p ON oi.product_id = p.product_id
                        WHERE oi.order_id = NEW.order_id)
    WHERE order_id = NEW.order_id;
END //

DELIMITER ;