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

问题描述

当我尝试在表中插入新行时，我的触发器失败，因为Mysql不支持更新分配触发器的表中的行。有没有人在建议好的解决方法/替代方案？

我的触发器：

   - 触发器DDL语句
 DELIMITER $$ 
 
 CREATE TRIGGER check_sequence 
 BEFORE插入数据之前
 BEGIN 
如果EXISTS TRUE FROM data WHERE sequence = NEW.sequence）THEN 
 UPDATE data SET sequence = sequence + 1 WHERE sequence> = NEW.sequence; 
 END IF; 
 END $$ 
 DELIMITER; 
  

当我尝试插入新行时显示错误Sql：

错误1442（HY000）：无法更新存储函数/触发器中的表'数据'，因为它已被调用此存储函数/触发器的语句使用。感谢您的回答，

方案

在MySQL中，您无法在表A的触发器中更新（或插入）表A。

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

您可以尝试使用sproc并使用事务，或者考虑使用PostgreSQL。

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?

My trigger:

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

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.

Thanks for answer,

解决方案

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

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

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