Mysql 触发器

最新推荐文章于 2025-08-19 18:46:24 发布

zhu473105308

最新推荐文章于 2025-08-19 18:46:24 发布

阅读量865

点赞数

CC 4.0 BY-SA版权

分类专栏： Mysql 文章标签： mysql date insert each

本文链接：https://blog.youkuaiyun.com/zhu473105308/article/details/7535763

Mysql 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个MySQL触发器的设计与实现细节，该触发器用于在插入新任务后同步更新当日的任务日志表。具体包括：1. 插入新记录到任务流程表时触发；2. 检查并确保每分钟内的任务日志唯一；3. 实现新增任务的日志同步或累计更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE TRIGGER insertToTbdaylogger AFTER INSERT ON tbtaskflow 

FOR EACH ROW

BEGIN
   DECLARE i_temp int;
        select count(*) into i_temp from tbdaylogger where  insertTimes>=DATE_SUB(NOW(),INTERVAL CONCAT("'",MINUTE(NOW()),":",SECOND(NOW()),"'") MINUTE_SECOND)
             and insertTimes < NOW() and taskid=NEW.taskid;
   if i_temp = 0 then
        insert into tbdaylogger(taskid,totalSuccessNum,totalSuccessFlow,totalErrorFlow,totalErrorNum) 
                        values(NEW.taskid,NEW.totalSuccessNum,NEW.totalSuccessFlow,NEW.totalErrorFlow,NEW.totalErrorNum);
   else 
        update tbdaylogger set 
        totalSuccessNum=CAST(totalSuccessNum AS UNSIGNED)+CAST(NEW.totalSuccessNum AS UNSIGNED),
        totalSuccessFlow=CAST(totalSuccessFlow AS UNSIGNED) + CAST(NEW.totalSuccessFlow AS UNSIGNED),
        totalErrorFlow= CAST(totalErrorFlow AS UNSIGNED)+CAST(NEW.totalErrorFlow AS UNSIGNED),
        totalErrorNum=CAST(totalErrorNum AS UNSIGNED)+CAST(NEW.totalErrorNum AS UNSIGNED)
        where insertTimes>=DATE_SUB(NOW(),INTERVAL CONCAT("'",MINUTE(NOW()),":",SECOND(NOW()),"'") MINUTE_SECOND)
             and insertTimes <NOW() and taskid=NEW.taskid;
   end if;
END;

查询当前小时内共有的记录数

   select count(*) into i_temp from tbdaylogger where  insertTimes>=DATE_SUB(NOW(),INTERVAL CONCAT("'",MINUTE(NOW()),":",SECOND(NOW()),"'") MINUTE_SECOND)
             and insertTimes < NOW()

CONCAT(str1,str2)链接字符串

DATE_SUB(NOW(),INTERVAL 1 DAY)

向前查询一天内的时间即为当前时间向前一天

CAST(totalSuccessNum AS UNSIGNED)

转换成数字相加