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

What is the data type of `timestamp`? – Gordon Linoff Jul 06 '15 at 11:53 — Gordon Linoff, Jul 06 '15 at 11:53
@GordonLinoff It's `DATETIME` like 0000-00-00 00:00:00. – lingo Jul 06 '15 at 12:26 — lingo, Jul 06 '15 at 12:26

score 6 · Accepted Answer · answered Jul 06 '15 at 12:02

6

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.

answered Jul 06 '15 at 12:02

Aman Aggarwal

17,619
9
53
81

I also tried to create an event, but even that didn't work. @AmanAggarwal – lingo Jul 06 '15 at 12:29
its better to create a small procedure and run it via code when you insert the data in table.. – Aman Aggarwal Jul 06 '15 at 12:33
So you can't use `DELETE` in MySQL events? – lingo Jul 06 '15 at 12:50

score 0 · Answer 2 · answered Jul 06 '15 at 11:54

0

try DATE_SUB(NOW(), INTERVAL 1 MONTH)

answered Jul 06 '15 at 11:54

s k nagesh

98
9

I changed `timestamp < (NOW() - INTERVAL 1 MONTH)` to `timestamp < DATE_SUB(NOW(), INTERVAL 1 MONTH)` and no effect. – lingo Jul 07 '15 at 17:37

MySQL trigger DELETE old rows AFTER INSERT

2 Answers2

Linked

Related