在触发器内部,您无法更改触发器所属的表
你也不能间接改变那张桌子。
但是,您可以做一些其他事情。如果你没有删除一行,但添加一个字段deleted那么你可以标记 kbd>它就像删除一样。
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;