-> 触发器

-> 触发器
2019年7月24日
16:01
利用触发器做数据转储汇总
示例:
BEGIN
IF NOT EXISTS(SELECT 1 from sensordata where devid = new.devid and DATE_FORMAT(time,’%Y-%m-%d %H-%i’) = DATE_FORMAT(new.time,’%Y-%m-%d %H-%i’))
THEN
IF (new.sigcode = ‘B101’)
THEN INSERT INTO sensordata(id,devid,airtemperature,time) VALUES(null,new.devid,ROUND(new.fval,2),new.time);
ELSEIF(new.sigcode = ‘B202’)
THEN INSERT INTO sensordata(id,devid,airmoisture,time) VALUES(null,new.devid,ROUND(new.fval,2),new.time);
ELSEIF(new.sigcode = ‘8101’)
THEN INSERT INTO sensordata(id,devid,soiltemperature,time) VALUES(null,new.devid,ROUND(new.fval,2),new.time);
ELSEIF(new.sigcode = ‘9101’)
THEN INSERT INTO sensordata(id,devid,soilmoisture1,time) VALUES(null,new.devid,ROUND(new.fval,2),new.time);
ELSEIF(new.sigcode = ‘9301’)
THEN INSERT INTO sensordata(id,devid,soilmoisture2,time) VALUES(null,new.devid,ROUND(new.fval,2),new.time);
ELSEIF(new.sigcode = ‘9501’)
THEN INSERT INTO sensordata(id,devid,soilmoisture3,time) VALUES(null,new.devid,ROUND(new.fval,2),new.time);
ELSEIF(new.sigcode = ‘9701’)
THEN INSERT INTO sensordata(id,devid,soilmoisture4,time) VALUES(null,new.devid,ROUND(new.fval,2),new.time);
End IF;
ELSE
IF (new.sigcode = ‘B101’)
THEN UPDATE sensordata SET airtemperature = ROUND(new.fval,2) WHERE devid = new.devid and DATE_FORMAT(time,’%Y-%m-%d %H-%i’) = DATE_FORMAT(new.time,’%Y-%m-%d %H-%i’);
ELSEIF(new.sigcode = ‘B202’)
THEN UPDATE sensordata SET airmoisture = ROUND(new.fval,2) WHERE devid = new.devid and DATE_FORMAT(time,’%Y-%m-%d %H-%i’) = DATE_FORMAT(new.time,’%Y-%m-%d %H-%i’);
ELSEIF(new.sigcode = ‘8101’)
THEN UPDATE sensordata SET soiltemperature = ROUND(new.fval,2) WHERE devid = new.devid and DATE_FORMAT(time,’%Y-%m-%d %H-%i’) = DATE_FORMAT(new.time,’%Y-%m-%d %H-%i’);
ELSEIF(new.sigcode = ‘9101’)
THEN UPDATE sensordata SET soilmoisture1 = ROUND(new.fval,2) WHERE devid = new.devid and DATE_FORMAT(time,’%Y-%m-%d %H-%i’) = DATE_FORMAT(new.time,’%Y-%m-%d %H-%i’);
ELSEIF(new.sigcode = ‘9301’)
THEN UPDATE sensordata SET soilmoisture2 = ROUND(new.fval,2) WHERE devid = new.devid and DATE_FORMAT(time,’%Y-%m-%d %H-%i’) = DATE_FORMAT(new.time,’%Y-%m-%d %H-%i’);
ELSEIF(new.sigcode = ‘9501’)
THEN UPDATE sensordata SET soilmoisture3 = ROUND(new.fval,2) WHERE devid = new.devid and DATE_FORMAT(time,’%Y-%m-%d %H-%i’) = DATE_FORMAT(new.time,’%Y-%m-%d %H-%i’);
ELSEIF(new.sigcode = ‘9701’)
THEN UPDATE sensordata SET soilmoisture4 = ROUND(new.fval,2) WHERE devid = new.devid and DATE_FORMAT(time,’%Y-%m-%d %H-%i’) = DATE_FORMAT(new.time,’%Y-%m-%d %H-%i’);
End IF;
END IF;
END


已使用 Microsoft OneNote 2016 创建。