1，ADDCSUTSAME
create or replace trigger ADDCSUTSAME before insert on bd_cubasdoc for each row declare -- local variables here pk_bas varchar2(200); cursor basjob(id varchar2)is select custname from bd_cubasdoc where custname=id and nvl(dr,0)=0 /*and custname in (select custname from bd_cubasdoc) and length(custname)>3*/; begin open basjob(:new.custname) ; 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,pk_bas||'客商名称重复，请直接修改已存在客商增行！'); end; end if; end ADDCSUTSAME;
优化v
create or replace trigger ADDC3 before insert on bd_cubasdoc for each row declare -- local variables here i number(1); begin select count(1)into i from bd_cubasdoc where custname=:new.custname or (engname=:new.engname and length(:new.engname)>3) or (taxpayerid=:new.taxpayerid and length(:new.taxpayerid)>3); if i>0 or regexp_like(:new.custname,'[[:space:]]') then begin raise_application_error(-20001,'客商已存在或有空格，请在已有客商修改增行！'); end; end if; end;
2，ADDCUST1A
create or replace trigger ADDCUST1A before INSERT or UPDATE on BD_CUBASDOC for each row declare -- local variables here begin IF regexp_like(:new.custname,'[[:xdigit:]]') or regexp_like(:new.custname,'[[:space:]]') then raise_application_error(-20001,'注意：客商不能重复，请在原有客商增行！'); end if; end ;
3，CSpcace_ACCBANK
create or replace trigger CSpcace_ACCBANK before insert or update on bd_accbank for each row declare -- local variables here pk_bas bd_accbank.pk_accbank%type; cursor basjob(id varchar2) is select pk_accbank from ncv5.bd_cubasdoc,ncv5.arap_djfb,ncv5.bd_cumandoc,ncv5.bd_accbank where ncv5.arap_djfb.ksbm_cl = ncv5.bd_cumandoc.pk_cumandoc and ncv5.bd_cumandoc.pk_cubasdoc = ncv5.bd_cubasdoc.pk_cubasdoc and ncv5.bd_accbank.pk_accbank = ncv5.arap_djfb.skyhzh and ncv5.arap_djfb.payflag in ('1', '2') and pk_accbank=id; begin open basjob(:new.pk_accbank); loop fetch basjob into pk_bas; exit when basjob%notfound; end loop; close basjob; if regexp_like(:new.bankacc,'[[:space:]]') or regexp_like(:new.combineaccnum,'[[:space:]]') or regexp_like(:new.unitname,'[[:space:]]') or pk_bas is not null then raise_application_error(-20001,'银行账号、联行号、单位名称中有空格或账号已从网银成功付款！'); end if; end;
优化v-1
create or replace trigger C_ACCBANK_SPACE before insert or update on bd_accbank for each row declare -- local variables here begin if regexp_like(:new.bankacc,'[[:space:]]') or regexp_like(:new.combineaccnum,'[[:space:]]') or regexp_like(:new.unitname,'[[:space:]]') then raise_application_error(-20001,'注意：银行账号、联行号或单位名称中有空格！'); end if; end ;
优化v-2
create or replace trigger C_ACCBANK_NET before UPDATE on BD_ACCBANK for each row declare -- local variables here pk_bas bd_accbank.pk_accbank%type; cursor basjob(id varchar2) is select pk_accbank from ncv5.bd_cubasdoc,ncv5.arap_djfb,ncv5.bd_cumandoc,ncv5.bd_accbank where ncv5.arap_djfb.ksbm_cl = ncv5.bd_cumandoc.pk_cumandoc and ncv5.bd_cumandoc.pk_cubasdoc = ncv5.bd_cubasdoc.pk_cubasdoc and ncv5.bd_accbank.pk_accbank = ncv5.arap_djfb.skyhzh and ncv5.arap_djfb.payflag in ('1', '2') and pk_accbank=id; begin open basjob(:new.pk_accbank); loop fetch basjob into pk_bas; exit when basjob%notfound; end loop; close basjob; if pk_bas is not null then /* if :new.bankacc<>:old.bankacc or :new.unitname<>:old.unitname*/ if utl_match.edit_distance_similarity(:old.bankacc,:new.bankacc)<'90' or utl_match.edit_distance_similarity(:old.unitname,:new.unitname)<'70' then raise_application_error(-20001,'注意：账户已成功付款，不能修改账号或单位名称！'); end if; end if; end;
4，delbd_custbank
create or replace trigger delbd_custbank 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 not in (select bd_custbank.pk_custbank from ncv5.arap_djfb, ncv5.bd_accbank,ncv5.bd_custbank where bd_accbank.pk_accbank = arap_djfb.skyhzh and bd_accbank.pk_accbank=bd_custbank.pk_accbank); begin /*判断数据是否在目标数据库存在*/ open basjob(:old.pk_custbank); loop fetch basjob into pk_bas; /*dbms_output.put_line('pa_bas:'||pk_bas);*/ exit when basjob%notfound; end loop; close basjob; /******************************/ if pk_bas is not null then begin delete ncv5.bd_custbank /*目标数据库表*/ where pk_custbank = :old.pk_custbank; end; end if; end delbd_custbank;
5，delbd_custbanknet
create or replace trigger delbd_custbanknet before delete or update 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 and bd_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, :old.account||'已从网银付款，不能修改或删行，请取消！'); end; end if; end delbd_custbanknet;
4,5优化v
create or replace trigger S_DELETE_CUSTBANK 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 not in (select bd_custbank.pk_custbank from ncv5.arap_djfb, ncv5.bd_accbank, ncv5.bd_custbank where bd_accbank.pk_accbank = arap_djfb.skyhzh and bd_accbank.pk_accbank = bd_custbank.pk_accbank); begin /*判断数据是否在目标数据库存在*/ open basjob(:old.pk_custbank); loop fetch basjob into pk_bas; /*dbms_output.put_line('pa_bas:'||pk_bas);*/ exit when basjob%notfound; end loop; close basjob; /******************************/ if pk_bas is not null then begin delete ncv5.bd_custbank /*目标数据库表*/ where pk_custbank = :old.pk_custbank; end; else raise_application_error(-20001, :old.account || '已从网银付款，不能修改或删行，请取消！'); end if; end S_DELETE_CUSTBANK;
6，UPDCUSTNET
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 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(:new.pk_cubasdoc); loop fetch basjob into pk_bas,pk_custname; 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 utl_match.edit_distance_similarity(pk_custname,:new.custname)<'50' then begin raise_application_error(-20001,pk_custname||'已从网银付款，且支付状态为‘支付成功’或‘支付中’，不能修改名称，请取消！（若实际支付失败或退回，请联系集团进行更改）'); end; end if; end if; end UPDCUSTNET;
优化v：
create or replace trigger UPDCUSTALL before update on bd_cubasdoc for each row declare netpk number(1); begin --将从网银付款客商写入变量netname select count(distinct pk_cubasdoc) into netpk from ncv5.arap_djfb, ncv5.bd_cumandoc where arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc and bd_cumandoc.pk_cubasdoc = :new.pk_cubasdoc and arap_djfb.payflag in ('1', '2'); --找到已从网银付款客商 if netpk >'0' then if utl_match.edit_distance_similarity(:old.custname, :new.custname) < '70' then raise_application_error(-20001, '已成功付款，不可任意修改'); end if; --没有从网银付款客商 else if utl_match.edit_distance_similarity(:old.custname, :new.custname) < '50' then raise_application_error(-20001, '不可任意修改，请新增客商'); end if; end if; end;