mysql触发器 求和

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    total_amount DECIMAL(10, 2)
);

CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT,
    item_price DECIMAL(10, 2),
    quantity INT
);

DELIMITER //

CREATE TRIGGER update_total_amount
AFTER INSERT ON order_details
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total_amount = total_amount + NEW.item_price * NEW.quantity
    WHERE order_id = NEW.order_id;
END //

DELIMITER ;

INSERT INTO orders (order_id) VALUES (1);

INSERT INTO order_details (detail_id, order_id, item_price, quantity) VALUES (1, 1, 10.00, 2);
INSERT INTO order_details (detail_id, order_id, item_price, quantity) VALUES (2, 1, 5.00, 3);

SELECT * FROM orders;

+----------+-------------+
| order_id | total_amount|
+----------+-------------+
|        1 |       35.00 |
+----------+-------------+