트리거 사용

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;

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);

END IF;

END$$

lightdata에 데이터가 insert 되었을때 

만약 데이터가 있으면 있는 데이터에 Update를 하고 없으면 Insert 하는 트리거