luxinping1987
luxinping1987
采纳率0%
2018-12-19 14:18 阅读 1.9k

mysql 触发器 有时不执行

我写了一个触发器有时不执行
CREATE TRIGGER tri_mem_recover_date_add BEFORE INSERT ON ac_flow_dispose_info FOR EACH ROW begin
if(new.recover_date is not null ) THEN
if( UNIX_TIMESTAMP(new.recover_date)>=UNIX_TIMESTAMP(CONCAT(DATE(new.recover_date)," 08:30:00")) and UNIX_TIMESTAMP(new.recover_date)<=UNIX_TIMESTAMP(CONCAT(DATE(new.recover_date)," 17:30:00"))) then
set new.mem_recover_date=DATE_ADD(new.recover_date,INTERVAL -2 HOUR);
elseif(UNIX_TIMESTAMP(new.recover_date)<UNIX_TIMESTAMP(CONCAT(DATE(new.recover_date)," 08:30:00")))then
set new.mem_recover_date=CONCAT(date(DATE_SUB(new.recover_date,INTERVAL 1 DAY)),' 16:00:00');
else
set new.mem_recover_date=CONCAT(date(new.recover_date),' 16:00:00');
end if;
end if;
END;

CREATE TRIGGER tri_mem_recover_date BEFORE UPDATE ON ac_flow_dispose_info FOR EACH ROW begin
if(new.recover_date is not null && old.recover_date!= new.recover_date ) THEN
if( UNIX_TIMESTAMP(new.recover_date)>=UNIX_TIMESTAMP(CONCAT(DATE(new.recover_date)," 08:30:00")) and UNIX_TIMESTAMP(new.recover_date)<=UNIX_TIMESTAMP(CONCAT(DATE(new.recover_date)," 17:30:00"))) then
set new.mem_recover_date=DATE_ADD(new.recover_date,INTERVAL -2 HOUR);
elseif(UNIX_TIMESTAMP(new.recover_date)<UNIX_TIMESTAMP(CONCAT(DATE(new.recover_date)," 08:30:00")))then
set new.mem_recover_date=CONCAT(date(DATE_SUB(new.recover_date,INTERVAL 1 DAY)),' 16:00:00');
else
set new.mem_recover_date=CONCAT(date(new.recover_date),' 16:00:00');
end if;
end if;
END;

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • qq_44181700 Sudden*surprise 2018-12-19 16:30

    好像是mysql触发器的表名问题,可以试一试

    点赞 评论 复制链接分享
  • luxinping1987 luxinping1987 2018-12-20 06:41

    问题找到了 old.recover_date!= new.recover_date 这个datetime类型的比较是不对的,转换为UNIX_TIMESTAMP 比较,同时要注意UNIX_TIMESTAMP(null)的情况要改成UNIX_TIMESTAMP(ifnull(),'')

    点赞 评论 复制链接分享

相关推荐