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