Insert and delete data in table using before inser

Question

I have created a trigger which inserts the last record of conversation table in conversation_h table, and delete that record from conversation using before insert event.

Any other logic or code is also welcome but i want achieve the above using trigger only.

DELIMITER $$
CREATE
    TRIGGER `transfer_data` BEFORE INSERT 
    ON `conversation`

    FOR EACH ROW 

BEGIN

if  (select count(*) from `conversation`) > 2
then 

  set @mid = (select id from conversation order by id asc limit 0,1);

insert into conversation_h select * from conversation where id=@mid;

delete from conversation where id=@mid;


    END IF;

    END$$
DELIMITER ;

Answer 1

This is not possible with triggers:

A (...) trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the (...) trigger.