oracle中记录trigger的异常记录
create or replace trigger TIG_BP_MACHINE_DF_BIND before insert on "BP_MACHINE_DATACOLLECTION_DF" for each row declare V_SFC_NO VARCHAR2(50); --电芯 V_RESULT VARCHAR2(50); --结果 begin IF LENGTH(:NEW.JE_CODE)>10 THEN SELECT SFC_NO INTO V_SFC_NO FROM SFC_INFO WHERE PID=:NEW.BATCH_NO; SELECT F_PRODBATCH_TO_SFC(V_SFC_NO,:NEW.JE_CODE,:NEW.CREATE_DATE) INTO V_RESULT FROM DUAL; IF V_RESULT!='成功' THEN P_WriteLog(inserting, updating, '0', V_RESULT||:NEW.BATCH_NO); END IF; END IF; exception when others then P_WriteLog(inserting, updating, sqlcode, sqlerrm); end;
使用 存储过程 P_WriteLog 记录错误信息
P_WriteLog 存储过程:
CREATE OR REPLACE Procedure P_WriteLog( p_Inserting In Boolean , p_updating In Boolean , p_sqlcode In Number , p_sqlerrm In Varchar2) As Begin if p_Inserting then insert into s_error_log (errcode,errmsg,host,errdate,operation) values(p_sqlcode,substr(p_sqlerrm,1,100),userenv('terminal'),sysdate,'inserting'); else if p_updating then insert into s_error_log (errcode,errmsg,host,errdate,operation) values(p_sqlcode,substr(p_sqlerrm,1,100),userenv('terminal'),sysdate,'updating'); else insert into s_error_log (errcode,errmsg,host,errdate,operation) values(p_sqlcode,substr(p_sqlerrm,1,100),userenv('terminal'),sysdate,'deleting'); end if; end if; End;
s_error_log 表:
ERRCODE | NUMBER | 错误代码 |
ERRMSG | VARCHAR2(200) | 错误信息 |
HOST | VARCHAR2(16) | 主机 |
ERRDATE | DATE | 创建日期 |
OPERATION | VARCHAR2(20) | 操作 |