mySql中触发器的问题:当查询语句查询结果为空时出错。错误信息为:
No data - zero rows fetched , selected, or proecssed.有人遇到过吗?
我的触发器如下:
CREATE TRIGGER `temp_after_ins_tr` AFTER INSERT ON `temp`
FOR EACH ROW
BEGIN
set @countNumber = 0;
set @value1 = '0';
set @insertDaytime = '';
Select 1,daytime,(NEW.value1) into @countNumber,@insertDaytime,@value1
From temp
where substationid=NEW.substationid and switchid=NEW.switchid
and sanchu=0 and daytime < NEW.daytime
order by daytime DESC
limit 1;
if @countNumber=1 then
insert into temp2(datetimes,substationid,switchid,value1)
values
(@insertDaytime,NEW.substationid,NEW.switchid,@value1);
end IF;
END;结果在执行
Select 1,daytime,(NEW.value1) into @countNumber,@insertDaytime,@value1
From temp
where substationid=NEW.substationid and switchid=NEW.switchid
and sanchu=0 and daytime < NEW.daytime
order by daytime DESC
limit 1;
这个查询时,如果查询不到数据,则出错了。
请问我要怎么做?
No data - zero rows fetched , selected, or proecssed.有人遇到过吗?
我的触发器如下:
CREATE TRIGGER `temp_after_ins_tr` AFTER INSERT ON `temp`
FOR EACH ROW
BEGIN
set @countNumber = 0;
set @value1 = '0';
set @insertDaytime = '';
Select 1,daytime,(NEW.value1) into @countNumber,@insertDaytime,@value1
From temp
where substationid=NEW.substationid and switchid=NEW.switchid
and sanchu=0 and daytime < NEW.daytime
order by daytime DESC
limit 1;
if @countNumber=1 then
insert into temp2(datetimes,substationid,switchid,value1)
values
(@insertDaytime,NEW.substationid,NEW.switchid,@value1);
end IF;
END;结果在执行
Select 1,daytime,(NEW.value1) into @countNumber,@insertDaytime,@value1
From temp
where substationid=NEW.substationid and switchid=NEW.switchid
and sanchu=0 and daytime < NEW.daytime
order by daytime DESC
limit 1;
这个查询时,如果查询不到数据,则出错了。
请问我要怎么做?
我现在用
Select count(*) into @countNumber From temp
where substationid=NEW.substationid and switchid=NEW.switchid
and sanchu=0 and daytime < NEW.daytime;
先查询出记录数,然后在根据@countNumber 来决定是否做insert操作,问题是解决了。
但我觉得多了一次查询,效率就差了点(我之所以用触发器也就是因为速度太慢了,才考虑用的)。有没有更好的方法,大家?
FOR EACH ROW
BEGIN
set @countNumber = 0;
set @value1 = '0';
set @insertDaytime = '';
Select 1,daytime,(NEW.value1) into @countNumber,@insertDaytime,@value1
From temp
where substationid=NEW.substationid and switchid=NEW.switchid
and sanchu=0 and daytime < NEW.daytime
order by daytime DESC
limit 1;
insert into temp2(datetimes,substationid,switchid,value1)
select @insertDaytime,NEW.substationid,NEW.switchid,@value1
where ifnull(@countNumber,-1)=1;
END;