MySQL Trigger Insert After with Select query from different table

insert, mysql, triggers

Solution

Managed to solve it:

DROP TRIGGER IF EXISTS Transfer_Rights_to_Subgroup;
DELIMITER //
CREATE TRIGGER Transfer_Rights_to_Subgroup AFTER INSERT ON subgroup
FOR EACH ROW
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE c1 INT;
    DECLARE c2 INT;
    DECLARE cur CURSOR FOR SELECT User_ID,Type FROM rel_group WHERE rel_group.Topic_ID =     NEW.Topic_ID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
        ins_loop: LOOP
            FETCH cur INTO c1,c2;
            IF done THEN
                LEAVE ins_loop;
            END IF;
            INSERT INTO rel_Subgroup VALUES (c1,NEW.Subtopic_ID,c2);
        END LOOP;
    CLOSE cur;
END; //
DELIMITER ;

Problem

new to DBA thanks for bearing with me. Overview: I have Groups, Subgroups and Users. - User can be owner of Group so should be Owner of all its subgroups - User can be collaborator or follower of group so should be collaborator or follower of all its subgroups - User can be collaborator or follower of just subgroup Tables are as follow (simplified): Group(topic_id,title) Subgroup (subtopic_id,title,topic_id) rel_Group (user_id,topic_id,type) //To Determine relationship of user to Group (Owner,Collaborator or Follower) rel_Subgroup (user_id,subtopic_id,type) //To Determine relationship of user to Subgroup (Owner,Collaborator or Follower) User (user_id) 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 This is the closest i got but am still getting: #1415 - Not allowed to return a result set from a trigger. SQL Query ``` 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 ; ``` I am hoping to sort the insert and then will figure out the update/delete. Any help, much appreciated! thx

Original source