0

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

CREATE DEFINER=`root`@`localhost` TRIGGER `triggerName` BEFORE INSERT ON `tableName` FOR EACH ROW BEGIN
    DECLARE current_count INT;
        DECLARE oldest_data INT;
        SELECT count(*) INTO current_count FROM tableName;
        IF (current_count >= 500) THEN      
            SELECT min(ID) INTO oldest_data FROM tableName;
            DELETE FROM tableName WHERE ID=oldest_data;
        END IF;
    END

The above trigger makes sure that there are only 500 records(latest) in a table. When new data comes in old data has to be deleted. I am unable to achieve this simple use case using triggers because of the above mentioned error.

People talk deadlocks but I am using innodb engine with MySQL 8. I read that innodb engine imposes row level lock hence deleting 'X' row before inserting 'Y' row should not cause any recursive triggers or waiting for locks. What am I missing????????

Srinidhi Sundaravaradhan
  • 33
  • 7
  • https://stackoverflow.com/questions/53517234/a-trigger-that-will-keep-only-last-n-records is related but everyone suggests alternative methods – Srinidhi Sundaravaradhan Jan 06 '22 at 09:54
  • Pick one that suits you - – P.Salmon Jan 06 '22 at 10:39
  • 2
    [MySQL just can't do this in a trigger](https://stackoverflow.com/a/12878145). You cannot modify the table that the trigger is triggered by, except for the one row that just got inserted. It has nothing to do with deadlocks or (not) using InnoDB or some syntax you may not have figured out yet. It's just MySQL, it cannot do it. A "workaround" (if you want to call it that) would be to delete old rows yourself in a separate query, before (or after) you insert new rows, in the same transaction, maybe in a stored procedure. – Solarflare Jan 06 '22 at 11:05

0 Answers0