MySQL trigger DELETE old rows AFTER INSERT

Question

Whats wrong with this MySQL trigger? After insert I'm trying to delete rows older than 1 month. This trigger only removes the last inserted row.

CREATE TRIGGER `users_logins_delete_olds` 
AFTER INSERT ON `users_logins` FOR EACH ROW 
BEGIN
    DELETE FROM users_logins WHERE user_id = new.user_id AND timestamp < (NOW() - INTERVAL 1 MONTH);
END

Answer 1

You can't delete the rows from the same table on which you implement the trigger. Because on insert mysql locks the table and can't delete the rows because delete needs locking so its a deadlock sitution, that's why mysql would not allow this.

Answer 2

try DATE_SUB(NOW(), INTERVAL 1 MONTH)