一个表在插入之前我需要先验证一下数据是否重复
重复则不插入我自己写了一个,但是不对CREATE TRIGGER `fishingport`.`tempinfo_insert`
BEFORE INSERT on `fishingport`.`tempinfo`
FOR EACH ROW BEGIN
begin @num := select count(*) from tempinfo where boatid=NEW.boatid and readid=NEW.readid and readtime<(NEW.readtime + interval 10 minute);
if(num>0)
begin
insert into tempinfo values(null,NEW.boatid,NEW.readid,NEW.readtime,0);
end
end
END$$
DELIMITER ;解释一下:这张表里有字段tempid(自增长),boatid,readid,readtime,isread(int)
我现在需要在插入数据前对比是否有同boatid,readid的10分钟之内的数据,有就不执行没用过mysql的触发器,网上的例子也不是很清楚
望高手指点
重复则不插入我自己写了一个,但是不对CREATE TRIGGER `fishingport`.`tempinfo_insert`
BEFORE INSERT on `fishingport`.`tempinfo`
FOR EACH ROW BEGIN
begin @num := select count(*) from tempinfo where boatid=NEW.boatid and readid=NEW.readid and readtime<(NEW.readtime + interval 10 minute);
if(num>0)
begin
insert into tempinfo values(null,NEW.boatid,NEW.readid,NEW.readtime,0);
end
end
END$$
DELIMITER ;解释一下:这张表里有字段tempid(自增长),boatid,readid,readtime,isread(int)
我现在需要在插入数据前对比是否有同boatid,readid的10分钟之内的数据,有就不执行没用过mysql的触发器,网上的例子也不是很清楚
望高手指点
BEFORE INSERT on `fishingport`.`tempinfo`
FOR EACH ROW
BEGIN
if exists(select 1 from tempinfo where boatid=NEW.boatid and readid=NEW.readid and readtime<(NEW.readtime + interval 10 minute));
begin
insert into tempinfo values(null,NEW.boatid,NEW.readid,NEW.readtime,0);
end
END
BEFORE INSERT on `fishingport`.`tempinfo`
FOR EACH ROW BEGIN
begin @num := select count(*) from tempinfo where boatid=NEW.boatid and readid=NEW.readid and readtime<(NEW.readtime + interval 10 minute);
if(num>0) then insert into tempinfo values(null,NEW.boatid,NEW.readid,NEW.readtime,0);
end if;
end
END$$
DELIMITER ;
FOR EACH ROW BEGIN
DECLARE num INTEGER;
select count(*) into num from fishporting where boatid=NEW.boatid and readid=NEW.readid and readtime<(NEW.readtime + interval 10 minute);
if num>0 then
insert into fishporting(boatid,readid,readtime,isread) values(NEW.boatid, NEW.readid, NEW.readtime,0);
end if;
END$$DELIMITER ;