I have a table with two columns:
C1 | C2
1 | 3
Whenever C1
gets updated to 0, C2
should be incremented by 1.
I have developed a trigger:
create trigger increment
after update on T1
begin
if new.C1=0 then
update T1 set c2 = c2 +1;
end if;
end;
But this raises an error whenever I try to update column C1:
ERROR 1442 (HY000): Can't update table 'T1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
I have only one update trigger on this table. How can I make this work?