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个回答

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

luxinping1987
luxinping1987 触发器的名称问题?
一年多之前 回复

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐