目标:就是实现对一个实时写入表的记录的删除,只需要保留一天的数据即可
新建了一个触发器:
delimiter
create trigger t_anomalyTable_ai
after insert on anomalyTable
for each row
delete from anomalyTable where time < '2010-08-06 00:00:00';
delimiter
写完后插入数据的时候
insert into nf_analyzer.anomalyTable (anomaly,type,time) values('124','dod',now())会报错:
Can't update table 'anomalyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.第一次用触发器,希望各位高手知道下!!
新建了一个触发器:
delimiter
create trigger t_anomalyTable_ai
after insert on anomalyTable
for each row
delete from anomalyTable where time < '2010-08-06 00:00:00';
delimiter
写完后插入数据的时候
insert into nf_analyzer.anomalyTable (anomaly,type,time) values('124','dod',now())会报错:
Can't update table 'anomalyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.第一次用触发器,希望各位高手知道下!!
--具体如下:
CREATE TABLE muBiaoTb (....) ;--这里要跟你的anomalyTable表结构一样 delimiter $$
create trigger t_anomalyTable_ai
after insert on muBiaoTb --注意把触发器建立在muBiaoTb上
for each row
BEGIN
delete from anomalyTable where time < now();--删除你的anomalyTable不是今天的数据
INSERT anomalyTable --插入你的数据
SELECT new.col1,new.col2 ..... ;---这里写出你的列表就好了
END ;
$$
delimiter ;
ps:你插入数据的时候 要插入在muBiaoTb 上 触发上面的触发器