Multiply two column from two different table and set them into another table using triggers

Question

I have 3 Mysql tables and I want to make one of the fields generated from multiplying two fields from two different tables. These are my tables:

ITEMS

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

DETAIL TRANSACTIONS

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

TRANSACTIONS

id_trans |  total_price
1        |      100

A total price field inside TRANSACTIONS is what I wanted, and I have tried making a trigger like:

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`;

But the result is not what I wanted. Any help will be appreciated!

Answer 1

Key words are FOR EACH ROW - ie update 1 row at a time..And do not assume transactions exists test and create if need be

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 $$