MySQL触发器-更新后删除

Question

在一个项目的测试用例中，如果某个字段满足某个条件之一，我必须删除记录。最简单的方法似乎是一个简单的触发器：

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 ;

但我怀疑发生了错误：

update test set res=3 where id=1; ERROR 1442 (HY000): 因为语句已被调用存储函数/触发器使用，无法在存储函数/触发器中更新表“test”。

如果只有访问数据库的权限，还有什么其他方法可以实现？我的意思是我不应该更改被测试的应用程序。

- parasit

Answer 1

触发器内不能更改其所属的表，也不能间接地更改该表。

然而，您可以做一些其他事情。如果不删除行，但添加一个名为 deleted 的字段，则可以将其标记为已删除，如下所示。

delimiter $$
drop trigger w_leb_go $$

CREATE TRIGGER w_leb_go BEFORE UPDATE ON test FOR EACH ROW
BEGIN
  IF NEW.res=3 THEN
    SET NEW.deleted = 1;
  END IF;
END$$

delimiter ;

请注意，如果您想要更改行中的任何内容，则触发器必须是before。或者，您可以将删除提醒添加到某个临时表中，并在更新语句后测试该表。

CREATE TRIGGER au_test_each AFTER UPDATE ON test FOR EACH ROW
BEGIN
  IF NEW.res=3 THEN
    INSERT INTO deletion_list_for_table_test (test_id) VALUE (NEW.id);
  END IF;
END$$

现在你需要修改你的更新语句为：

START TRANSACTION;
UPDATE test SET whatever to whichever;
DELETE test FROM test
INNER JOIN deletion_list_for_table_test dt ON (test.id = dt.test_id);
DELETE FROM deletion_list_for_table_test WHERE test_id > 0 /*i.e. everything*/
COMMIT;

当然，如果你对行进行了标记，你可以简化删除代码为：

START TRANSACTION;
UPDATE test SET whatever to whichever;
DELETE FROM test WHERE deleted = 1;
COMMIT;

- Johan

Answer 2

你可能需要做的是使用触发器的“BEFORE INSERT”来检查正在添加的记录是否无效。

这个SO问题提供了一些关于如何处理无效记录的细节：Prevent Insert 另一种方法是修改程序，将记录插入到另一个具有清除所需表的触发器的表中。

- John M

Answer 3

我的几分建议：- 添加“已删除”列被认为是表结构的更改。 因此，除非是新开发，否则不建议这样做。

如果这是遗留应用程序，则最好- 将删除提醒添加到某个临时表中，并在更新语句后测试该表。

- Shiva