title: How to UPDATE from within an INSERT trigger in MySQL? What other approach can I take?
body: <p>I've the following trigger which is really bugging me.</p>
  
  <p>I want to do an UPDATE inside the trigger, but MySQL doesn't allow it, since the statement "triggering" the trigger refer to the participants table.</p>
  
  <p>What can I do in order to make it work? I've considered stored procedures, but they are also forbidden in this case.</p>
  
  <p><strong>The logic is</strong>: I've a table with participants in a meeting. The invariant that people from the 'tap' group should always accept the invitation if someone from the 'vip' group is attending. In this case, if a 'vip' is attending the meeting I must <strong>UPDATE</strong> all 'tap' attendances, but how is this possible???</p>
  
  <pre><code>DELIMITER $$
  
  CREATE TRIGGER tap_meet_vip
  BEFORE INSERT ON participants
  FOR EACH ROW BEGIN
      IF (NEW.pid IN (SELECT userid FROM people WHERE `group` = 'vip')) THEN  # new participant is a member of the vip group
          UPDATE participants SET `status` = 'a' WHERE pid IN (SELECT userid FROM people WHERE `group` = 'tap') AND meetid = NEW.meetid ; # update status to `accept` of all tap group participants   
      ELSEIF (NEW.pid IN (SELECT userid FROM people WHERE `group` = 'tap') AND EXISTS (SELECT * FROM participants INNER JOIN people ON pid = userid WHERE `group` = 'vip')) THEN # new participant is a member of the tap group and meeting has vip participant
          SET NEW.`status` = 'a';
      END IF;
  END;$$
  </code></pre>
  
  <p>The above is written using MySQL and the version is 5.6.</p>