title: How to update rows in table in after delete trigger
body: <p>I have a table country_group</p>
  
  <pre><code>+------+----------+--------+
  | code | group_id | weight |
  +------+----------+--------+
  | me   | 1        | 50.00  |
  +------+----------+--------+
  | me   | 2        | 10.00  |
  +------+----------+--------+
  | me   | 3        | 40.00  |
  +------+----------+--------+
  </code></pre>
  
  <p>To me, it is very important that for each country total sum of column weight is 100. What I want do here is when I delete one of rows I need to change  weight values for that country so that sum of all weights for that country is still 100. I want to have trigger in my database for this.</p>
  
  <p>For example if I delete row where weight is 10.00 then table should look like this:</p>
  
  <pre><code>+------+----------+--------+
  | code | group_id | weight |
  +------+----------+--------+
  | me   | 1        | 55.00  |
  +------+----------+--------+
  | me   | 3        | 45.00  |
  +------+----------+--------+
  </code></pre>
  
  <p>So this is my trigger:</p>
  
  <pre><code>CREATE DEFINER = CURRENT_USER TRIGGER   extensionui2.country_group_AFTER_DELETE 
  AFTER DELETE ON country_group 
  FOR EACH ROW
  BEGIN
      DECLARE w FLOAT(5,2);
      DECLARE c INT;
      DECLARE s FLOAT(5,2);
  
      SET w = OLD.weight;
      SELECT count(*) into c FROM country_group WHERE code = OLD.code;
  
      SET s = w / c;
      UPDATE country_group SET weight = weight + s WHERE code = OLD.code;
  END;
  </code></pre>
  
  <p>But I get this error:</p>
  
  <pre><code>Operation failed: There was an error while applying the SQL script to the database.
  Executing:
  DELETE FROM `extensionui2`.`country_group` WHERE `code`='ms' and`group_id`='9';
  
  ERROR 1442: 1442: Can't update table 'country_group' in stored  function/trigger because it is already used by statement which invoked this stored function/trigger.
  SQL Statement:
  DELETE FROM `extensionui2`.`country_group` WHERE `code`='ms' and`group_id`='9'
  </code></pre>
  
  <p>What do I need to change in my trigger code to make this work?</p>