mysql触发器中使用变量,如何在触发器中声明变量并将其与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

解决方案

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