项目修改客商分公司客商助记码自动更新trigger

create or replace trigger updCust
  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;

begin
  /*判断目标公司数据是否存在*/

  /*判断目标公司数据是否存在*/
  /* dbms_output.put_line('aaa');
  select pk_cubasdoc into pk_bas from bd_cubasdoc1\*目标数据库表*\ where pk_cubasdoc=:new.pk_cubasdoc and nvl(dr,0)=0;*/
  --   dbms_output.put_line('aaa');
  open basjob(:new.pk_cubasdoc);

  loop

    fetch basjob
      into pk_bas;

    exit when basjob%notfound;

  end loop;
  close basjob;
  dbms_output.put_line(:new.pk_cubasdoc);
  if pk_bas is not null then
    begin
      update ncv5.bd_cubasdoc/*目标数据库表*/
         set pk_cubasdoc       = :new.pk_cubasdoc,
             pk_corp           = :new.pk_corp,
             custcode          = :new.custcode,
             custname          = :new.custname,
             custshortname     = :new.custshortname,
             engname           = :new.engname,
             mnecode           = :new.mnecode,
             trade             = :new.trade,
             freecustflag      = :new.freecustflag,
             drpnodeflag       = :new.drpnodeflag,
             isconnflag        = :new.isconnflag,
             pk_cubasdoc1      = :new.pk_cubasdoc1,
             custprop          = :new.custprop,
             pk_areacl         = :new.pk_areacl,
             pk_corp1          = :new.pk_corp1,
             taxpayerid        = :new.taxpayerid,
             legalbody         = :new.legalbody,
             creditmny         = :new.creditmny,
             ecotypesincevfive = :new.ecotypesincevfive,
             saleaddr          = :new.saleaddr,
             conaddr           = :new.conaddr,
             zipcode           = :new.zipcode,
             phone1            = :new.phone1,
             phone2            = :new.phone2,
             phone3            = :new.phone3,
             fax1              = :new.fax1,
             fax2              = :new.fax2,
             linkman1          = :new.linkman1,
             linkman2          = :new.linkman2,
             linkman3          = :new.linkman3,
             bp1               = :new.bp1,
             bp2               = :new.bp2,
             bp3               = :new.bp3,
             mobilephone1      = :new.mobilephone1,
             mobilephone2      = :new.mobilephone2,
             mobilephone3      = :new.mobilephone3,
             email             = :new.email,
             url               = :new.url,
             def1              = :new.def1,
             def2              = :new.def2,
             def3              = :new.def3,
             def4              = :new.def4,
             def5              = :new.def5,
             def6              = :new.def6,
             def7              = :new.def7,
             def8              = :new.def8,
             def9              = :new.def9,
             def10             = :new.def10,
             def11             = :new.def11,
             def12             = :new.def12,
             def13             = :new.def13,
             def14             = :new.def14,
             def15             = :new.def15,
             def16             = :new.def16,
             def17             = :new.def17,
             def18             = :new.def18,
             def19             = :new.def19,
             def20             = :new.def20,
             registerfund      = :new.registerfund,
             sealflag          = :new.sealflag,
             memo              = :new.memo,
             pk_pricegroup     = :new.pk_pricegroup,
             correspondunit    = :new.correspondunit,
             ts                = :new.ts,
             dr                = :new.dr
       where pk_cubasdoc = :new.pk_cubasdoc;
       update ncv5.bd_cumandoc set
       cmnecode= :new.mnecode
       where pk_cubasdoc = :new.pk_cubasdoc;
    end;

  end if;
end updCust;