我可以使用 MySQL 触发器更新刚刚添加的行吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9353042/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I update the just added row using MySQL triggers
提问by wheresrhys
The default initial value of one column in my database is the same as the row's auto-incremented id. I'm trying to use triggers to set it.
我的数据库中一列的默认初始值与该行的自动递增 ID 相同。我正在尝试使用触发器来设置它。
CREATE TRIGGER `default_order_value`
AFTER INSERT ON `clusters`
FOR EACH ROW
BEGIN
UPDATE `clusters` SET `order` = NEW.id WHERE `id` = NEW.id;
END
But this keeps throwing a syntax error
但这不断抛出语法错误
#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 5
#1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以了解在第 5 行的 '' 附近使用的正确语法
I've tried all sorts of permutations of this with no luck. Can anyone see what I'm doing wrong?
我已经尝试了各种各样的排列，但没有运气。谁能看到我做错了什么？
回答by Rene Pot
As zerkms said, you need to change the delimeter. But since you only use 1 line of code, you don't need the BEGIN and END. And that way, you don't need to change the delimiter either
正如zerkms所说，您需要更改分隔符。但由于您只使用 1 行代码，因此不需要 BEGIN 和 END。这样，您也不需要更改分隔符
CREATE TRIGGER `default_order_value`
AFTER INSERT ON `clusters`
FOR EACH ROW
UPDATE `clusters` SET `order` = NEW.id WHERE `id` = NEW.id;
Since you are getting an error you cannot update the row, I suggest the following:
由于您收到错误，您无法更新该行，我建议如下：
Do NOT perform the update query at all. On default the order value = the ID value. So when the order value changes, you can update it properly.
根本不要执行更新查询。默认情况下，订单值 = ID 值。所以当订单值发生变化时，您可以正确更新它。
If you are requesting the data with php, do something like this:
如果您使用 php 请求数据，请执行以下操作：
$order = $row['order'];
if ($order == '')
$order = $row['id'];
After you need it updating, you've got the correct value.
在您需要更新之后，您就获得了正确的值。
回答by ypercube??
I don't think you can do that. An AFTER INSERTtrigger cannot modify the same table, neither by issuing an UPDATEnor by something like this:
我不认为你可以这样做。一个AFTER INSERT触发器不能修改同一个表，既不发出UPDATE也不是由这样的事情：
CREATE TRIGGER `default_order_value`
AFTER INSERT ON `clusters`
FOR EACH ROW
SET NEW.`order` = NEW.id ;
which results in this error:
这导致此错误：
> Error Code: 1362. Updating of NEW row is not allowed in after trigger
You can't either use a BEFORE INSERTtrigger because then the NEW.idis not known (if you modify the above, the ordercolumn will get 0value after the Insert.
您不能使用BEFORE INSERT触发器，因为那时NEW.id是未知的（如果您修改上述内容，则该order列将0在插入后获得值。
What you can do, is use a transaction:
您可以做的是使用事务：
START TRANSACTION ;
INSERT INTO clusters (id)
VALUES (NULL);
UPDATE clusters
SET `order` = id
WHERE id = LAST_INSERT_ID();
COMMIT ;
回答by zerkms
You get the error because mysql treats ;in line 5 as the end of your trigger declaration, which obviously leads to the syntax error.
您收到错误是因为 mysql 将;第 5 行中的内容视为触发器声明的结尾，这显然会导致语法错误。
So you need to redefine delimiter before you specify the trigger body:
所以你需要在指定触发器主体之前重新定义分隔符：
delimiter |
CREATE TRIGGER `default_order_value`
AFTER INSERT ON `clusters`
FOR EACH ROW
BEGIN
UPDATE `clusters` SET `order` = NEW.id WHERE `id` = NEW.id;
END;
|
delimiter ;
回答by vishal
You can create just BEFORE INSERT TRIGGER, it's works like this:
你可以在 INSERT TRIGGER 之前创建，它的工作原理是这样的：
CREATE TRIGGER `default_order_value`
BeFORE INSERT ON `clusters`
FOR EACH ROW
BEGIN
SET NEW.`order` = NEW.id ;
END
same as below we are using
与下面相同，我们正在使用
DELIMITER $$
USE `e_store`$$
DROP TRIGGER /*!50032 IF EXISTS */ `Test`$$
CREATE
/*!50017 DEFINER = 'root'@'%' */
TRIGGER `Test` BEFORE INSERT ON `categories`
FOR EACH ROW
BEGIN
DECLARE vtype VARCHAR(250) DEFAULT NULL;
SET vtype = NEW.name;
IF (NEW.MDNAME IS NULL)
THEN
-- SET NEW.MDNAME = 'NA';
SET NEW.MDNAME=MD5(NEW.name);
END IF;
END;
$$
DELIMITER ;
回答by Wayne Strickland
This worked for me:
这对我有用：
CREATE TRIGGER `update_table_2`
AFTER UPDATE ON `table_1`
FOR EACH ROW
UPDATE table2
JOIN table_1
SET table_2.the_column = NEW.the_column
WHERE table_2.auto_increment_field = OLD.auto_increment_field
