复制------------------------------------------------------------------------- -- 存储过程名：P_DTMS_UPDATE_SAP -- 插入数据到T_DATA_DTMS_TAX_EXPORTTOSAP中 -- 自治事务（pragma autonomous_transaction） -- 2012-12-28 ------------------------------------------------------------------------- createorreplaceprocedure P_DTMS_UPDATE_SAP( i_pkvalue in number,自治 i_opcontent_ori in VARCHAR2, i_opcontent_dest in VARCHAR2, i_source in varchar2 ) is pragma autonomous_transaction; begin INSERTINTO T_DATA_DTMS_TAX_EXPORTTOSAP(ID, DTMS_TAX_INVOICE_ID, OPERATE_TYPE, EXPORT_TO_SAP_ORI, EXPORT_TO_SAP_DEST, OPERATE_TIME, SOURCE) VALUES(SEQ_DATA_DTMS_TAX_EXPORTTOSAP.NEXTVAL,i_pkvalue,update,i_opcontent_ori,i_opcontent_dest,sysdate, i_source); commit; end; ------------------------------------------------------------------------- -- 触发器名称：TRG_INVOICE_EXPORTTOSAP_MODIFY -- 当表T_DTMS_TAX_INVOICE做更新操作时触发，用于对EXPORT_TO_SAP标志位做将1改为0时，事务实例抛出异常，讲解回滚修改，自治即：不允许将EXPORT_TO_SAP从1改为0 -- 2012-12-28 ------------------------------------------------------------------------- createorreplacetrigger"TRG_INVOICE_EXPORTTOSAP_MODIFY" afterupdate on T_DTMS_TAX_INVOICE for each row declare v_pkvalue NUMBER(20); v_opcontent_ori VARCHAR2(50);--修改前的事务实例值 v_opcontent_dst VARCHAR2(50);--修改后的企商汇值 begin v_pkvalue := :new.id; casewhen updating then v_opcontent_ori := :old.EXPORT_TO_SAP; v_opcontent_dst := :new.EXPORT_TO_SAP; if v_opcontent_ori = 1 and v_opcontent_dst = 0 then P_DTMS_UPDATE_SAP(v_pkvalue,v_opcontent_ori,v_opcontent_dst,invoice);--自治事务，调用这个过程的讲解时候它就会独立于调用它的父事务进行操作 RAISE_APPLICATION_ERROR(-20100, Cannot Modify T_DTMS_TAX_INVOICE.EXPORT_TO_SAP From 1 To 0.);--抛出异常，RAISE_APPLICATION_ERROR(num,自治msg)，香港云服务器num在-20000到-20999之间，事务实例msg写你希望抛出的讲解异常。自治 end if; endcase; end; 1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42. |
喜欢
讨厌