Take the 2-minute tour ×

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
  if new.C1=0 then
    update T1 set c2 = c2 +1;
  end if;

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?

share|improve this question
Please don't use the Oracle tag for MySQL questions, it's for Oracle database. (If I got this wrong and your question is indeed about Oracle database, please edit your question to remove the MySQL tag and put the Oracle tag back in.) –  Mat Aug 2 '13 at 13:38

1 Answer 1

You can also change values using new. You just have to use not after but before trigger.

delimiter $$
create trigger increment
before update on T1
    if new.C1=0 then
        set new.c2 = new.c2 +1;
    end if;
end $$
delimiter ;
share|improve this answer
I added the delimiter clauses around the code. You also forget the set keyword before the assignment. +1 otherwise. –  RolandoMySQLDBA Aug 2 '13 at 14:51
why cant we use after update ? what does the error mean ? is it mutating table error ?? –  user21546 Aug 5 '13 at 6:55
The idea of trigger is not to do an additional update of the table but to adjust the values so that we do additional things with one UPDATE. Actually my trigger differs a bit from yours. Do you really need to increment C2 column of all rows when C1 of one row becomes 0? –  Mindaugas Riauba Aug 5 '13 at 8:01
not of all rows , i just need to increment C2 column for the row whose C1 becomes 0 everytime.. –  user21546 Aug 5 '13 at 8:50

Your Answer


