MySQL Trigger Insert After with Select 来自不同表的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14952180/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Trigger Insert After with Select query from different table
提问by Hardane
new to DBA thanks for bearing with me.
DBA 新手感谢您的支持。
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)
组(topic_id,title)
Subgroup(subtopic_id,title,topic_id)
子组(subtopic_id,title,topic_id)
rel_Group(user_id,topic_id,type) //To Determine relationship of user to Group (Owner,Collaborator or Follower)
rel_Group(user_id,topic_id,type) //确定用户与组的关系（Owner,Collaborator or Follower）
rel_Subgroup(user_id,subtopic_id,type) //To Determine relationship of user to Subgroup (Owner,Collaborator or Follower)
rel_Subgroup(user_id,subtopic_id,type) //确定用户与子组（Owner,Collaborator or Follower）的关系
User(user_id)
用户(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
我想在创建子组时创建一个触发器，该触发器将在 rel_Subgroup 中插入/更新/删除行，因此组上的所有者、协作者或关注者的用户分别是子组的所有者、协作者或关注者
This is the closest i got but am still getting: #1415 - Not allowed to return a result set from a trigger.
这是我得到但仍然得到的最接近的结果： #1415 - 不允许从触发器返回结果集。
SQL Query
SQL查询
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
谢谢
回答by Hardane
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 ;
回答by user2001117
Try this one:
试试这个：
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 ;
