从另一个表中的另一列计算列值

Question

我有一个包含商店中每个商品单价和其他细节的表格。

CREATE TABLE Item (Item_id CHAR(4), Description CHAR(40), Sizes CHAR(40), Weight REAL, Unit_price REAL, PRIMARY KEY(Item_id));

还有一个包含每个订单中所包含项目细节的订单。

CREATE TABLE Order_contains_items (Item_id CHAR(4), Order_no INT, Quantity_ordered INT, Ordered_price REAL, PRIMARY KEY(Item_id, Order_no), FOREIGN KEY(Order_no) REFERENCES Order(Order_no), FOREIGN KEY(Item_id) REFERENCES Item(Item_id));

现在我想计算。

Order_contains_items.Ordered_price = Item.Unit_price * Order_contains_items.Quantity_ordered WHERE Order_contains_items.Item_id = Item.Item_id

请注意，我希望它成为表格本身的一部分，而不是作为不同视图或查询。我该怎么做？我研究了触发器和其他机制，但它们是否适用于不同表中的值，特别是在存在这样的约束条件下？

我尝试了以下基于另一列计算的列的触发器：

CREATE TRIGGER Order_price BEFORE INSERT ON Order_contains_items
    FOR EACH ROW BEGIN
        SET NEW.Ordered_price = Item.Unit_price * NEW.Quantity_ordered
            WHERE NEW.Item_id = Item.Item_i
END;

但似乎并没有起作用。

- akhiljain

---

是的，触发器可以做到这一点。您需要在INSERT和（如果允许）UPDATE上计算一个值。 - HABO

@HABO：我根据链接尝试了以下内容： CREATE TRIGGER Order_price BEFORE INSERT ON Order_contains_items FOR EACH ROW BEGIN SET NEW.Ordered_price = Item.Unit_price * NEW.Quantity_ordered WHERE NEW.Item_id = Item.Item_i END; 但似乎没有起作用。 - akhiljain

你已经选定了 mysql-server-2005，还是这两个表在不同的数据库中？ - HABO

你们实际使用的是哪个关系型数据库管理系统，SQL Server 还是 MySQL？ - peterm

Answer 1

这是如何在MySQL中完成的（您的问题标记有mysql和sql-server）

CREATE TRIGGER tg_bi_order_contains_items 
BEFORE INSERT ON Order_contains_items
FOR EACH ROW
  SET NEW.Ordered_price = 
  (
    SELECT Unit_price * NEW.Quantity_ordered
      FROM Item
     WHERE Item_id = NEW.Item_id
     LIMIT 1
  );

CREATE TRIGGER tg_bu_order_contains_items 
BEFORE UPDATE ON Order_contains_items
FOR EACH ROW
  SET NEW.Ordered_price = 
  (
    SELECT Unit_price * NEW.Quantity_ordered
      FROM Item
     WHERE Item_id = NEW.Item_id
     LIMIT 1
  );

这里有一个关于 SQLFiddle 的演示

- peterm