MySQL：如何在触发器中使用分隔符？ [英] MySQL: How do I use delimiters in triggers?

问题描述

有人告诉我，我需要在触发器中使用分隔符。我正在查看MySQL手册页面，我在示例中找到它;但是，它不在通用文档中。

这是我正在修正的代码：

  CREATE TRIGGER成人在插入或更新之前table5.column5 
为每个ROW BEGIN 
更新table1 SET column5 = table5.column5 WHERE table5id = table1xtable5.table5id WHERE table1xtable5.table1id = OLD .table1.id $ 
 END $ 
  

老实说，我不知道该怎么做，以及 http://dev.mysql.com/上的文档doc / refman / 5.5 / en / create-trigger.html 似乎是一个非常不正确的参考。例如，当阅读旧和新时，它引用主题表 - 与触发器关联的表。有两个表与此触发器相关联，我实际上正在尝试将更多的表与此触发器相关联....当查看示例代码时， UPDATE 这会影响table4，只使用table4中的列（它们在值中加1）。另外，他们使用 WHERE〜a3 = NEW.a1 。我不认为id的工作是这样的。不应该有一个通用的ID可以跨表。在这个例子中，他们应该使用一个相互依赖关系的表格，以供适用性。

我今天早些时候提到了一个类似的问题，关于这样做的好方法，但是现在我想知道，只要有一个方法。谢谢阅读。 ：）

这是一个更新另一个表的触发器的示例，但似乎不使用分米：

  CREATE TRIGGER mytrigger BEFORE INSERT ON odp 
 FOR EACH ROW 
更新total_points SET points = points + NEW.points; 
  

在这个例子中没有解释的是如何 odp 和 total_points 重叠。我们如何确定 total_points 表的分数行是正确的分数行？ MySQL更新的点表中的哪一行？所以，不应该有一些方法来指定在__ code中更新的total_points.points行中的哪一行前面的INSERT ON odp

code>？

解决方案

第1部分

分隔符用于源对象，如存储过程/函数，触发器或事件。所有这些对象可能在BEGIN ... END子句中都有一个body代码。

MySQL脚本中的所有语句都应以分隔符结尾，默认为';'。但是如果源对象具有一些语句的主体，那么该怎么办？e：g：

  INSERT INTO table1 VALUES（1）; 
 
 CREATE PROCEDURE procedure1（）
 BEGIN 
 SET @s ='SELECT SQRT（POW（？，2）+ POW（？，2））AS斜边' 
 PREPARE stmt2 FROM @s; 
 SET @a = 6; 
 SET @b = 8; 
 EXECUTE stmt2 USING @a，@b; 
 END; 
 
 INSERT INTO table1 VALUES（2）; 
  

有多少州？ 3或8？答案是三，因为脚本有两个INSERT和一个CREATE PROCEDURE语句。如您所见，CREATE PROCEDURE也有一些内部声明;我们应该对MySQL客户端说所有这些语句（在BEGIN ... END内） - 是ONE语句的一部分;我们可以通过分隔符的帮助来实现：

  INSERT INTO table1 VALUES（1）; 
 
 DELIMITER $$ 
 
 CREATE PROCEDURE procedure1（）
 BEGIN 
 SET @s ='SELECT SQRT（POW（？，2）+ POW ？，2））AS斜边'; 
 PREPARE stmt2 FROM @s; 
 SET @a = 6; 
 SET @b = 8; 
 EXECUTE stmt2 USING @a，@b; 
 END $$ 
 
 DELIMITER; 
 
 INSERT INTO table1 VALUES（2）; 
  

注意，当您的触发器没有BEGIN ... END子句时，可能会省略分隔符。 p>

---

第2部分

没有分隔符该声明将被解析为 -

  CREATE PROCEDURE procedure1（）
 BEGIN 
 SET @s =' SELECT SQRT（POW（？，2）+ POW（？，2））AS斜边
  

而不是 -

  CREATE PROCEDURE procedure1（）
 BEGIN 
 SET @s ='SELECT SQRT（POW（？，2）+ POW（？，2））AS斜边' 
 PREPARE stmt2 FROM @s; 
 SET @a = 6; 
 SET @b = 8; 
 EXECUTE stmt2 USING @a，@b; 
 END 
  

Someone told me that I need to use a delimiter in my trigger. I'm looking at the MySQL manual page, and I'm finding it in the example; however, it's not in the generic documentation.

Here's the code I am trying to correct:

CREATE TRIGGER adult BEFORE INSERT OR UPDATE ON table5.column5
FOR EACH ROW BEGIN
  UPDATE table1 SET column5 = table5.column5 WHERE table5id = table1xtable5.table5id WHERE table1xtable5.table1id = OLD.table1.id$
END$

Honestly, I have no idea how to do this, and the documentation at http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html seems like a horribly inadequate reference. For example, when reading about "old" and "new", it references the "subject table"--the table associated with the trigger. There are two tables associated with this trigger, and I'm actually trying to associate more tables with this trigger.... When looking at the example code, the UPDATE line, which effects table4, only columns within table4 are used (they're adding 1 to the value). Also, they're using WHERE ~ a3 = NEW.a1. I don't think that id's work that way. There shouldn't be a universal ID that works across tables. They should definitely be using an interdependency table in the example, for applicability.

I actually asked a similar question earlier today about what a good method for doing this would be, but now I'm wondering, simply, if there is a method. Thanks for reading. :)

Here is an example of a trigger that updates another table, but it doesn't seem to use a delimeter:

CREATE TRIGGER mytrigger BEFORE INSERT ON odp 
FOR EACH ROW
  UPDATE total_points SET points = points + NEW.points;

What isn't explained in this example is how odp and total_points overlap. How can we be sure that the total_points table's points row is the correct points row? Which row in the points table would MySQL update? x//

So, shouldn't there be some way to specify which of the total_points.points rows to update BEFORE INSERT ON odp?

解决方案

Part 1

The delimiters are used for source objects like stored procedure/function, trigger or event. All these objects may have a body - code within BEGIN...END clause.

All statement in MySQL scripts should be ended with delimiter, the default is ';'. But what to do if source object has body with some statements, e,g:

INSERT INTO table1 VALUES(1);

CREATE PROCEDURE procedure1()
BEGIN
  SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
  PREPARE stmt2 FROM @s;
  SET @a = 6;
  SET @b = 8;
  EXECUTE stmt2 USING @a, @b;
END;

INSERT INTO table1 VALUES(2);

How many statemants? 3 or 8? The answer is three, because script has two INSERTs and one CREATE PROCEDURE statements. As you see, CREATE PROCEDURE has some internal statements too; we should say to MySQL client that all these statement (inside BEGIN...END) - are part of ONE statement; we can do it with a help of delimiters:

INSERT INTO table1 VALUES(1);

DELIMITER $$

CREATE PROCEDURE procedure1()
BEGIN
  SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
  PREPARE stmt2 FROM @s;
  SET @a = 6;
  SET @b = 8;
  EXECUTE stmt2 USING @a, @b;
END$$

DELIMITER ;

INSERT INTO table1 VALUES(2);

Note, when your trigger has no BEGIN...END clause, delimiters may be omitted.

---

Part 2

Without delimiters the statement will be parsed as -

CREATE PROCEDURE procedure1()
BEGIN
  SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';

instead of -

CREATE PROCEDURE procedure1()
BEGIN
  SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
  PREPARE stmt2 FROM @s;
  SET @a = 6;
  SET @b = 8;
  EXECUTE stmt2 USING @a, @b;
END

这篇关于MySQL：如何在触发器中使用分隔符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！