在MYSQL中编写触发器 [英] Write a Trigger in MYSQL

问题描述

在我的项目中，需要在具有列(Id，用户ID，Mobiles，Message，MarylinID，Processed，TableNumber)的表(eis_batchmessage)上编写触发器.
列userids，Mobiles，MarylinID包含多个用逗号分隔的值，例如

用户标识为:"1427,4589,2358"
手机的名称是:"91992673545,9176635522,9176342322"

我有另一个表(mymessage)，其中有列(id，userid，mobiles，message，marylinid)

当数据插入到eis_batchmessage中时，插入触发器将触发，并且逗号分隔的值将被拆分并插入mymessage表中.

谁能帮助我编写/完成触发器.我在吐出值并插入时遇到问题.

我到目前为止的代码如下

Hi,

In my Project there is a requirement to write a trigger on a Table (eis_batchmessage) having columns (Id,userids,Mobiles,Message,MarylinID,Processed,TableNumber).
The column userids,Mobiles,MarylinID contains multiple value separated by comma like

for userid is : "1427,4589,2358"
for Mobiles is : "91992673545,9176635522,9176342322"

I have another table (mymessage) having columns (id,userid,mobiles,message,marylinid)

When data insert into eis_batchmessage then after insert trigger will fire and comma separate value will split and insert into the mymessage table.

Can anybody help me to write/complete my trigger. I am facing problem in spiting the value and inserting.

My so far code is below

create trigger ins_BachProcess_trig after insert on eis_batchmessage
for each row
BEGIN
Select NEW.Id, NEW.userids, NEW.Mobiles, NEW.Message, NEW.MarylinID, NEW.Processed, NEW.TableNumber from eis_batchmessage;
call InsertBatch()
END;



CREATE PROCEDURE InsertBatch()
BEGIN

DECLARE cur1 CURSOR FOR  Id,userids,Mobiles,Message,MarylinID,Processed,TableNumber from eis_batchmessage

DECLARE v_id BIGINT;
DECLARE v_userid TEXT;
DECLARE v_Mobiles TEXT;
DECLARE v_Message varchar(260);
DECLARE v_Marylinid BIGINT;
DECLARE v_processed INT;
DECLARE v_tableno INT;

OPEN cur1;


CLOSE cur1;

END

推荐答案

看看本文SQL用户定义的函数，用于解析分隔的字符串 [

这篇关于在MYSQL中编写触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！