Geek Answers Handbook

MySQL Trigger Insert After selecting a query from another table

new to DBA thanks to support with me.

Overview: I have groups, subgroups and users.

  • The user can be the owner of the group, therefore, must be the owner of all its subgroups.
  • A user can be a co-author or follower of a group, so he must be a co-author or follower of all his subgroups.
  • User can only be a co-author or follower of a subgroup

The tables are as follows (simplified):

Group (topic_id, name)

Subgroup (subtopic_id, title, topic_id)

rel_Group (user_id, topic_id, type) // Determine the relationship of the user to the group (owner, collaborator or follower)

rel_Subgroup (user_id, subtopic_id, type) // Determine the relationship of the user to the subgroup (Owner, Collaborator or Follower)

User (user_id)

I want to create a trigger when a subgroup is created that will insert INSERT / UPDATE / DELETE strings in rel_Subgroup so that users who are the Owner, Collaborator or follower in the group, respectively, are the Owner, Collaborator or follower in the subgroup

This is the closest I received, but I still get: # 1415 - It is 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 hope to sort the insert and then find out the update / deletion.

Any help greatly appreciated!

THX

+4
source share
2 answers

I managed to solve the problem:

 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 ;
+11
source

Try the following:

 delimiter // create trigger Transfer_Rights_to_Subgroup after insert on Subgroup for each row begin select user_id,type into @userid, @type from group where rel_Group.topic_id = NEW.topic_id; insert into rel_Subgroup VALUES (@userid,NEW.subtopic_id,@type); END; // delimiter ;
0
source

More articles:


All Articles