声明变量 MySQL 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15841455/
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
Declare variable MySQL trigger
提问by Arthur
My question might be simple for you, if you're used to MySQL. I'm used to PostgreSQL SGBD and I'm trying to translate a PL/PgSQL script to MySQL.
如果您习惯 MySQL，我的问题对您来说可能很简单。我习惯了 PostgreSQL SGBD，我正在尝试将 PL/PgSQL 脚本转换为 MySQL。
Here is what I have :
这是我所拥有的：
delimiter //
CREATE TRIGGER pgl_new_user
AFTER INSERT ON users FOR EACH ROW
BEGIN
DECLARE m_user_team_id integer;
SELECT id INTO m_user_team_id FROM user_teams WHERE name = "pgl_reporters";
DECLARE m_projects_id integer;
DECLARE cur CURSOR FOR SELECT project_id FROM user_team_project_relationships WHERE user_team_id = m_user_team_id;
OPEN cur;
ins_loop: LOOP
FETCH cur INTO m_projects_id;
IF done THEN
LEAVE ins_loop;
END IF;
INSERT INTO users_projects (user_id, project_id, created_at, updated_at, project_access)
VALUES (NEW.id, m_projects_id, now(), now(), 20);
END LOOP;
CLOSE cur;
END//
But MySQL Workbench gives me an error on DECLARE m_projects_id. I don't really understand because I've the same instruction two lines above...
但是 MySQL Workbench 在DECLARE m_projects_id. 我真的不明白，因为我在上面两行有相同的说明......
Any hints ?
任何提示？
EDIT: neubert solved this error. Thanks.
编辑：neubert 解决了这个错误。谢谢。
But yet, when I try to insert into users :
但是，当我尝试插入用户时：
Error Code: 1329. No data - zero rows fetched, selected, or processed
Do you have any idea ? Or better, do you know how I can get a better error message ?
你有什么主意吗 ？或者更好，你知道我怎样才能得到更好的错误信息吗？
回答by neubert
All DECLAREs need to be at the top. ie.
所有 DECLARE 都需要位于顶部。IE。
delimiter //
CREATE TRIGGER pgl_new_user
AFTER INSERT ON users FOR EACH ROW
BEGIN
DECLARE m_user_team_id integer;
DECLARE m_projects_id integer;
DECLARE cur CURSOR FOR SELECT project_id FROM user_team_project_relationships WHERE user_team_id = m_user_team_id;
SET @m_user_team_id := (SELECT id FROM user_teams WHERE name = "pgl_reporters");
OPEN cur;
ins_loop: LOOP
FETCH cur INTO m_projects_id;
IF done THEN
LEAVE ins_loop;
END IF;
INSERT INTO users_projects (user_id, project_id, created_at, updated_at, project_access)
VALUES (NEW.id, m_projects_id, now(), now(), 20);
END LOOP;
CLOSE cur;
END//
回答by amk
Agree with neubert about the DECLARE statements, this will fix syntax error. But I would suggest you to avoid using openning cursors, they may be slow.
同意 neubert 关于 DECLARE 语句，这将修复语法错误。但我建议您避免使用打开游标，它们可能会很慢。
For your task: use INSERT...SELECT statement which will help you to copy data from one table to another using only one query.
对于您的任务：使用 INSERT...SELECT 语句，它可以帮助您仅使用一个查询将数据从一个表复制到另一个表。
