Oracle 触发器被删除、今天爽歪歪了！！！

欢迎技术交流。 QQ：138986722    

以前写的一个触发器~~~昨天下午觉得这个触发器没有用了~~~于是就把它停用了~~~但是想了想不"过瘾"。于是又从新打开工具把它drop掉了~~~！于是悲剧便开始了！

    今天早上来公司的时候、同事说、他添加的数据有问题！我想了想、是不是触发器有出什么错了~~~于是便打开Table里面的Trigger一看、什么都木有、触发器找不到了~~~在直接打开Trigger一看、发现我昨天"删除"的那个触发器还在、而不想"删除"的触发器已经被我无情的"drop"掉了！我郁闷、我欲哭无泪。赶紧的找备份、突然发现我备份好挺多的、但是找了半天、硬是没有那个触发器的备份。终于在博客里面找到了一个原始版本的、！心存侥幸的我、开始触发器恢复之旅。然而、在一次次的期望与失望间、我绝望了！

    怎么办、根据老版本从新写一个了：

判断是否数字的Function：

CREATE OR REPLACE FUNCTION ISNUMBER(MyStr VARCHAR2) RETURN NUMBER IS STR VARCHAR2(400); ISNUM NUMBER; NUM NUMBER; BEGIN --返回0，不是数字。1，是数字 ISNUM:=0; STR:=TRIM(MyStr); --如果输入值为空，就返回不是数字。 IF TRIM(STR) IS NOT NULL THEN BEGIN NUM:=TO_NUMBER(STR); ISNUM:=1; EXCEPTION WHEN INVALID_NUMBER THEN NULL; WHEN OTHERS THEN NULL; END; END IF; RETURN ISNUM; END;

 

判断循环日期(季度)的Function：

CREATE OR REPLACE FUNCTION gettimebytime(mytime number) RETURN number IS v_sysTime number ; v_bool number ; v_returnTime number; BEGIN v_sysTime := ceil(to_char(sysdate,'mm')); if v_sysTime = 2 or v_sysTime = 5 or v_sysTime = 8 or v_sysTime = 11 then v_bool := 2 ; --为当前季度的第二个月 end if; if v_sysTime = 1 or v_sysTime = 4 or v_sysTime = 7 or v_sysTime = 10 then v_bool := 1 ; --为当前季度的第一个月 end if; if v_sysTime = 3 or v_sysTime = 6 or v_sysTime = 9 or v_sysTime = 12 then v_bool := 3; --为当前季度的第三个月 end if; if v_bool = mytime then v_returnTime := v_sysTime; end if; if v_bool < mytime then v_returnTime := mytime - v_bool + v_sysTime; end if; if v_bool > mytime then if (v_bool - mytime) = 1 then v_returnTime := v_sysTime + 2; end if; if (v_bool - mytime) = 2 then v_returnTime := v_sysTime + 1; end if; end if; dbms_output.put_line('月度时间:'||v_returnTime); return v_returnTime ; END;

 

悲剧的触发器：

create or replace trigger biufer_tbmeetmgr_CHANGETIME --after插入之后 --before 插入之前 before insert or update or delete on tbmeetmgr for each row declare TYPE c_time IS REF CURSOR; --创建游离标记 vrec c_time; varInt number ; strOverSql varchar2(30); roomname varchar2(100); roomcnt number; v_usercount number; roomId varchar2(100); --会议室编号 booleanStr varchar2(10); --判断是否数字返回值 v_username varchar2(50); v_stopMonthsTime varchar2(10); begin --调用存储过程 if inserting then booleanStr := isnumber(:NEW.Huiyishi); --返回0为字符串、1为数字 if booleanStr = 1 then roomId := :NEW.Huiyishi; select count(0) into roomcnt from tbmeetroom t where t.id = :NEW.Huiyishi; if roomcnt > 0 then select t.name into roomname from tbmeetroom t where t.id = :NEW.Huiyishi; end if; if roomcnt <= 0 then roomname := :NEW.Huiyishi; end if; end if; if booleanStr = 0 then roomId := 0; roomname := :NEW.Huiyishi; end if; select count(0) into v_usercount from tbsysuserinfo t where t.userid = :NEW.Creator; if v_usercount > 0 then select t.username into v_username from tbsysuserinfo t where t.userid = :NEW.Creator; end if; --一次性会议 if :NEW.huiyimoshi = 1 then insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,roomId,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),:NEW.Stoptime,0,:NEW.ecid, :NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username,:NEW.YITI,:NEW.STATUS); end if; --单周会议 varInt := 0; if :NEW.xunhuaimoshi = 1 then select ceil(( to_date(:NEW.Stoptime,'yyyy-mm-dd') - next_day(to_date(substr(:NEW.Createtime, 1, 10),'yyyy-mm-dd')-1, ceil(substr(:NEW.Xunhuairiqi,3)))+1 )/7) into varInt from dual; OPEN vrec for SELECT to_char(next_day(to_date(substr(:NEW.Createtime,1,10),'yyyy-mm-dd')-1, ceil(substr(:NEW.Xunhuairiqi,3)))+1+(rownum-1)*7 , 'yyyy-MM-dd') from dual connect by rownum<=varInt ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('----+++++单周循环日期++++++-----:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,roomId,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,1,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username,:NEW.YITI,:NEW.STATUS); -- commit; end loop; end if; --每月循环 varInt := 0; if :NEW.xunhuaimoshi = 3 then select ceil(months_between(to_date(:NEW.stoptime,'yyyy-mm-dd'), to_date(substr(:NEW.createtime, 1, 10),'yyyy-mm-dd'))) into varInt from dual; OPEN vrec for SELECT to_char(add_months(to_date((substr(:NEW.createtime,1,8))||(substr(:NEW.Xunhuairiqi,3,4)),'yyyy-MM-dd'),+(rownum-1)), 'yyyy-mm-dd') from dual connect by rownum<= ceil(varInt) ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('月度时间:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,roomId,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,3,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username,:NEW.YITI,:NEW.STATUS); -- commit; end loop; end if; --季度循环 varInt := 0; if :NEW.xunhuaimoshi = 4 then select ceil((to_date(:NEW.stoptime,'yyyy-mm-dd')-to_date(substr(:NEW.createtime, 1, 10),'yyyy-mm-dd'))/90) into varInt from dual; v_stopMonthsTime := gettimebytime(ceil(substr(substr('4-2-28',3,7),0,1))); OPEN vrec for SELECT to_char(add_months(to_date((substr(:NEW.createtime,1,5))||(v_stopMonthsTime||'-'||substr('4-2-28',5,7)),'yyyy-MM-dd'),+(rownum-1)*3), 'yyyy-mm-dd') from dual connect by rownum<= ceil(varInt) ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('月度时间:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,roomId,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,4,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username,:NEW.YITI,:NEW.STATUS); -- commit; end loop; end if; end if; if deleting then delete from tbmeetmgrinfo t where t.mid = :OLD.Id; end if; if updating then delete from tbmeetmgrinfo t where t.mid = :OLD.Id; booleanStr := isnumber(:NEW.Huiyishi); if booleanStr = 1 then roomId := :NEW.Huiyishi; select count(0) into roomcnt from tbmeetroom t where t.id = :NEW.Huiyishi; if roomcnt > 0 then select t.name into roomname from tbmeetroom t where t.id = :NEW.Huiyishi; end if; if roomcnt <= 0 then roomname := :NEW.Huiyishi; end if; end if; if booleanStr = 0 then roomId := 0; roomname := :NEW.Huiyishi; end if; select count(0) into v_usercount from tbsysuserinfo t where t.userid = :NEW.Creator; if v_usercount > 0 then select t.username into v_username from tbsysuserinfo t where t.userid = :NEW.Creator; end if; --一次性会议 if :NEW.huiyimoshi = 1 then insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,roomId,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),:NEW.Stoptime,0,:NEW.ecid, :NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username,:NEW.YITI,:NEW.STATUS); end if; --单周会议 varInt := 0; if :NEW.xunhuaimoshi = 1 then select ceil(( to_date(:NEW.Stoptime,'yyyy-mm-dd') - next_day(to_date(substr(:NEW.Createtime, 1, 10),'yyyy-mm-dd')-1, ceil(substr(:NEW.Xunhuairiqi,3)))+1 )/7) into varInt from dual; OPEN vrec for SELECT to_char(next_day(to_date(substr(:NEW.Createtime,1,10),'yyyy-mm-dd')-1, ceil(substr(:NEW.Xunhuairiqi,3)))+1+(rownum-1)*7 , 'yyyy-MM-dd') from dual connect by rownum<=varInt ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('----+++++单周循环日期++++++-----:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,roomId,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,1,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username,:NEW.YITI,:NEW.STATUS); -- commit; end loop; end if; --每月循环 varInt := 0; if :NEW.xunhuaimoshi = 3 then select ceil(months_between(to_date(:NEW.stoptime,'yyyy-mm-dd'), to_date(substr(:NEW.createtime, 1, 10),'yyyy-mm-dd'))) into varInt from dual; OPEN vrec for SELECT to_char(add_months(to_date((substr(:NEW.createtime,1,8))||(substr(:NEW.Xunhuairiqi,3,4)),'yyyy-MM-dd'),+(rownum-1)), 'yyyy-mm-dd') from dual connect by rownum<= ceil(varInt) ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('月度时间:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,roomId,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,3,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username,:NEW.YITI,:NEW.STATUS); -- commit; end loop; end if; --季度循环 varInt := 0; if :NEW.xunhuaimoshi = 4 then select ceil((to_date(:NEW.stoptime,'yyyy-mm-dd')-to_date(substr(:NEW.createtime, 1, 10),'yyyy-mm-dd'))/90) into varInt from dual; v_stopMonthsTime := gettimebytime(ceil(substr(substr('4-2-28',3,7),0,1))); OPEN vrec for SELECT to_char(add_months(to_date((substr(:NEW.createtime,1,5))||(v_stopMonthsTime||'-'||substr('4-2-28',5,7)),'yyyy-MM-dd'),+(rownum-1)*3), 'yyyy-mm-dd') from dual connect by rownum<= ceil(varInt) ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('月度时间:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,roomId,:NEW.Starttime,:NEW.Endtime,substr(:NEW.Createtime,1,10),strOverSql,4,:NEW.ecid,:NEW.Id,:NEW.TONGZHIFANGSHI,:NEW.TONGZHISHIJIAN,roomname,:NEW.ZHUTI,:NEW.Meetno,:NEW.Creator,v_username,:NEW.YITI,:NEW.STATUS); -- commit; end loop; end if; end if; end;

 

我的电脑也有存档、博客也有、看你娘的还怎么恢复不了！！！

吃一堑长一智！ o(︶︿︶)o 唉！！！ 惨痛的经历！！！