在MariaDB中基于双表数据创建自动计算列：为Pedido表添加amount列

Hey there! Let's work through how to add that amount column to your Pedido table, where the value automatically calculates as Pedido.cant * Producto.precio.

First, a quick heads-up: MySQL doesn’t support generated columns (auto-calculated table columns) that reference data from other tables. So we’ve got two reliable ways to solve this—let’s break them down.

Option 1: Create a View (Recommended) #

This is the cleanest approach because it avoids storing redundant data, and the amount value will always stay up-to-date whenever you query it. A view acts like a virtual table that combines data from Pedido and Producto on the fly.

Here’s the SQL to create the view:

CREATE VIEW PedidoWithAmount AS
SELECT 
    p.numpedido,
    p.fechapedido,
    p.clie,
    p.resp,
    p.fab,
    p.producto,
    p.cant,
    (p.cant * pr.precio) AS amount
FROM Pedido p
JOIN Producto pr 
    ON p.fab = pr.idfab 
    AND p.producto = pr.idproducto;

Use it just like a regular table:

SELECT * FROM PedidoWithAmount;

This will return all your Pedido data plus the calculated amount column, matching your example results (like 100 * 7 = 700 for the first order).

Option 2: Add a Physical Column with Triggers #

If you need the amount column to be part of the actual Pedido table (for indexing or specific app needs), you can add a physical column and use triggers to keep its value updated automatically.

Step 1: Add the amount column to Pedido #

ALTER TABLE Pedido ADD COLUMN amount INT;

Step 2: Create triggers to maintain the amount value #

We need three triggers to cover all scenarios: inserting a new order, updating an order’s cant, and updating a product’s precio (since that affects existing orders).

Trigger for new order inserts

DELIMITER //
CREATE TRIGGER trg_pedido_insert_amount
BEFORE INSERT ON Pedido
FOR EACH ROW
BEGIN
    SET NEW.amount = NEW.cant * (
        SELECT precio 
        FROM Producto 
        WHERE idfab = NEW.fab AND idproducto = NEW.producto
    );
END //
DELIMITER ;

Trigger when updating an order’s cant

DELIMITER //
CREATE TRIGGER trg_pedido_update_cant
BEFORE UPDATE ON Pedido
FOR EACH ROW
BEGIN
    SET NEW.amount = NEW.cant * (
        SELECT precio 
        FROM Producto 
        WHERE idfab = NEW.fab AND idproducto = NEW.producto
    );
END //
DELIMITER ;

Trigger when updating a product’s precio

DELIMITER //
CREATE TRIGGER trg_producto_update_precio
AFTER UPDATE ON Producto
FOR EACH ROW
BEGIN
    UPDATE Pedido
    SET amount = cant * NEW.precio
    WHERE fab = NEW.idfab AND producto = NEW.idproducto;
END //
DELIMITER ;

Pros & Cons of This Approach #

• Pros: The amount column is stored physically, so you can add indexes to speed up queries, and you don’t need to join tables every time you need the value.
• Cons: You have to maintain multiple triggers, and updating a product’s price will trigger updates to all related orders—this could cause performance issues with large datasets.

内容的提问来源于stack exchange，提问作者José Cristian Fernandez Tirado