话说系统保存日期不准确。为什么?我也不知道,现在只讲为杜绝而写的触发器,遇到灵异事件,因为没有水看官网,也不知道这是不是已经通报的BUG。
直入主题,有个表aa_tmp_tb
create table AAA_TMP_TB
(
tmpid NUMBER,
inputdate DATE
)
再建一个触发器
create or replace trigger AAA_TMP_TB_fix_date_tri
before insert or update on AAA_TMP_TB
for each row
declare
begin
if :new.INPUTDATE < sysdate - 3650 or :new.INPUTDATE > sysdate + 3650 then
:new.INPUTDATE := sysdate;
end if;
if :old.INPUTDATE <> :new.INPUTDATE then
:new.INPUTDATE := :old.INPUTDATE;
end if;
end AAA_TMP_TB_fix_date_tri;
好戏来了,假设下面的CCC_TB表有记录,inputdate有值
insert into AAA_TMP_TB (tmpid, INPUTDATE)
(select 1, (select max(inputdate) from CCC_TB ) from dual);
结果新插入 AAA_TMP_TB表的记录inputdate没有值
insert into AAA_TMP_TB (EDIS_PURQUERYID, EDIS_INPUTDATE)
(select 2, sysdate from dual);
结果tmpid为2的 AAA_TMP_TB表的记录inputdate有值,是系统日期
最奇怪的还是下面:
insert into AAA_TMP_TB (tmpid, INPUTDATE)
(select 3, (select max(inputdate)+1-1 from CCC_TB ) from dual);
结果tmpid为3的 AAA_TMP_TB表的记录inputdate有值,奇怪并且现时未解,先记录,有时间再研究