mysql - 如何在触发器中声明变量并将其与mysql一起使用？

Question

错误是什么？

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

END 需要在 $$ 之前。

这在 fiddle 上的 MySql 5.5.28 中有效通过将分隔符设置为 |。

作为一个方面，我真的建议为了可读性目的整理你的代码 - 这不是一个主要问题，但你有一些大写的关键字，一些不是，一些东西包裹在 `` 中，有些不是，没有缩进。

我个人也更喜欢事物的全名 - Task 而不是 Tsk 等。当你看到到处都是首字母缩略词时会变得非常糟糕，而完整的单词会更清晰。吐槽完了。

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