通过触发器将表记录归档到另一个表（将每日表记录移动到每周表，每天）
我在mysql 5中编写了这个触发器：
在 tbl_daily 上插入每行后创建触发器changeToWeeklly开始
插入 tbl_weeklly SELECT * FROM carslocation v
其中 v.recivedate < curdate();
从 tbl_daily 中删除，其中 receivedate < curdate();
结尾;
我想按日期归档记录，将昨天插入的记录从每日表移动到每周表，将最后一周表移动到每月表，并从上一个表中删除此记录 当插入每日表格时，此触发器出现以下错误： “无法更新存储函数/触发器中的表‘tbl_daily’，因为它已被调用此存储函数/触发器的语句使用。”
请帮我解决在相关表中归档旧数据的问题：
将昨天插入的记录移动到周表中，如果有可靠的解决方案请告诉我。
I have written this trigger in mysql 5:
create trigger changeToWeeklly after insert on tbl_daily for each rowbegin
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您无法从同一个表的插入触发器中删除表中的行。无论如何，我建议将触发器中的处理保持在最低限度是非常有利的。
我个人只会在 carslocation 表上有一个插入触发器，其唯一的工作是将每个插入的行复制到 tbl_daily 存档表。然后，我将有一个单独的 SQL 查询，该查询将安排在每天结束时自动运行一次，以对存档数据执行所需的内务处理：- 即。在月底清除旧的每月数据，在周末将所有行从每周移动到每月，并在每天结束时将所有行从每日移动到每周。
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.