当我的触发器被调用时，Mysql 无法更新行 [英] Mysql can't update rows when my trigger is called

问题描述

当我尝试在我的表中插入新行时，我的触发器失败，因为 Mysql 不支持更新分配给触发器的同一个表中的行.有人对好的解决方法/替代方案有什么建议吗?

My trigger fails when i try to insert a new row in my table because Mysql doesn't support updating rows in the same table the trigger is assigned to. Does anyone have suggestions on a good workaround/alternative?

我的触发器:

-- Trigger DDL Statements
DELIMITER $$

CREATE TRIGGER check_sequence
BEFORE INSERT ON data FOR EACH ROW
BEGIN 
    IF EXISTS(SELECT TRUE FROM data WHERE sequence = NEW.sequence) THEN
        UPDATE data SET sequence=sequence+1 WHERE sequence >= NEW.sequence;
    END IF;
END $$
DELIMITER ;

尝试插入新行时显示的错误 Sql:

Error Sql that is displayed when i try to insert new row:

ERROR 1442 (HY000): Can't update table 'data' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

感谢您的回答，

推荐答案

在 MySQL 中，您不能在表 A 的触发器中更新(或插入)表 A.

In MySQL, You can't update (or insert in) table A in a trigger for table A.

http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html

您可以尝试使用 sproc 并使用事务，或者考虑 PostgreSQL，它可以做到这一点.

You can try a sproc and use a transaction, or consider PostgreSQL, which can do this.

这篇关于当我的触发器被调用时，Mysql 无法更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！