MySQL Trigger - delete after update

Question

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 ;

But as I suspect got error:

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.

Solution 1

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

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.

Solution 2

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

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 ;

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;