我写了一个触发器,作用是:
当新数据插入后,只保留改变最近5条记录,其他记录删除:
delimiter //
CREATE TRIGGER tbl1_ai AFTER INSERT ON tbl1
FOR EACH ROW BEGIN delete from tbl1 where (select count(*) from tbl1 where SingInDateTime >= NEW.SingInDateTime) >5;
END;
delimiter;
当我插入数据,insert into tbl1 values
(null,now(),'Report','1');
提示错误:
SQL ERROR 1442 : Can't update table 'tbl1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.在触发器中如何删除本表记录?
还是这个操作不适合用触发器来做呢?
当新数据插入后,只保留改变最近5条记录,其他记录删除:
delimiter //
CREATE TRIGGER tbl1_ai AFTER INSERT ON tbl1
FOR EACH ROW BEGIN delete from tbl1 where (select count(*) from tbl1 where SingInDateTime >= NEW.SingInDateTime) >5;
END;
delimiter;
当我插入数据,insert into tbl1 values
(null,now(),'Report','1');
提示错误:
SQL ERROR 1442 : Can't update table 'tbl1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.在触发器中如何删除本表记录?
还是这个操作不适合用触发器来做呢?
定时执行mysql数据库任务方案。