archiving table records to another table by trigger(move daialy table records to weekly table, evry day)

Question

I have written this trigger in mysql 5:

create trigger changeToWeeklly after insert on tbl_daily for each row
begin
insert into tbl_weeklly SELECT * FROM vehicleslocation v
where v.recivedate < curdate();
delete FROM tbl_daily where recivedate < curdate();
end;

i want to archive records by date, move yesterday inserted record from dailly to weekly table and last weekly table to mounthly table and deletes this records from previous table this trigger has following error when insert in daily tabled occurred : "Can't update table 'tbl_daily' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."

please help me to solve th problem of archive old data in related tables:

move yesterday inserted records to weekly table, if there is a reliable solution tell me please.

Answer 1

You can't delete rows out of a table from within that same table's insert trigger. In any case I would suggest that it is very advantageous to keep processing in triggers to a minimum.

I personally would have just one insert trigger, on the vehicleslocation table, who's only job would be to copy each inserted row to the tbl_daily archive table. I would then have a separate SQL query that would be scheduled to run automatically just once at the end of each day to perform the required housekeeping on your archive data:- ie. at end of month purge old monthly data, at end of week move all rows from weekly to monthly, and at end of every day move all rows from daily to weekly.