从两个表触发乘法 [英] Trigger multiply from two tables

问题描述

我需要创建一个触发器，以将两个表中的两个字段相乘，但是我不知道该怎么做，所以让我们看看您是否可以帮助我.

i need to create a trigger that multiply two fields from two tables, but i have no idea of how to do it, so let's see if you can help me.

两个表

产品(产品名称，价格)

Products (product_name, price)

订单(产品名称(外键)，已订购单位)

Orders (product_name (foreign key),units_ordered)

我需要在表格上添加订单的另一个字段，这些订单的价格乘以产品的价格，并乘以订单的units_ordered，因此:total_price =价格(来自产品)X units_ordered(来自订单)

I need to add another field on table the Orders that multiply price from Products and units_ordered from Orders, so : total_price = price (from products) X units_ordered (from Orders)

在此先感谢您，我的英语不好.问候

Thanks in advance, and sorry for my bad english. Regards

推荐答案

您根本不需要触发器.而且，您也不需要为总价格添加另一列，因为它已经很多余了.

You don't need trigger at all. And also you don't need to add another column for the total price as it is already redundant.

如果您想要它们的总价格，只需在记录记录期间进行.例子

If you want their total prices, just do it during the projection of records. Example

SELECT  a.Product_Name,
        a.Price,
        b.units_ordered,
        a.Price * b.units_ordered AS TotalPrice 
FROM    Products a
        INNER JOIN Orders b
            ON a.Product_name = b.Product_name

，或者您可以在 SELECT 语句中创建一个 VIEW .例子，

or you can create a VIEW out of your SELECT statement. Example,

CREATE VIEW ProductOrder
AS
SELECT  a.Product_Name,
        a.Price,
        b.units_ordered,
        a.Price * b.units_ordered AS TotalPrice 
FROM    Products a
        INNER JOIN Orders b
            ON a.Product_name = b.Product_name

并从视图中选择

SELECT * FROM ProductOrder

但是，如果您确实要添加另一列，则仍然不能选择触发器.您只需要使用 UPDATE 并合并两个表来更新该列的值.假设您的新列在订单"表上称为 TotalPrice .

But if you really want to add another column, still trigger is not an option. You only need to update the values for that column using UPDATE and joining of the two tables. Assuming your new column is called TotalPrice on table `Orders.

UPDATE  Orders a
        INNER JOIN Products b
            ON a.Product_name = b.Product_name
SET     a.TotalPrice = a.units_ordered * b.Price

这篇关于从两个表触发乘法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！