MySql: Trigger to delete old rows not working

Asked Oct 01 '22 at 19:09

Active Oct 01 '22 at 19:09

Viewed 19 times

I have written a trigger to delete all rows older that 1 day in the table on every insert. But whenever I insert a new row, it is throwing the following error

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

Here is the trigger I wrote

CREATE TRIGGER delete_testing
AFTER INSERT ON example
FOR EACH ROW
DELETE FROM example
WHERE date_created < DATE_SUB(NOW(), INTERVAL 1 DAY)

asked Oct 01 '22 at 19:09

Sarthak

1

You can't do this from a trigger, use transaction to combine insert and delete together. – Shadow Oct 01 '22 at 21:08

MySql: Trigger to delete old rows not working

0 Answers0