Declare variable MySQL trigger

Question

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.

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...

Any hints ?

EDIT: 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 ?

Answer 1

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//