First of all, I come from China. So please forget my poor and wrong English!
What I want:
I use mysql to store my data. The data will change everday untill a month pass. So evey day I need to delete old data from mysql and insert new data into tables.
I try use the follow trigger to help me easy my work, but it gets error. Why, what`s wrong!
delimiter $$
drop trigger if exists `insert_sell_before`;
create trigger if not exists `insert_sell_before` before insert on `my_table_name` for each row
begin
declare start_date date;
declare end_date date;
select min(NEW.`date_field`),max(NEW.`date_field`) into start_date,end_date;
delete from `my_table_name` where `date_filed` between start_date and end_date;
end$$
(1442, "Can't update table '电话网络情况明细表' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.")
Please help me. Thanks very much!
What` wrong. I can I correct it?
select min(NEW.`date_field`),max(NEW.`date_field`) ..-NEW.date_fieldis current row value, not the whole table values. So MIN/MAX taken over ONE value makes no sense, you'll obtain the same current value.for each rowmean. I will use 2 separate queries to achieve my goal. Is there any button here to close the question since you already helped me solve the problem.