前不久我随便写了项目中的一个触发器,里面要用判断select选取的值,不说了,直接上代码
delimiter //
create trigger Update_Sundriedbrick after insert on ry_information
for each row
begin
declare totalnumber int;
declare restnumber int;
declare createtime char(20);
declare flage int;
set flage =1;
set flage =(select count(*) from sundriedbrick where SB_CreateTime=createtime and B_IdNumber = new.B_Id);
set createtime = concat(extract(year_month from NOW()),extract(DAY from NOW())) ;
if flage=1 then
select SB_RestNumber ,SB_TotalNumber into restnumber, totalnumber from sundriedbrick where SB_CreateTime =createtime and B_IdNumber = new.B_Id;
set restNumber = restNumber+new.B_Total;
set totalNumber = totalNumber+new.B_Total;
update sundriedbrick set SB_RestNumber = flage,SB_TotalNumber =totalnumber where SB_CreateTime =createtime and B_IdNumber = new.B_Id;
else
insert into sundriedbrick values(concat("SB",createtime),new.B_Id,new.B_Name,createtime,'admin','wangxin',new.B_Total,new.B_Total,createtime) ;
#rollback;
end if;
end ;//
红色标注的就是要判断的我们的值是否存在sundriedbrick中,如果按照单线程的执行,那个flage肯定要先被赋值了,然后才会进行判断,但是现在select 还没赋值完就直接执行到下面的if语句了,因此我把这个改动为:
if (select count(*) from sundriedbrick where SB_CreateTime=createtime and B_IdNumber = new.B_Id)=1
然后就能正常执行,我从这推断可能是这个多线程引起的,其他的因素我也没有细细探究