I have an ammo table, and when data is added to this table, the rows where the price field is greater than 1000 should decrease by 50. But this does not happen:

 delimiter & create trigger LowPriceToAmmo after insert on ammo for each row begin declare i int; declare countI int; declare countAmmo int; select count(ammo.price) into countI from ammo; set @i:= 1; while @i > countI do select ammo.price into countAmmo from ammo where id = @i; if(countAmmo > 1000) then update ammo set price = price - 50 where id = @i; end if; set @i:= @i +1; end while; end&
  • Once again: after adding a new record to the ammo table, it is necessary that the ALL records with price>=1000 in the table are reduced by 50? Do I understand correctly? - cyadvert
  • @cyadvert yes. You understood everything correctly - pride
  • Then why do you need such a complicated trigger? Why not just run normal UPDATE ? UPDATE ammo SET price=price-50 WHERE price>=1000 ? - cyadvert
  • @makintosh In mysql, the @i variable @i nothing to do with i , which is declared declare and tries to be used in while. variables with a dog and variables without a dog are made for completely different tasks. - Mike
  • @cyadvert I am going through training practice on mysql - pride

1 answer 1

You cannot update with the UPDATE in the trigger the same table to which the trigger is attached. It is better to perform such an update with a separate request.