Using a Trigger for Archiving

Databases
1

Hi guys,

I have two tables which are identical, save for the fact that one is InnoDB and the other Archive. What I want to do is to have all records that are over 60 days old moved from the InnoDB table to the Archive table. I considered a CRON job, but then wondered if a Trigger would be the way to go instead? So when a field is inserted in to the InnoDB table, the Trigger fires and uses INSERT INTO…SELECT to copy data to the Archive table and then delete the old data from the first table.

So this leads to two questions, which are basically:

  1. Is this a wise way to do it

and if so

  1. How do I handle the date range? I don’t want to use DATE_SUB() on account of the fact that I don’t want to break replication, so I’d prefer to just specify a date, and I’m not entirely sure whether I can do that with a Trigger.

Cheers guys