Hi, I want to set up a trigger so that if on an update the prediction field is = 3 then the trigger changes the value to 4 and saves it in the database. The trigger is below.
For some reason I keep getting an error saying: "#1442 - Can't update table 'tzanalytic_forecast_cached' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."
Is this set up the right way?
delimiter $$
CREATE TRIGGER no_BoW BEFORE UPDATE ON t FOR EACH ROW
BEGIN set @prediction = new.prediction;
UPDATE t SET t.prediction = (SELECT IF(@prediction = '3', '4', @prediction)) WHERE t.event_id = new.event_id AND t.price_tier = new.price_tier; END;
$$ delimiter ;