archiving table records to another table by trigger(move daialy table records to weekly table, evry
- by sirvan
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.
