트리거 사용(기존 변경)

-- Trigger DDL Statements

DELIMITER $$

USE `ilsdb`$$

CREATE

DEFINER=`root`@`localhost`

TRIGGER `ilsdb`.`update_datainfo`

AFTER INSERT ON `ilsdb`.`lightdata`

FOR EACH ROW

BEGIN

IF EXISTS(SELECT llight, lgroup FROM lightinfo WHERE llight=NEW.llight AND lgroup=NEW.lgroup)THEN

UPDATE lightinfo SET llight=NEW.llight, lgroup=NEW.lgroup, lstatus=NEW.lstatus, ldimming=NEW.ldimming, llastsmps1=NEW.lsmps1, llastsmps2=NEW.lsmps2, llastrfstatus=NEW.lrfstatus, llastmpustatus=NEW.lmpustatus, llastsolarpower=NEW.lsolarpower, llasttemperature=NEW.ltemperature, llasthumidity=NEW.lhumidity, llastairflow=NEW.lairflow, llastsunset=NEW.lsunset, llastmist=NEW.lmist

WHERE llight=NEW.llight AND lgroup=NEW.lgroup;

IF NEW.lrfstatus = 0 OR NEW.lmpustatus = 0 THEN

UPDATE groupinfo set errorcheck = 1 WHERE lgroup=NEW.lgroup;

ELSE

UPDATE groupinfo set errorcheck = 0 WHERE lgroup=NEW.lgroup;

END IF;

ELSE

INSERT INTO lightinfo(llight, lgroup, lstatus, ldimming, llastsmps1, llastsmps2, llastrfstatus, llastmpustatus, llastsolarpower, llasttemperature, llasthumidity, llastairflow, llastsunset, llastmist)

VALUES(NEW.llight, NEW.lgroup, NEW.lstatus, NEW.ldimming, NEW.lsmps1, NEW.lsmps2, NEW.lrfstatus, NEW.lmpustatus, NEW.lsolarpower, NEW.ltemperature, NEW.lhumidity, NEW.lairflow, NEW.lsunset, NEW.lmist);

IF NEW.lrfstatus = 0 OR NEW.lmpustatus = 0 THEN

UPDATE groupinfo set errorcheck = 1 WHERE lgroup=NEW.lgroup;

ELSE 

UPDATE groupinfo set errorcheck = 0 WHERE lgroup=NEW.lgroup;

END IF;

END IF;

END$$

lightdata에 데이터가 Insert 되면 lightinfo에 마지막 인서트 된 update 또는 insert 하고 만약 몇가지 데이터가 0또는 1로 들어오면 groupinfo 의 errorcheck 값을 0, 1로 저장하는 트리거