从两个不同的表乘两列，并使用触发器将它们设置为另一个表。

Question

我有3个Mysql表，我想将两个不同表中的两个字段相乘生成一个字段。这是我的桌子：

ITEMS

id_item |  price
1       |   20
2       |   30
3       |   50

详细事务

id_trans(fk) | id_item | total_items
1            |    1    |     1
1            |    2    |     1
1            |    3    |     1

TRANSACTIONS

id_trans |  total_price
1        |      100

在TRANSACTIONS中有一个total price字段是我想要的，我试着做一个触发器，如下所示：

CREATE TRIGGER total_price
AFTER INSERT ON detail_transactions
FOR EACH ROW
UPDATE transactions
   SET transactions.`total_price`= 
    (SELECT SUM(items.'price'*detail_transactions.'total_items') 
       FROM items
       JOIN detail_transactions
      ON items.'id_item'= detail_transactions.`id_item`)
 WHERE transactions.`id_trans` = NEW.`id_trans`;

但结果不是我想要的。任何帮助都将不胜感激！

Answer 1

关键字为每一行(在time..And上更新1行)，不要假设事务存在、测试和创建(如果需要)。

drop trigger if exists t;
delimiter $$
create trigger t after insert on detail_transactions
for each row begin
    if not exists (select 1 from transactions t where t.id_trans = new.id_trans) then
        insert into transactions 
            select new.id_trans,new.total_items * price
            from items 
            where items.id_item = new.id_item ;
    else
        update transactions join items on items.id_item = new.id_item
            set total_price = total_price + (new.total_items * price);
    end if;
            
end $$

Answer 2

CREATE TRIGGER tr_ai_update_total_price
AFTER INSERT 
ON detail_transactions
FOR EACH ROW
REPLACE INTO transactions (id_trans, total_price)
    SELECT NEW.id_trans, SUM(items.price * detail_transactions.total_items) 
    FROM items
    JOIN detail_transactions USING (id_item)
    WHERE transactions.id_trans = NEW.id_trans;

这个查询假设transactions (id_trans)被定义为UNIQUE (可能是PRIMARY KEY)。

如果这个id_trans的行已经存在，它将被替换为新值。如果它不存在，则将插入它。

触发器创建语句包含1条语句，因此不需要开始-结束或分隔符.