how to declare variable in trigger and use it with mysql ??

Question

what is the error ? DELIMITER $$

CREATE TRIGGER Task_insert_trig AFTER INSERT ON task FOR EACH ROW begin declare userID int;

Set userID =(select userID from assigned_task where Atk_Task_Id = new.Tsk_Id and Atk_Project_Id = new.Tsk_Project_Id); insert into dashboard_event set Dsh_Project_Id = new.Tsk_Project_Id, Dsh_Actor = userID, Dsh_Action = 'Assign', Dsh_Type = 'Task', Dsh_Target = new.Tsk_Id, Dsh_Date = now(); $$ end DELIMITER ;

Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 12

Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'end DELIMITER' at line 1

Answer 1

I believe the error was related to the $$ delimiter, $$ end delimiter; is not correct. Also, I wouldn't use variable names that might be confused to a table column (such as userID in assigned_task table. Also the insert syntax is broken. (UPDATE: actually the insert was just fine, I didn't know you could do it that way).

Try

DELIMITER $$
CREATE TRIGGER Task_insert_trig AFTER INSERT ON task FOR EACH ROW 
begin 

Set @userID =(select userID from assigned_task where Atk_Task_Id = new.Tsk_Id and Atk_Project_Id = new.Tsk_Project_Id limit 1); 
insert into dashboard_event 
(Dsh_Project_Id , Dsh_Actor , Dsh_Action ,  Dsh_Type , Dsh_Target , Dsh_Date )
values 
(new.Tsk_Project_Id, @userID,  'Assign',  'Task', new.Tsk_Id,  now());

end $$ 
DELIMITER ;