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 ;
MySQL triggers can't manipulate the table they are assigned to. All other major DBMS support this feature so hopefully MySQL will add this support soon.
http://forums.mysql.com/read.php?99,122354,240978#msg-240978
Also, you need to ensure that you don't have other procedures or functions that perform updates on the table that this particular procedure is assigned to or you end up in a recursion. For instance
create trigger trig1 After update on table1 FOR EACH ROW
BEGIN
UPDATE table2 SET colum1 = column1 + 1
END;
create trigger trig2 After update on table2 FOR EACH ROW
BEGIN
UPDATE table1 SET colum2 = column2 + 1
END;
This will end up in a recursion so beware of the existing stored procedures and functions.
