我可以使用MySQL触发器更新刚刚添加的行吗 [英] Can I update the just added row using MySQL triggers

问题描述

数据库中一列的默认初始值与该行的自动递增ID相同.我正在尝试使用触发器进行设置.

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.

CREATE TRIGGER `default_order_value` 
AFTER INSERT ON `clusters` 
FOR EACH ROW  
BEGIN  
    UPDATE `clusters` SET `order` = NEW.id WHERE `id` = NEW.id; 
END

但这总是引发语法错误

#1064-您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在第5行的''附近使用

#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

我已经尝试过各种各样的排列，但是没有运气.有人可以看到我在做什么错吗?

I've tried all sorts of permutations of this with no luck. Can anyone see what I'm doing wrong?

推荐答案

正如zerkms所说，您需要更改距离.但是由于只使用1行代码，所以不需要BEGIN和END.这样，您也无需更改定界符

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

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:

完全不执行更新查询.默认情况下，订单值= ID值.因此，当订单值更改时，您可以正确地对其进行更新.

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.

如果您要使用php请求数据，请执行以下操作:

If you are requesting the data with php, do something like this:

$order = $row['order'];
if ($order == '')
    $order = $row['id'];

需要更新后，您将获得正确的值.

After you need it updating, you've got the correct value.

这篇关于我可以使用MySQL触发器更新刚刚添加的行吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！