更新时间:2023-02-04 23:23:06
在触发器内部,您不能更改触发器所属的表.
你也不能间接改变那张桌子.
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;