MySQL Error: Can't update table 'tbl'

Question

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

Trigger before insert code:

CREATE DEFINER=`root`@`localhost` TRIGGER `opcdls`.`tblTrigger` 
BEFORE INSERT ON `tbl` FOR EACH ROW
BEGIN
    DELETE FROM tbl 
    WHERE ProxyLoggingDate <= DATE_SUB(NOW(), INTERVAL 7 WEEK);
END

I want to create a trigger where before each insert check each row and delete the old.

But when I try to add a ro on this table it gives me below error

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

What does this error mean? Thanks

Answer 1

Instead a trigger i create a event and it is working properly.

SET GLOBAL event_scheduler = ON; CREATE EVENT IF NOT EXISTS tbl ON SCHEDULE EVERY 1 MINUTE STARTS NOW() DO DELETE FROM tbl WHERE ProxyLoggingDate <= DATE_SUB(NOW(), INTERVAL 7 WEEK);