声明变量 MySQL 触发器

2022-01-01 00:00:00 sql mysql stored-procedures triggers

如果您习惯了 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//

相关文章