Menu
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 ?
806
This is done by placing the caret (^) symbol in front of the variable name when specifying it. In this example, both Var1 and Var2 will be exported to the parent object when the trigger has completed. They can also be used by triggers at their own level, just as in previous examples.
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
Here, we will learn about user defined variable. We can set some value to the variable with the help of SET command. SET @yourVariableName=value; Note − In the SELECT statement, the “yourVariableName” contains the NULL value and after using the SET command it contains the value which we have given.
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// If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
