0

I want to keep only last 5 records in my table. So I intended to create a trigger (on MyTable) which would after insert of a new record delete all records except last 5 ones. When I try to create this trigger:

DELIMITER //
CREATE TRIGGER DeleteOldRecords AFTER INSERT ON MyTable FOR EACH ROW
BEGIN
  DELETE FROM MyTable WHERE id NOT IN
  (SELECT id FROM MyTable ORDER BY id DESC LIMIT 5);
END//

I get the following error message: Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'. I use MySQL v5.7.14

If I enclose the IN clause into subquery like this:

DELIMITER //
CREATE TRIGGER DeleteOldRecords AFTER INSERT ON MyTable FOR EACH ROW
BEGIN
  DELETE FROM MyTable WHERE id NOT IN
  (SELECT id FROM (SELECT id FROM MyTable ORDER BY id DESC LIMIT 5) x);
END//

I'm able to create such a trigger but now I cannot insert records into my table MyTable. I get the following error message:

Error Code: 1442. Can't update table 'MyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

What could be the solution? (I don't want cron job for deleting old records)

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
sbrbot
  • 6,169
  • 6
  • 43
  • 74
  • 1
    why do you want to do this? Surely it's simpler to just run a query to fetch the last 5 if that's all you want to see most of the time. The rest can be there for audit purposes. You won't see any performance issues generally until you have many thousands of rows (maybe even millions), so cleaning up the data like this is not particularly an urgent task really. – ADyson Nov 28 '18 at 10:28
  • Yes, that's how I do it now, fetch and show only last 5 records. I don't have many thousands of rows in this table at all (this is table of site settings where I want to keep just few last settings for restore). I don't see any performance issue here. I just wanted this for the sake of db hygiene (to delete not used records) not for the sake of performance. It's not that I deadly need this functionality, but now I'm curious why I have this issue with this trigger. – sbrbot Nov 28 '18 at 10:38
  • 1
    Issues are well explained in the error message you get :-) Triggers does not allow updating the same table (on which Trigger is defined). If you dont want Cronjob, then you can use MySQL events instead. – Madhur Bhaiya Nov 28 '18 at 10:40
  • 'updating the table from a trigger would then cause the same trigger to fire again in an infinite recursive loop' - https://stackoverflow.com/questions/15300673/mysql-error-cant-update-table-in-stored-function-trigger-because-it-is-already . You can substitute delete for update in tie quote. – P.Salmon Nov 28 '18 at 12:41

0 Answers0