MySQL: how do I limit the number of records stored for a value of a column?

Question

Lets say I have a column of user ID's storing past login successes.

Now, I only want the most recent 20 login attempts, so Is there a way to specify the table to not store more than 20 records per user id?

Answer 1

There are a few possible ways, one would be to use a Stored Procedure to insert the records. That way, the SP would check if there are more than 20 and drop old ones after inserting the new one. The drawback is that if you manually insert a row, it won't get dropped.

A trigger might work... Create an AFTER INSERT trigger that then deletes the older logins > 20...