MySQL automatically updates field when data is inserted
I am making a web application for clients to order anti-anti-aging items. For this, I created a MySQL database that has the following tables:
-
customers
-
orders
-
orders-items
-
products
The table customers
contains all information about a person, for example:
- Client id for primary key and auto-increment (id)
- First name (first_name)
- Last name (last_name)
- Email address (email_adress)
- Customer information (customer_info)
Example:
The table orders
contains all the specific information about him, for example:
- Order id for primary key and auto-increment (id)
- Customer ordered by customer associated with a field
id
from tablecustomers
(customer_id) - Order information (order_info)
- The location to which the order is to be shipped (location)
- The total cost the client has to pay (total_price)
- When the order was created (created)
Example:
The table orders-items
lists all the items that each customer ordered, this is related to order-id
from the previous table.
- Identifier, for primary key and auto-increment, is not used for any relationship (id)
- The order ID used for a product is for an order. It has to do with a field
id
from the tableorders
(order_id) - The product ID that is used to order the product is associated with the id field from the table
products
. (Product_id) - Quantity of ordered product (quantity)
Example:
The table products
shows all the information about the products:
- The identifier for the primary key and auto-increment. This is related to the field
product_id
from the tableorder_items
(id) - Product name (name)
- Product Description (Descriptions)
- Product price (price)
Example:
Question:
I have this request:
SELECT `orders-items`.`order_id` , SUM(`orders-items`.`quantity`* `products`.`price`) total
FROM `orders-items`
INNER JOIN `Products` ON `orders-items`.`products_id` = `products`.`id`
And he shows me a list of all the common prices that everyone has to pay order_id
.
But how to do this so that this value total_price
everyone has order_id
to pay is automatically inserted into the table orders
inside the box total_price
on the right order_id
when the product is inserted into the table orders-list
?
Or better yet, don't track total_prices
customers
to pay?
source to share
Several things to consider.
Availability is total_price
redundant for yourself. You can find out this amount by adding up the prices of these order items at any time. It might be interesting to have it for performance reasons, but is it really necessary for your scenario? This rarely happens.
It would be useful to use price
for each order_item
on a different side And why this is because the prices of thoses products may change in the future and you don't want to lose track of how much they were sold during that particular sale.
Either way, you can update yours total_price
with triggers like this:
DELIMITER $$
CREATE TRIGGER order_items_insert AFTER INSERT ON `orders-items` FOR EACH ROW
BEGIN
UPDATE orders o INNER JOIN (SELECT i.order_id id, SUM(i.quantity * p.price) total_price FROM `orders-items` i INNER JOIN products p ON p.id = i.products_id AND i.order_id = new.order_id) t ON t.id = o.id SET o.total_price = t.total_price;
END$$
CREATE TRIGGER order_items_update AFTER UPDATE ON `orders-items` FOR EACH ROW
BEGIN
UPDATE orders o INNER JOIN (SELECT i.order_id id, SUM(i.quantity * p.price) total_price FROM `orders-items` i INNER JOIN products p ON p.id = i.products_id AND i.order_id = new.order_id) t ON t.id = o.id SET o.total_price = t.total_price;
END$$
CREATE TRIGGER order_items_delete AFTER DELETE ON `orders-items` FOR EACH ROW
BEGIN
UPDATE orders o INNER JOIN (SELECT i.order_id id, SUM(i.quantity * p.price) total_price FROM `orders-items` i INNER JOIN products p ON p.id = i.products_id AND i.order_id = old.order_id) t ON t.id = o.id SET o.total_price = t.total_price;
END$$
DELIMITER ;
source to share
One option would be trigger
that is executed when a new client is inserted.
You can use an alternative stored procedure
. With this, you only need to call the procedure InsertCustomer
and the database will handle the price update for you → you will not have these dependencies in your application.
Another way could be doing 2 queries (insert and update) in transaction
. But for this I recommend Domain Driven Design
. You will have a service that has a method CreateCustomer(Customer $customer)
that executes an insert request and then an update request. If successful, it commits the transaction and returns true, if not successful, it cancels the transaction and retries false. It should be your own convention to only manipulate data with services (that know the business logic).
source to share