arap_djfb.payflag补充的触发器（付款未成功可以改客商名称）

-1 支付失败
2 支付中
1 支付成功
0或（）空  没有支付
create or replace trigger UPDCUST2
 
  before update on bd_cubasdoc  
 
  for each row
 
declare
 
  -- local variables here
 
    pk_bas char(20);
 
  /*查询目标公司是否存在基本档案*/
 
  cursor basjob(id char) is
 
    select pk_cubasdoc 
 
      from ncv5.bd_cubasdoc /*目标数据库表*/
 
       where pk_cubasdoc = id
 
       and nvl(dr, 0) = 0
 
       and  pk_cubasdoc  in
 
       (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
         and arap_djfb.payflag in ('1', '2'));
 
begin
 
   open basjob(:old.pk_cubasdoc); 
 
  loop
    fetch basjob
      into pk_bas;
 
    exit when basjob%notfound; 
 
  end loop;
 
  close basjob;
 
  --raise_application_error(-20001,:new.pk_cubasdoc);
 
  --raise_application_error(-20001,:new.custname);
 
  --raise_application_error(-20001,pk_custname);
 
  --dbms_output.put_line(:new.pk_cubasdoc);
 
  if pk_bas is not null  then
 
    if  :new.custname<>:old.custname  then
 
    begin
 
      raise_application_error(-20001,:old.custname||'已从网银付款，不能修改名称，请取消！');
 
    end;
    end if;
  end if;
end UPDCUST2;
下面这样是最新可行的：
create or replace trigger UPDCUST2
  before update on bd_cubasdoc
  for each row
declare
  -- local variables here
    pk_bas varchar2(200);

  /*查询目标公司是否存在基本档案*/
  cursor basjob(id varchar2) is
    select custname
      from ncv5.bd_cubasdoc /*目标数据库表*/
       where custname = id
       and nvl(dr, 0) = 0

       and  custname  in

       (select bd_cubasdoc.custname 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
         and arap_djfb.payflag in ('1', '2'));
begin
   open basjob(:old.custname);
  loop

    fetch basjob

      into pk_bas;
    exit when basjob%notfound;
  end loop;
  close basjob;

 
    if pk_bas<>:new.custname  then

     
    begin
      raise_application_error(-20001,:old.custname||'已从网银付款，不能修改名称，请取消！');
    end;

    
  end if;
end UPDCUST2;

转载于:https://www.cnblogs.com/sumsen/archive/2012/05/29/2524826.html