MySQL trigger after insert with insert and delete instructions does not work properly

Question

I have the following trigger created in phpMyAdmin:

Table: tb_agenda Time: AFTER Event: INSERT

BEGIN
 INSERT INTO tb_realizacao (dt_agenda,
 titulo, 
 titulo_en, 
 descricao, 
 descricao_en, 
 dt_cadastro)
 SELECT dt_agenda,
 titulo,
 titulo_en,
 descricao,
 descricao_en,
 dt_cadastro
 FROM tb_agenda
 WHERE dt_agenda < NOW();
 DELETE FROM tb_agenda
 WHERE dt_agenda < NOW();
END

The trigger works 50%. The INSERT instruction works perfectly but the DELETE one doesn't execute. I tried to execute it in the SQL panel and it works fine.

• Rick James: Hmmm... I wonder if there could sometimes be two different values for NOW().
• Masoud: just a question. Why are you executing SELECT statement in an after insert trigger? And Also it maybe better to provide the full trigger body.
• Michael - sqlbot: @RickJames there shouldn't be: NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.
• Rick James: Good. I'm at a loss to explain what you are experiencing.
• user69089: So, no ideas why the DELETE isn't executed?

Answer 1

Your trigger tries to do something that cannot be done in MySQL. You cannot use an SQL statement (DELETE, in your case) on the table that is associated to the trigger. You will get an error like this:

ERROR 1442 (HY000): Can't update table 't' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

You are probably using MyISAM for your table. The DELETE does not cause a rollback, because the engine is not transactional. If you switch to InnoDB (which is usually the best choice) you will notice that the trigger and the original INSERT will completely fail.