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

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

提问by mohamed

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

错误代码：1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以了解在第 12 行的 '' 附近使用的正确语法

错误代码：1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的“end DELIMITER”附近使用的正确语法

回答by PeteGO

The ENDneeds to be before the $$.

该END需求是前$$。

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

这个工程在MySQL 5.5.28这个小提琴通过设置分隔符|。

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 - Taskinstead of Tsketc. Gets really bad when you see acronyms everywhere too when the full words would be much clearer. Rant over.

我个人也更喜欢事物的全名——任务而不是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

回答by ceteras

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).

我相信错误与 $$ 分隔符有关，$$ end delimiter;不正确。另外，我不会使用可能与表列混淆的变量名（例如assigned_task 表中的用户ID。插入语法也被破坏了。（更新：实际上插入很好，我不知道你能做到）就这样）。

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 ;

回答by LucaE

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.

“当过程中有多个语句时，您需要一个 BEGIN/END 块。您可以使用该块将多个语句括起来。

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."

但这还不是全部。BEGIN/END 块，也称为复合语句，是您可以定义变量和控制流的地方。”

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;.

首先，请注意诸如 IF ... END IF 或 WHILE ... END WHILE 之类的关键字流控制组被视为单个语句，就其以分号终止而言，也就是说，它以分号终止在它的末尾加上一个分号：IF ... END IF; 当......结束时;.

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.

然后，如果触发器或存储过程的主体仅包含一个语句，并且该语句不是变量声明，也不是上述关键字的流控制组，则该语句不能以分号 (;) 结束，也不能用分号括起来一个 BEGIN ... END 块。

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.

相反，如果触发器或存储过程的主体包含多个语句，特别是如果它包含变量声明和/或关键字流控制组，则它必须包含在 BEGIN ... END 块中。

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

最后，BEGIN ... END 块本身不能以分号结尾。

Hope this helps

希望这可以帮助