写的触发器已经生效,可是没有执行begin之后的错误提示

[复制链接]
查看11 | 回复7 | 2011-11-1 16:26:29 | 显示全部楼层 |阅读模式
本帖最后由 isumsen 于 2011-11-27 23:55 编辑
我写的这个触发器是执行已经付过款的客商账户删除给出错误提示,不让删除的。如下:
create or replace trigger delbd_custbank3
before delete on bd_custbank
for each row
declare
-- local variables here
pk_bas char(20);
cursor basjob(id char) is
select pk_custbank
from ncv5.bd_custbank /*目标数据库表*/
where pk_custbank = id
and nvl(dr, 0) = 0
and pk_custbank in
(select bd_custbank.pk_custbank from ncv5.arap_djfb, ncv5.bd_accbank,ncv5.bd_custbank
where bd_accbank.pk_accbank = arap_djfb.skyhzh
andbd_accbank.pk_accbank=bd_custbank.pk_accbank);
begin
/*判断数据是否在目标数据库存在*/
open basjob(: old.pk_custbank);
loop
fetch basjob
into pk_bas;
exit when basjob%notfound;
end loop;
close basjob;
/******************************/
if pk_bas is not null then
begin
raise_application_error(-20001, '已从网银付款,不能修改名称,请取消!');
end;
end if;
end delbd_custbank3;

现在这个触发器已经生效了,在删除付过款的银行账号之后,点击保存失效,只有取消。
可是奇怪的是,raise_application_error(-20001, '已从网银付款,不能修改名称,请取消!');
没有执行,保存是时候,没有任何的错误提示。
另外,我把raise_application_error(-20001, '已从网银付款,不能修改名称,请取消!')放在begin之上,触发器就失效了。
请高手帮助看下

回复

使用道具 举报

千问 | 2011-11-1 16:26:29 | 显示全部楼层
不是这么写的,
EXCEPTION
WHEN OTHERS THEN
begin
v_value := sqlcode;
errormsg:=SUBSTR(SQLERRM,1,200);
RAISE_APPLICATION_ERROR(-20900, 'getError' || SQLCODE);
end;
回复

使用道具 举报

千问 | 2011-11-1 16:26:29 | 显示全部楼层
ziling2011 发表于 2011-11-28 08:44
不是这么写的,
EXCEPTION
WHEN OTHERS THEN

不知道你是不是回复我帖子的
回复

使用道具 举报

千问 | 2011-11-1 16:26:29 | 显示全部楼层
begin
open basjob(: old.pk_custbank);
loop
fetch basjob
into pk_bas;
exit when basjob%notfound;
if pk_bas is not null then
begin
raise_application_error(-20001, '已从网银付款,不能修改名称,请取消!');
end;
end if;
end loop;
close basjob;
end delbd_custbank3;

回复

使用道具 举报

千问 | 2011-11-1 16:26:29 | 显示全部楼层
fengjin821 发表于 2011-11-28 13:30
begin
open basjob(: old.pk_custbank);
loop

谢谢,我执行了,效果和我上面的一样,但是不能删行保存,但是仍没有错误提示。
怀疑是用友NC这个地方代码写死
回复

使用道具 举报

千问 | 2011-11-1 16:26:29 | 显示全部楼层
写的这个触发器就有错误提示:
create or replace trigger UPDCUSTNET
before update on bd_cubasdoc
for each row
declare
-- local variables here
pk_bas char(20);
pk_custname varchar2(200);
/*查询目标公司是否存在基本档案*/
cursor basjob(id char) is
select pk_cubasdoc,custname
from ncv5.bd_cubasdoc /*目标数据库表*/
where pk_cubasdoc = id
and nvl(dr, 0) = 0
andpk_cubasdocin
(select bd_cubasdoc.pk_cubasdoc from ncv5.bd_cubasdoc,ncv5.arap_djfb,ncv5.bd_cumandoc
where arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc
and bd_cumandoc.pk_cubasdoc = bd_cubasdoc.pk_cubasdoc);
begin
open basjob(:new.pk_cubasdoc);
loop
fetch basjob
into pk_bas,pk_custname;
exit when basjob%notfound;
end loop;
close basjob;

if pk_bas is not nullthen
if pk_custname:new.custname then
begin
raise_application_error(-20001,pk_custname||'已从网银付款,不能修改名称,请取消!');
end;
end if;
end if;
end UPDCUSTNET;


未命名.jpg (69.01 KB, 下载次数: 2)
下载附件
2011-11-29 09:05 上传



回复

使用道具 举报

千问 | 2011-11-1 16:26:29 | 显示全部楼层
fengjin821 发表于 2011-11-28 13:30
begin
open basjob(: old.pk_custbank);
loop

请问一下,你把错误提示放在了loop里面,我原始的语句是放在了loop外面,那个效率高?
回复

使用道具 举报

千问 | 2011-11-1 16:26:29 | 显示全部楼层
这是我查询到的操作删行时的debug后台代码:
[AWT-EventQueue-2] DEBUG - executeBatch sql: select distinct areaclcode,areaclname,pk_areacl,pk_fatherarea,mnecode from bd_areaclwhere 11=11and (pk_areacl in (select distinct power.resource_data_id from sm_power_areaclass power,sm_user u,sm_user_role urole where u.cuserid='0001X110000000000XZ7' and u.cuserid=urole.cuseridand urole.pk_corp='0001' and urole.pk_role=power.pk_role and power.orgtypecode=1 and (power.pk_org='0001' or iscommon_power in('y','Y')))) and (pk_areacl='0001X110000000000ZLK' )order by areaclcode
[AWT-EventQueue-2] DEBUG - executeBatch sql: select distinct doccode,docname,pk_defdoc,pk_defdoc1 from bd_defdocwhere 11=11and (sealflag is null or sealflag 'Y')and (pk_defdoc='ORGCORP00000000A0132' )order by doccode
[AWT-EventQueue-2] DEBUG - enter method: nc.itf.uap.busibean.IRef.queryMainBatch([Ljava.lang.String


[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.busibean.IRef.queryMainBatch write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.busibean.IRef.queryMainBatch read result from server spend time: 16
[AWT-EventQueue-2] DEBUG - leave method: nc.itf.uap.busibean.IRef.queryMainBatch([Ljava.lang.String

, spend time: 16
[AWT-EventQueue-2] INFO - ExecBatchRefSetPk taken time:16ms.
[AWT-EventQueue-2] INFO - 单据模板加载业务数据所用时间:16 ms
[AWT-EventQueue-2] DEBUG - 开始解析公式:[copycode->custcode, copycode1->custcode, copyname->custname, copyname1->custname, tradename->getColValue(bd_defdoc,docname,pk_defdoc,trade), custbas_code->getColValue(bd_cubasdoc,custcode,pk_cubasdoc,pk_cubasdoc1), corp_code->getColValue(bd_corp,unitcode,pk_corp,pk_corp1), unitname-> getColValue(bd_settleunit,settleunitname,pk_settleunit,correspondunit), areacl_name->getColValue(bd_areacl,areaclname,pk_areacl,pk_areacl), ecotype_name->getColValue(bd_defdoc,docname,pk_defdoc,ecotypesincevfive), pricegroupname->getColValue(bd_defdoc,docname,pk_defdoc,pk_pricegroup)]
[AWT-EventQueue-2] DEBUG - 开始拆分解析公式....
[AWT-EventQueue-2] DEBUG - 解析公式结束!
[AWT-EventQueue-2] DEBUG - 开始从公式中取得变量...
[AWT-EventQueue-2] DEBUG - 返回公式中的变量!
[AWT-EventQueue-2] DEBUG - 开始从公式中取得变量...
[AWT-EventQueue-2] DEBUG - 返回公式中的变量!
[AWT-EventQueue-2] DEBUG - 开始取公式的值...
[AWT-EventQueue-2] DEBUG - 公式[custcode]执行结果为:[02002101009]
[AWT-EventQueue-2] DEBUG - 公式[custcode]执行结果为:[02002101009]
[AWT-EventQueue-2] DEBUG - 公式[custname]执行结果为:[上海歆瑞装饰材料有限公司]
[AWT-EventQueue-2] DEBUG - 公式[custname]执行结果为:[上海歆瑞装饰材料有限公司]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[null]
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_defdoc,docname,pk_defdoc,trade)]执行结果为:[null]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[null]
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_cubasdoc,custcode,pk_cubasdoc,pk_cubasdoc1)]执行结果为:[null]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[null]
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_corp,unitcode,pk_corp,pk_corp1)]执行结果为:[null]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[null]
[AWT-EventQueue-2] DEBUG - 公式[ getColValue(bd_settleunit,settleunitname,pk_settleunit,correspondunit)]执行结果为:[null]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[0001X110000000000ZLK]
调试: 公司数据没下载,开始从后台查询公式SELECT
bd_areacl.areaclname
FROM
bd_areacl
WHERE
bd_areacl.pk_areacl = ?
[AWT-EventQueue-2] DEBUG - enter method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object


[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues read result from server spend time: 0
[AWT-EventQueue-2] DEBUG - leave method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object

, spend time: 0
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_areacl,areaclname,pk_areacl,pk_areacl)]执行结果为:[上海地材类供应商名录]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[ORGCORP00000000A0132]
调试: 公司数据没下载,开始从后台查询公式SELECT
bd_defdoc.docname
FROM
bd_defdoc
WHERE
bd_defdoc.pk_defdoc = ?
[AWT-EventQueue-2] DEBUG - enter method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object


[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues read result from server spend time: 16
[AWT-EventQueue-2] DEBUG - leave method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object

, spend time: 16
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_defdoc,docname,pk_defdoc,ecotypesincevfive)]执行结果为:[有限责任(公司)]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[null]
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_defdoc,docname,pk_defdoc,pk_pricegroup)]执行结果为:[null]
[AWT-EventQueue-2] DEBUG - 还原公式信息...
[AWT-EventQueue-2] DEBUG - 开始解析公式:[account->getColValue(bd_accbank ,bankacc,pk_accbank,pk_accbank), accname->getColValue(bd_accbank ,bankname,pk_accbank,pk_accbank), accaddr->getColValue(bd_accbank ,address,pk_accbank,pk_accbank), memo->getColValue(bd_accbank ,memo,pk_accbank,pk_accbank), pk_currtype->getColValue(bd_accbank ,pk_currtype,pk_accbank,pk_accbank), currtypecode->getColValue(bd_currtype ,currtypecode ,pk_currtype ,pk_currtype ), currtypename->getColValue(bd_currtype ,currtypename ,pk_currtype ,pk_currtype )]
[AWT-EventQueue-2] DEBUG - 开始拆分解析公式....
[AWT-EventQueue-2] DEBUG - 解析公式结束!
[AWT-EventQueue-2] DEBUG - 开始从公式中取得变量...
[AWT-EventQueue-2] DEBUG - 返回公式中的变量!
[AWT-EventQueue-2] DEBUG - 开始从公式中取得变量...
[AWT-EventQueue-2] DEBUG - 返回公式中的变量!
[AWT-EventQueue-2] DEBUG - 开始取公式的值...
[AWT-EventQueue-2] DEBUG - getColValue参数值=[0001X1100000000037BN]
调试: 公司数据没下载,开始从后台查询公式SELECT
bd_accbank.bankacc
FROM
bd_accbank
WHERE
bd_accbank.pk_accbank = ?
[AWT-EventQueue-2] DEBUG - enter method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object


[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues read result from server spend time: 0
[AWT-EventQueue-2] DEBUG - leave method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object

, spend time: 0
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_accbank ,bankacc,pk_accbank,pk_accbank)]执行结果为:[32779508010106493]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[0001X1100000000037BN]
调试: 公司数据没下载,开始从后台查询公式SELECT
bd_accbank.bankname
FROM
bd_accbank
WHERE
bd_accbank.pk_accbank = ?
[AWT-EventQueue-2] DEBUG - enter method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object


[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues read result from server spend time: 15
[AWT-EventQueue-2] DEBUG - leave method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object

, spend time: 15
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_accbank ,bankname,pk_accbank,pk_accbank)]执行结果为:[上海农村商业银行九亭支行]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[0001X1100000000037BN]
调试: 公司数据没下载,开始从后台查询公式SELECT
bd_accbank.address
FROM
bd_accbank
WHERE
bd_accbank.pk_accbank = ?
[AWT-EventQueue-2] DEBUG - enter method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object;)
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues read result from server spend time: 0
[AWT-EventQueue-2] DEBUG - leave method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object;), spend time: 0
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_accbank ,address,pk_accbank,pk_accbank)]执行结果为:[null]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[0001X1100000000037BN]
调试: 公司数据没下载,开始从后台查询公式SELECT
bd_accbank.memo
FROM
bd_accbank
WHERE
bd_accbank.pk_accbank = ?
[AWT-EventQueue-2] DEBUG - enter method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object;)
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues read result from server spend time: 16
[AWT-EventQueue-2] DEBUG - leave method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object;), spend time: 16
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_accbank ,memo,pk_accbank,pk_accbank)]执行结果为:[null]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[0001X1100000000037BN]
调试: 公司数据没下载,开始从后台查询公式SELECT
bd_accbank.pk_currtype
FROM
bd_accbank
WHERE
bd_accbank.pk_accbank = ?
[AWT-EventQueue-2] DEBUG - enter method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object;)
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues read result from server spend time: 0
[AWT-EventQueue-2] DEBUG - leave method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object;), spend time: 0
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_accbank ,pk_currtype,pk_accbank,pk_accbank)]执行结果为:[00010000000000000001]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[00010000000000000001]
调试: 公司数据没下载,开始从后台查询公式SELECT
bd_currtype.currtypecode
FROM
bd_currtype
WHERE
bd_currtype.pk_currtype = ?
[AWT-EventQueue-2] DEBUG - enter method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object;)
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues read result from server spend time: 0
[AWT-EventQueue-2] DEBUG - leave method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object;), spend time: 0
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_currtype ,currtypecode ,pk_currtype ,pk_currtype )]执行结果为:[CNY]
[AWT-EventQueue-2] DEBUG - getColValue参数值=[00010000000000000001]
调试: 公司数据没下载,开始从后台查询公式SELECT
bd_currtype.currtypename
FROM
bd_currtype
WHERE
bd_currtype.pk_currtype = ?
[AWT-EventQueue-2] DEBUG - enter method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object;)
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.mw.MwBizEJB.findColumnValues read result from server spend time: 16
[AWT-EventQueue-2] DEBUG - leave method: nc.bs.dbcache.intf.IDBCacheBS.findColumnValues(nc.vo.dbcache.query.QueryClause,[Ljava.lang.Object;), spend time: 16
[AWT-EventQueue-2] DEBUG - 公式[getColValue(bd_currtype ,currtypename ,pk_currtype ,pk_currtype )]执行结果为:[人民币]
[AWT-EventQueue-2] DEBUG - 还原公式信息...
[AWT-EventQueue-2] DEBUG - enter method: nc.itf.uap.bd.cust.ICuBasDocTmp.update(nc.vo.bd.b08.CubasdocVO)
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.bd.BusiScmEJB.update write info to server spend time: 0
[AWT-EventQueue-2] DEBUG - Invoke nc.itf.uap.bd.BusiScmEJB.update read result from server spend time: 31
[AWT-EventQueue-2] DEBUG - leave method: nc.itf.uap.bd.cust.ICuBasDocTmp.update(nc.vo.bd.b08.CubasdocVO), spend time: 31
[AWT-EventQueue-2] ERROR - ORA-20001: 已从网银付款,不能修改名称,请取消!
ORA-06512: 在 "XMV502.DELBD_CUSTBANKNET", line 26
ORA-04088: 触发器 'XMV502.DELBD_CUSTBANKNET' 执行过程中出错
java.lang.RuntimeException: ORA-20001: 已从网银付款,不能修改名称,请取消!
ORA-06512: 在 "XMV502.DELBD_CUSTBANKNET", line 26
ORA-04088: 触发器 'XMV502.DELBD_CUSTBANKNET' 执行过程中出错
at nc.impl.uap.bd.cust.CustBasDocDAO.update(CustBasDocDAO.java:1375)
at nc.impl.uap.bd.cust.CubasdocImpl.update(CubasdocImpl.java:104)
at nc.impl.uap.bd.BusiScmEJB.update(BusiScmEJB.java:595)
at nc.itf.uap.bd.BusiScmEJBEjbBean.update(BusiScmEJBEjbBean.java:348)
at nc.itf.uap.bd.BusiScmEJB_Local.update(BusiScmEJB_Local.java:2332)
at sun.reflect.GeneratedMethodAccessor4848.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at nc.bs.framework.server.AbstractEJBToBServiceHandler.invoke(AbstractEJBToBServiceHandler.java:79)
at nc.bs.framework.server.AbstractEJBToBServiceHandler.invoke(AbstractEJBToBServiceHandler.java:54)
at $Proxy26.update(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at nc.bs.framework.comn.serv.ServiceDispatcher.invokeBeanMethod(ServiceDispatcher.java:320)
at nc.bs.framework.comn.serv.ServiceDispatcher.execCall(ServiceDispatcher.java:129)
at nc.bs.framework.comn.serv.CommonServletDispatcher.doGet(CommonServletDispatcher.java:76)
at nc.bs.framework.comn.serv.CommonServletDispatcher.doPost(CommonServletDispatcher.java:95)
可以看出已经生效了,只是不明白为什么不弹出错误框
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

主题

0

回帖

4882万

积分

论坛元老

Rank: 8Rank: 8

积分
48824836
热门排行