声明变量MySQL触发器 [英] Declare variable MySQL trigger
问题描述
如果您习惯使用MySQL,那么我的问题对您来说可能很简单.我已经习惯了PostgreSQL SGBD,并且试图将PL/PgSQL脚本转换为MySQL.
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.
这就是我所拥有的:
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//
但是MySQL Workbench在DECLARE m_projects_id
上给我一个错误.我不太明白,因为我在上面两行有相同的说明...
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...
有任何提示吗?
Neubert解决了此错误.谢谢.
neubert solved this error. Thanks.
但是,当我尝试插入用户时:
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 ?
推荐答案
所有DECLARE必须位于顶部.即.
All DECLAREs need to be at the top. 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//
这篇关于声明变量MySQL触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!