在mysql中通过触发器删除插入表中的同一行 [英] Delete the same row inserted in a table by trigger in mysql

问题描述

我试图在插入临时表后删除一行，但出现此错误:

I'm trying to remove a row after inserted in a temp table and I get this error:

INSERT INTO `temp_program_counter` (`id`, `program_id`) values (NULL, '275')

无法更新存储函数/触发器中的表'temp_program_counter'，因为调用该存储函数/触发器的语句已使用该表.

Can't update table 'temp_program_counter' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

这是我的触发器:

DELIMITER $$
DROP TRIGGER IF EXISTS `testtemp`$$

CREATE

    TRIGGER `testtemp` AFTER INSERT ON `temp_program_counter` 
    FOR EACH ROW BEGIN
        UPDATE `program` SET `view_count`=`view_count`+1;
        DELETE  FROM `temp_program_counter` WHERE id=new.id;
    END;
$$

DELIMITER ;

此表是一个临时表，该表会延迟获取数据，并且在接收后必须更新主表并从临时表中删除. 感谢您的提前帮助.

this table is a temp table that get's data with delay and after receiving must update the main table and be deleted from temp. thanks for helping in advance.

推荐答案

您可能已经以某种方式解决了这个问题，但我还是会发布答案:

You probably have already resolved this in some way but I'll post the answer anyway:

您不能在触发器(或触发器和存储过程的组合)中引用触发触发器的同一表.这可能意味着一个无限循环，因此sql会通过给出该错误来防止它发生，从而始终导致相同的错误.

You cannot refer to the same table that triggered the trigger in a trigger (or a combination of trigger and stored procedure). This could mean an infinite loop, so sql prevents it by giving that error, resulting in always the same error.

这篇关于在mysql中通过触发器删除插入表中的同一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！