如何在触发器中声明变量并将其与mysql一起使用? [英] how to declare variable in trigger and use it with mysql?

问题描述

错误是什么?

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 ;

错误代码:1064 您的SQL语法有误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第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 '' at line 12

错误代码:1064 您的SQL语法有误；查看与您的MySQL服务器版本相对应的手册以获取正确的语法，以在'end末尾使用 第1行的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 'end DELIMITER' at line 1

推荐答案

END必须在$$之前.

在MySql 5.5.28中，此小提琴可以正常工作，方法是将分隔符设置为|

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.

我个人也更喜欢事物的全名-任务，而不是 Tsk 等.当您在各处都看到缩写词时，如果单词的全貌会更清晰的话，这会变得非常糟糕.狂奔.

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

这篇关于如何在触发器中声明变量并将其与mysql一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！