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.
Best Answer
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:
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.