问一个触发器的问题
有张表设置了插入触发器来修改另张表
CREATE DEFINER='trgacc'@'localhost' TRIGGER CONF_CDR after insert ON `BillLog`
FOR EACH ROW BEGIN
select Id into @ConfId from Interface_Data.Conf_List where BillNum = NEW.BillNum and
CallerNum = New.CallerNum and CalledNum = New.CalledNum and CallBeginTime = '' order by Id limit
1;
if @ConfId IS NOT NULL then
insert into test.DEBUG_TABLE ( JCON_BillNum ,JCON_CallerNum,JCON_CalledNum
,INS_Id,DO_time,Bill_InsertTime) values(NEW.BillNum,NEW.CallerNum,NEW.CalledNum,@ConfId,now
(),NEW.InsertTime);
update Interface_Data.Conf_List set CallBeginTime = NEW.`CallBeginTime`, CallEndTime =
NEW.`CallEndTime`, CallTimer = NEW.`CallTimer` ,BillSource=NEW.`BillSource` where Id = @ConfId;
end if;
END;
实际操作中发现许多不符合条件的也对Conf_List做了update ,这些不符合条件的插入语句是并发的,目前判定 是前面一条符合条件的对判断条件
变量@ConfId设置了值,而后一条不符合条件的由于前面设置了值那么也执行了合法的插入,导致Conf_List数据乱掉,这个有什么好的解决办法?
CREATE DEFINER='trgacc'@'localhost' TRIGGER CONF_CDR after insert ON `BillLog`
FOR EACH ROW BEGIN
select Id into @ConfId from Interface_Data.Conf_List where BillNum = NEW.BillNum and
CallerNum = New.CallerNum and CalledNum = New.CalledNum and CallBeginTime = '' order by Id limit
1;
if @ConfId IS NOT NULL then
insert into test.DEBUG_TABLE ( JCON_BillNum ,JCON_CallerNum,JCON_CalledNum
,INS_Id,DO_time,Bill_InsertTime) values(NEW.BillNum,NEW.CallerNum,NEW.CalledNum,@ConfId,now
(),NEW.InsertTime);
update Interface_Data.Conf_List set CallBeginTime = NEW.`CallBeginTime`, CallEndTime =
NEW.`CallEndTime`, CallTimer = NEW.`CallTimer` ,BillSource=NEW.`BillSource` where Id = @ConfId;
end if;
END;
实际操作中发现许多不符合条件的也对Conf_List做了update ,这些不符合条件的插入语句是并发的,目前判定 是前面一条符合条件的对判断条件
变量@ConfId设置了值,而后一条不符合条件的由于前面设置了值那么也执行了合法的插入,导致Conf_List数据乱掉,这个有什么好的解决办法?
作者: justlooks 发布时间: 2011-06-13
作者: jslifelee 发布时间: 2011-06-14