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.

Hmmm... I wonder if there could sometimes be two different values for NOW(). — Rick James, Jun 23 '15 at 01:32
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. — Masoud, Jun 23 '15 at 01:52
@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. — Michael - sqlbot, Jun 23 '15 at 11:31
If you are trying to DELETE the row whose INSERT caused the Trigger to fire; that won't work -- The INSERT does not happen until the Trigger finishes. That is, the DELETE did run, but found nothing to delete. — Rick James, Jun 23 '15 at 16:58
Are you trying to "Create an Insert trigger to automatically move records to a different table". If so, state that in the question. If not, then please explain the intent of the trigger. — Rick James, Jun 23 '15 at 17:00
I have the table tb_agenda that everytime it's populated with new data, the trigger inserts into tb_realizacao the data from tb_agenda which dt_agenda (date) is less than NOW(), then exclude these old data from tb_agenda. Summarizing, the tb_agenda is the next events that will happen, and tb_realizacao is the events that already happened. — , Jun 23 '15 at 17:37
If I create a trigger for tb_realizacao, when adding a new data only one from tb_agenda is added into the other table, not to mention the DELETE doesn't work. I don't know what to do. — , Jun 23 '15 at 22:32
On a side note, you should not use INSERT SELECT as this will cause a lock on all of the SELECTED ROWS. As noted by others the DELETE will also cause huge rollback segments potentially. It is better to do an INSERT with a seperate select from a CURSOR. It is also better to DELETE with a limit until no more rows can be deleted. — winmutt, Jul 02 '15 at 21:37

score 2 · Answer 1 · answered Jul 03 '15 at 01:04

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.

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

1 Answers1