最近写的触发器，保存一下

最新推荐文章于 2021-02-02 05:55:05 发布

原创最新推荐文章于 2021-02-02 05:55:05 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一段SQL代码，用于动态插入和更新数据库表中的数据，包括计数、查询特定字段值、计算总里程等操作。

begin
    declare m_dynamicInsertGsmNum int default 0;
    declare carNum VARCHAR(10);
    declare dri VARCHAR(20);
    declare today double;
    declare total double;

    select count(*) into m_dynamicInsertGsmNum from m_dynamicinsert where gsmNum = new.gsmNum;
    select car_num into carNum from m_car where gsm_num=new.gsmNum;
    select driver into dri from m_car where gsm_num=new.gsmNum;
    select sum(mileage) into today from m_lnglat where gsmNum = new.gsmNum and GPS_time between UNIX_TIMESTAMP(CAST(CURDATE() AS DATETIME)) and UNIX_TIMESTAMP(now());
    select sum(mileage) into total from m_lnglat where gsmNum = new.gsmNum and GPS_time between UNIX_TIMESTAMP('2000/01/01 00:00:00') and UNIX_TIMESTAMP(now()) ;

    if m_dynamicInsertGsmNum =0 then
        insert into m_dynamicinsert(gsmNum, lng, lat, speed, gpsTime, carNum, driver, todayMileage, totalMileage) values( new.gsmNum, new.lng, new.lat, new.speed, new.GPS_time, carNum, dri, today, total);
    else
        update m_dynamicinsert set gsmNum=new.gsmNum , lng=new.lng , lat=new.lat , speed=new.speed , gpsTime=new.GPS_time, carNum=carNum, driver=dri, todayMileage=today, totalMileage=total where gsmNum= new.gsmNum;
end if;
end