触发器

 

 

 

 

CREATE DEFINER=`root`@`%` TRIGGER `carXhistory` AFTER UPDATE ON `carXvalue` FOR EACH ROW BEGIN
  set @backup = (select needbackup from carXname where carXname.id = new.Xnameid);
  set @valuetype = (select Valuetype from carXname where carXname.id = new.Xnameid);
  set @unit = (select measurementunit from carXname where carXname.id = new.Xnameid);
  set @name = (select XnameCN from carXname where carXname.id = new.Xnameid);
  set @Xtype = (select Xtype from carXname where carXname.id = new.Xnameid);
  if (@backup = 'Yes' and @valuetype = 'Int' and new.vaule IS NOT NULL and new.updateTime != old.updateTime) then
   insert into carXhistory(Xvalueid,createTime,vaule,Xnameid,carid,measurementunit,XnameCN,Xtype) values (new.id,NOW(),new.val,new.Xnameid,new.carid,@unit,@name,@Xtype);
  end if;
END

CREATE DEFINER=`root`@`%` TRIGGER `tub_carXvalue` BEFORE UPDATE ON `carXvalue` FOR EACH ROW begin
  SET @CNT = (select count(*) from carXname where Xnamecn = '车轮运行时间' and id = new.Xnameid);
  IF @CNT > 0 and old.val != new.val THEN
    SET @LAMBCNT = (select count(*) from wheelusecount where carid = new.carid );
    SET @MAXID = (select max(id) from wheelusecount where carid = new.carid);
    SET @LAMBTYPE = (select val from carXvalue,carXname where carid = new.carid and carXValue.Xnameid
      = carXname.id and carXname.Xnamecn = '车轮型号' );
    IF old.val > new.val + 100 THEN
      insert into wheelusecount(carid,wheeltype,val,updateTime) values (new.carid,@LAMBTYPE,new.val,now());
    ELSE
      IF @LAMBCNT > 0 THEN
        update wheelusecount set val = new.val,updateTime = now(),wheeltype=@LAMBTYPE where id = @MAXID;
      ELSE
        insert into wheelusecount(carid,wheeltype,val,updateTime) values (new.carid,@LAMBTYPE,new.val,now());
      END IF;
    END IF;
  END IF;
end