MySQL触发器-更新后删除 [英] MySQL Trigger - delete after update

问题描述

对于一个项目中的测试用例，如果其中一个字段满足条件之一，则我必须删除记录. 最简单的触发似乎是简单的:

For test case in one project I must delete record if one of fields meets one of conditions. Easiest seems be simple trigger:

delimiter $$
drop trigger w_leb_go $$

create trigger w_leb_go after update on test
for each row
begin
set @stat=NEW.res;
set @id=NEW.id;
IF @stat=3 THEN
  delete from test where id=@id;
END IF;
end$$

delimiter ;

但是我怀疑有错误:

更新测试集res = 3，其中id = 1；
错误1442(HY000):无法更新存储函数/触发器中的表"test"，因为调用该存储函数/触发器的语句已使用该表.

update test set res=3 where id=1;
ERROR 1442 (HY000): Can't update table 'test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

仅通过访问数据库，还能怎么做?我的意思是我不应该更改经过测试的应用程序.

How else can be done only by having access to the database? I mean that I should not change the tested application.

推荐答案

您可能需要做的是使用触发器的插入前"检查所添加的记录是否无效.

What you may need to do is use the 'BEFORE INSERT' of the trigger to check if the record being added is not valid.

这个SO问题提供了有关如何处置无效记录的一些详细信息:

This SO question provides some detail on how to dipose of the invalid record: Prevent Insert

另一种方法是修改程序，以将记录插入到另一个表中，该表具有清除所需表的触发器.

The alternate is to modify your program to insert a record into another table which has a trigger to purge the desired table.

这篇关于MySQL触发器-更新后删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！