Trigger for multiplying two columns from different tables with a functions in mysql

Question

I have been endelessly trying to get a trigger to work, with many different variations and all ending in errors.

I have finally just decided to ask for help, since im still a beginner at this.

So say I have a many-to-many table that contains pk's of o.orderId and p.productId, and within this table I have two columns called amount and totalPrice

I have then created a function, that returns the price of products based on their productiD.

CREATE FUNCTION func_getProductPrice($pId varchar(45)) RETURNS int(11)
    DETERMINISTIC
BEGIN
DECLARE origPrice int(11);
SET origPrice = (select o.productPrice from (productId_has_orderId
inner join productId on productId = $pId);
RETURN origPrice;
END

And a before insert trigger that is meant to call on this function to get the price, multiply the returned value based on new entry and then update the price field of this new entry.

delimiter |
CREATE TRIGGER `vet`.`calcProdutctPrice` before INSERT ON `productId_has_orderId` 
FOR EACH ROW 
IF NEW.amount <> 1 THEN
BEGIN
DECLARE newPrice int(11);
set newPrice = ((SELECT SUM((new.amount) * (select func_getProductprice(new.p.productId))) AS total from productId_has_orderId));
    begin
        update productId_has_orderId
        set price = newPrice
        where orderId = new.orderId;
        end;
    END;
end if
|
delimiter ;

Which gives this error when inserting data

Cant't update table 'productId_has_orderId' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

An another variation of the trigger which essentially works the same, but the price is not a declared variable and is instead just updated to the new price

delimiter |
CREATE TRIGGER `vet`.`calcProdutctPrice` before INSERT ON `productId_has_orderId` 
FOR EACH ROW 
IF NEW.amount <> 1 THEN
BEGIN 
        update productId_has_orderId
        set price = ((SELECT SUM((new.amount) * (select func_getProductprice(new.p.productId))) AS total from productId_has_orderId));
        where orderId = new.orderId;
    END;
end if
|
delimiter ;

Which then instead gives this error when inserting values

insert into productId_has_orderId (orderId, productId, amount) values (5100002, 3100002, 3)

Error Code: 1093. You can't specify target table 'productId_has_orderId' for update in FROM clause 0.000 sec

I am not sure what else I can do by now, I was thinking about creating a trigger calling a stored procedure, but I don't know if I can pass values from a trigger to a procedure.

Any help is appreciated, thank you

Answer 1

Assume we have (schematically):

1. Products table

   CREATE TABLE products (productID INT, 
                          price DECIMAL);
   

2. Orders table

   CREATE TABLE orders (orderID INT, 
                        total_price DECIMAL);
   

3. 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.