MySQL 触发器 - 更新后删除

2022-01-17 00:00:00 sql-update sql mysql sql-delete triggers

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

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 where id=1;
错误 1442 (HY000):无法更新存储函数/触发器中的表测试"，因为它已被调用此存储函数/触发器的语句使用.

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.

推荐答案

在触发器内部，您不能更改触发器所属的表.
你也不能间接改变那张桌子.

Inside a trigger you cannot alter the table to which the trigger belongs.
Nor can you something which indirectly alters that table.

不过，您还可以做一些其他事情.如果您不删除一行，而是添加一个字段deleted，那么您可以像这样mark将其标记为已删除.

There are however a few other things you can do. If you don't delete a row, but add a field deleted then you can mark it as deleted like so.

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 之前.

Note that the trigger must be before if you want to alter anything in the row.

或者，您可以向某个临时表添加删除提醒，并在更新语句之后测试该表.

Alternatively you can add a deletion reminder to some temporary table and test that table after your update statement. 

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

现在您需要将更新语句更改为:

Now you need to change your update statement to: 

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;

当然，如果您标记您的行，您可以将删除代码简化为:

Of course if you mark your rows you can simplify the deletion code to: 

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

相关文章