CODEWITHSUNDEEP
mysql
mohamed
mohamed

Reputation: 11

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

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

Upvotes: 1

Views: 34079

Answers (3)

PeteGO
PeteGO

Reputation: 5781

The END needs to be before the $$.

This works in MySql 5.5.28 on this fiddle by setting the delimiter to |.

As a side point, I would really recommend tidying up your code for readability purposes - it's not a major issue but you've got some keywords in caps, some not, some things wrapped in ``, some not, and no indenting.

I personally prefer full names of things too - Task instead of Tsk etc. Gets really bad when you see acronyms everywhere too when the full words would be much clearer. Rant over.

CREATE TABLE assigned_task (
  Atk_Task_Id INT NOT NULL,
  Tsk_Project_Id INT NOT NULL);

CREATE TABLE dashboard_event (
  Dsh_Project_Id INT NOT NULL,
  Dsh_Actor INT NOT NULL,
  Dsh_Action CHAR(100) NOT NULL,
  Dsh_Type CHAR(100) NOT NULL,
  Dsh_Target INT NOT NULL,
  Dsh_Date DATETIME);

CREATE TABLE Task (
  Tsk_Id INT NOT NULL,
  Tsk_Project_Id INT NOT NULL);

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

Upvotes: 3

LucaE
LucaE

Reputation: 21

Here's all my findings on the subject:

This is a quote from a manual:

"You need a BEGIN/END block when you have more than one statement in the procedure. You use the block to enclose multiple statements.

But that's not all. The BEGIN/END block, also called a compound statement, is the place where you can define variables and flow of control."

In other words:

(These rules appear to apply to triggers and stored procedures in the same way, as it seems the same syntax is used in both.)

First, notice that a flow control group of keywords such as IF ... END IF or WHILE ... END WHILE is seen as a single statement as far as its termination with a semicolon is concerned, that is, it is terminated as a whole by a single semicolon at the end of it: IF ... END IF; WHILE ... END WHILE;.

Then, if the body of a trigger or stored procedure contains just one stament, and that statement is not a variable declaration nor a flow control group of keywords as above, that statement may not be terminated by a semicolon (;) and not enclosed by a BEGIN ... END block.

On the contrary, if the body of a trigger or stored procedure contains more than one stament, and particularly if it contains variable declarations and/or flow control groups of keywords, then it must be enclosed in a BEGIN ... END block.

Finally, the BEGIN ... END block itself must not be terminated by a semicolon.

Hope this helps

Upvotes: 1

ceteras
ceteras

Reputation: 3378

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 ;

Upvotes: 2

Related Questions