Not allowed to return a result set from a trigger for the query I ran-CodePudding

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?

CodePudding user response：

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 ;