11

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 ;
Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Russ
  • 115
  • 1
  • 2
  • 9

2 Answers2

42

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

Cory House
  • 14,235
  • 13
  • 70
  • 87
0

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.

goonerify
  • 1,668
  • 25
  • 27