MySQL Trigger Insert After with Select query from different table

Question

new to DBA thanks for bearing with me.

Overview: I have Groups, Subgroups and Users.

1. User can be owner of Group so should be Owner of all its subgroups
2. User can be collaborator or follower of group so should be collaborator or follower of all its subgroups
3. 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

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 ;

Comments

[Hardane]

I think you meant: select user_id,type into @userid, @type from rel_Group instead of group right? Tried this and am still getting a problem since this select returns more than one result:_"#1172 - Result consisted of more than one row"_