1

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?

1
  • 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, 2013 at 13:38

1 Answer 1

3

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
begin
    if new.C1=0 then
        set new.c2 = new.c2 +1;
    end if;
end $$
delimiter ;
4
  • 1
    I added the delimiter clauses around the code. You also forget the set keyword before the assignment. +1 otherwise. Aug 2, 2013 at 14:51
  • why cant we use after update ? what does the error mean ? is it mutating table error ?? Aug 5, 2013 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? Aug 5, 2013 at 8:01
  • not of all rows , i just need to increment C2 column for the row whose C1 becomes 0 everytime.. Aug 5, 2013 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.