Hello,
I am working on a project about employees and one of the requirement is that 5 years old employees should be archived to a text file somewhere and removed from database. I don't have that much experience with administrating DBs. What is the best way to go about this? Write triggers of some sort?
Thanks, Nishant
Use mysqldump, using the --where parameter to look for employee records that are at least 5 years old. You can restore from a mysqldump.
Once you have these stored to file, you can delete the rows from the table.
Only issue is this has to be done periodically, and the file results will only be the old records that currently exist in the table. But if inclined, you could combine the periodic output into a script containing all the removed employee records.
When data does not change, you cannot use triggers to do something with it. (Well you could create a trigger that does wildly inappropriate things on a table that changes, but I'd advise very strongly against it).
Create a script that does what you want (select employees, save to file, run delete query for the exact employees you have saved, NOT the employees from 5 years ago after you've finished, as it can be that some records just overstepped the mark between selecting them & saving them to file).
Run this every N time, N being the reasonable amount you may overstep the 'after exactly one year' mark (depending on circumstances daily, weekly or monthly most likely). Schedule that as a cron on the server, and do check up on it once in while to confirm it is still working.