Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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
add comment

1 Answer

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 ;
share|improve this answer
1  
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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