I want to create a trigger which updates my ‘ratio’ column automatically within the same table. I need to sort by date(month and year) for the specific items get the sum for the items at that date then divide by the item values.
This is the query I tried:
create trigger ratio before update ON Table1 for each row (select Month,Year,sum(item_value) as sum_item from Table1 where item_name like ('BUC%') group by Month,Year) x on Table1.Month = x.Month and Table1.Year = x.Year and Table1.item_name like ('BUC%') set Table1.Ratio = Table1.item_value/x.sum_item;
but I get the error
Error Code: 1415. Not allowed to return a result set from a trigger which I kind of understand. I’m very new to mysql by the way.
If I can’t do what I want this way is there another way or method to get it done?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Use a subquery to get the total value for the same product and month, and use that to calculate the ratio for the updated row.
DELIMITER $$ create trigger ratio before update ON Table1 for each row IF item_name LIKE 'BUC%' THEN SET NEW.ratio = NEW.item_value/( SELECT SUM(item_value) FROM Table1 WHERE item_name = NEW.item_name AND month = NEW.month AND year = NEW.year ); END IF$$ DELIMITER ;