이벤트

-- 이벤트 스케줄러 활성화 SET GLOBAL event_scheduler = ON; -- 매일 자정에 실행되는 이벤트 생성 CREATE EVENT daily_cleanup ON SCHEDULE EVERY 1 DAY STARTS '2025-05-02 00:00:00' DO DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 이벤트 수정 ALTER EVENT daily_cleanup ON SCHEDULE EVERY 2 DAY DO DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 60 DAY);

-- 이벤트 삭제 DROP EVENT IF EXISTS daily_cleanup; -- 이벤트 비활성화 ALTER EVENT daily_cleanup DISABLE;

SHOW VARIABLES LIKE 'event_scheduler';

CREATE EVENT backup_salaries ON SCHEDULE EVERY 1 MONTH STARTS '2025-06-01 00:00:00' DO INSERT INTO salary_backup SELECT * FROM salaries WHERE from_date > DATE_SUB(NOW(), INTERVAL 1 MONTH);

CREATE EVENT archive_ex_employees ON SCHEDULE EVERY 6 MONTH STARTS '2025-06-01 00:00:00' DO BEGIN INSERT INTO archived_employees SELECT * FROM employees e WHERE e.emp_no IN ( SELECT emp_no FROM dept_emp WHERE to_date < NOW() ); DELETE FROM employees WHERE emp_no IN ( SELECT emp_no FROM archived_employees ); END;

CREATE EVENT update_dept_salary_stats ON SCHEDULE EVERY 1 WEEK STARTS '2025-05-08 00:00:00' DO INSERT INTO dept_salary_stats SELECT d.dept_no, d.dept_name, AVG(s.salary) as avg_salary, NOW() as calculated_at FROM departments d JOIN dept_emp de ON d.dept_no = de.dept_no JOIN salaries s ON de.emp_no = s.emp_no WHERE s.to_date > NOW() GROUP BY d.dept_no, d.dept_name;