使用插入前触发器在表中插入和删除数据 [英] Insert and delete data in table using before insert trigger

问题描述

我创建了一个触发器，该触发器将会话表的最后一条记录插入 conversation_h 表中，并从 conversation 使用插入前事件。

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 ;

推荐答案

使用触发器无法做到：

（...）触发器无法修改调用（...）触发器的语句已在使用（用于读取或写入）的表。

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

这篇关于使用插入前触发器在表中插入和删除数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！