Suppose you no longer need to keep salary audit records that are more than a month old, you can schedule an event that runs daily and deletes the records from the salary_audit table that are a month old.
mysql> DROP EVENT IF EXISTS purge_salary_audit;DELIMITER $$CREATE EVENT IF NOT EXISTS purge_salary_auditON SCHEDULE EVERY 1 WEEK STARTS CURRENT_DATE DO BEGIN DELETE FROM salary_audit WHERE date_modified < DATE_ADD(CURDATE(), INTERVAL -7 day); END $$DELIMITER ;
Once the event is created, it will automatically do the job of purging the salary audit records.
- To check the events, execute:
mysql> SHOW EVENTS\G*************************** 1. row *************************** Db: employees Name: purge_salary_audit Definer: root@localhost ...
