title: MySQL Trigger Insert After with Select query from different table
body: <p>new to DBA thanks for bearing with me.</p>
  
  <p><strong>Overview:</strong>
  I have Groups, Subgroups and Users.</p>
  
  <ol>
  <li>User can be owner of Group so should be Owner of all its subgroups</li>
  <li>User can be collaborator or follower of group so should be collaborator or follower of all its subgroups</li>
  <li>User can be collaborator or follower of just subgroup</li>
  </ol>
  
  <p><strong>Tables are as follow (simplified):</strong></p>
  
  <p><strong>Group</strong>(topic_id,title)</p>
  
  <p><strong>Subgroup</strong> (subtopic_id,title,topic_id)</p>
  
  <p><strong>rel_Group</strong> (user_id,topic_id,type)
  //To Determine relationship of user to Group (Owner,Collaborator or Follower)</p>
  
  <p><strong>rel_Subgroup</strong> (user_id,subtopic_id,type)
   //To Determine relationship of user to Subgroup (Owner,Collaborator or Follower)</p>
  
  <p><strong>User</strong> (user_id)</p>
  
  <p>I want to create a trigger when a subgroup is created that will INSERT / UPDATE / DELETE rows in rel_Subgroup so users who are Owner, Collaborator or follower on group with respectively be Owner, Collaborator or follower on subgroup</p>
  
  <p>This is the closest i got but am still getting: 
  <strong>#1415 - Not allowed to return a result set from a trigger.</strong> </p>
  
  <p><strong>SQL Query</strong></p>
  
  <pre><code>delimiter //
  create trigger Transfer_Rights_to_Subgroup
  after insert 
  on Subgroup
  for each row
  begin
  select user_id,type from rel_Group where rel_Group.topic_id = NEW.topic_id;
  insert into rel_Subgroup VALUES (rel_Group.user_id,NEW.subtopic_id,rel_Group.type); 
  END; //
  delimiter ;
  </code></pre>
  
  <p>I am hoping to sort the insert and then will figure out the update/delete. </p>
  
  <p>Any help, much appreciated!</p>
  
  <p>thx</p>