Trigger for multiplying two columns from different tables with a functions in mysql
Assume we have (schematically):


Products table

CREATE TABLE products (productID INT, 
                       price DECIMAL);

Orders table

CREATE TABLE orders (orderID INT, 
                     total_price DECIMAL);

Joining table

CREATE TABLE joining (productID INT, 
                      orderID INT, 
                      amount DECIMAL, 
                      separate_price DECIMAL); 



There is a lot of records in products table. We form new order and add new record into orders table. Now we begin to insert records into joining table, and we want to calculate both separate_price (for each separate product) and total (for whole order).



The trigger for to calculate separate price will be:

CREATE TRIGGER calculate_separate
BEFORE INSERT
ON joining 
FOR EACH ROW
SET NEW.separate_price := (SELECT NEW.amount * products.price
                           FROM products
                           WHERE products.productID = NEW.productID);




The trigger for to calculate total price will be:

CREATE TRIGGER calculate_separate
AFTER INSERT
ON joining 
FOR EACH ROW
UPDATE orders 
    SET total_price = total_price + NEW.separate_price
    WHERE orderID = NEW.orderID;


If there is no separate_price field in joining table (and there is no trigger which calculates the value for that field) we will use

CREATE TRIGGER calculate_separate
AFTER INSERT
ON joining 
FOR EACH ROW
UPDATE orders 
    SET total_price = total_price + (SELECT NEW.amount * products.price
                                     FROM products
                                     WHERE products.productID = NEW.productID)
    WHERE orderID = NEW.orderID;




Pay attention: we calculate separate value (which is stored in the same table, in the record which creation fires a trigger) in BEFORE trigger (so the calculated value then will be inserted), whereas we re-calculate total value in AFTER trigger (and update a record in another table, and only in case when new record successfully inserted).

PS. Function mentioned in a question is not used - we do not need in it. We can use it, of course, but its usage is excess.

PPS. Each trigger above contains one single statement. So we do not need in BEGIN..END blocks ade DELIMITER reassign.