title: MySql Error: Can't update table in stored function/trigger
body: <p>I am using MySQL and the Engine InnoDB. I have a SQL table (simplified) containing 4 columns as you can see in this picture :</p>
  <p><img src="https://i.stack.imgur.com/UR2dy.png" alt="enter image description here" /></p>
  <p>When the state of a demand will become &quot;Done&quot; I want its Priority to be null and all demands with above priorities decremented.</p>
  <p>For example, if  the second demand : &quot;Bread&quot; is &quot;Done&quot;, its priority will be set to null and I want &quot;Butter&quot; to have a priority of 2, and &quot;Jam&quot;  a priority of 3.</p>
  <p>I have this trigger :</p>
  <pre><code>DELIMITER |
  CREATE TRIGGER modify_priority_trigger BEFORE UPDATE ON your_table
  FOR EACH ROW 
  begin
      if NEW.State= 'Done'
      then
         update your_table
         set priority = priority - 1
         where priority is not null
         and priority &gt; NEW.priority;
      
         set NEW.priority = NULL;
      end if;
  end
  |
  delimiter ;
  </code></pre>
  <p>But I have an error when I edit a line from a state from &quot;In Progress&quot; to &quot;Done&quot;.</p>
  <blockquote>
  <p><code>#1442</code> - Can't update table 'demand' in stored function/trigger because it is already used by statement which invoked this stored
  function/trigger.</p>
  </blockquote>
  <p>I have looked for this error on the Internet but it's the first time I use SQL trigger so I haven't succeed to correct my problem.
  Thank's for your help.</p>