待研究客商不重复的三个条件trigger|搞定|

create or replace trigger S_ADD_SAME
before insert on bd_cubasdoc
for each row
declare
-- local variables here
s_name bd_cubasdoc.custname%type;
s_engname bd_cubasdoc.engname%type;
s_tax bd_cubasdoc.taxpayerid%type;
begin
select distinct custname,engname,taxpayerid into s_name,s_engname,s_tax
from bd_cubasdoc
where (length(:new.engname)>3 or length(:new.taxpayerid)>3)
and pk_cubasdoc=:new.pk_cubasdoc;
if s_name in(select custname from bd_cubasdoc) or
s_engname in(select engname from bd_cubasdoc) or
s_tax in(select taxpayerid from bd_cubasdoc)
then
raise_application_error(-20001,'客商重复，请查询');
end if;
end S_ADD_SAME;

目前报错：

if in不支持子查询

已有的单据查询客商名称重复的：

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;

8.16更新 感谢一路的简化sql，不过还是不能加上or update：

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;

 

加上 update 报错（道理很简单，update修改的时候当前操作的就是new）

8.16 23:40 备份网银付款客商限制修改trriger

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;

不专门针对网银客商，所有的客商update 的触发器：

create or replace trigger UPDCUSTALL

  before update on bd_cubasdoc

  for each row

declare
 
  begin

    if  utl_match.edit_distance_similarity(:old.custname,:new.custname)<'70' then    

      raise_application_error(-20001,:old.custname||'不可任意修改，请新增客商');    
   
  end if;
end ;

感觉直接这样修改网银客商不靠谱：

create or replace trigger UPDCUSTALL

  before update on bd_cubasdoc

  for each row

declare
  netcustname varchar2(200);
  begin
   select distinct custname into netcustname from ncv5.arap_djfb,ncv5.bd_cumandoc,ncv5.bd_cubasdoc

        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 netcustname is not null then
      if 
       utl_match.edit_distance_similarity(:old.custname,:new.custname)<'80' then    

      raise_application_error(-20001,:old.custname||'已成功付款，不可任意修改');       
  end if;
 
  else
    if  utl_match.edit_distance_similarity(:old.custname,:new.custname)<'50' then
    raise_application_error(-20001,:old.custname||'不可任意修改，请新增客商');  
    end if ; 
    
    end if;
  
end ;

但是目前有报错： 实际返回的行数超出请求的行数

下面的触发器使用select pk_cubasdoc没有上面的问题了，但是没有付款的客商可以任意修改，有问题

create or replace trigger UPDCUSTALL

  before update on bd_cubasdoc

  for each row

declare
  netcustname varchar2(200);
  begin
   select distinct pk_cubasdoc into netcustname 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 netcustname is not null then
      if 
       utl_match.edit_distance_similarity(:old.custname,:new.custname)<'80' 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;
  exception
    when no_data_found then  
    return;
end ;

8.17日11:34更新

感谢itpub t9119的帮助http://www.itpub.net/thread-1707317-1-1.html

他告诉我：

代码缺陷导致的错误。需要修改下。
修改： "select distinct pk_cubasdoc    into netpk    from “
改成:  " select count(distinct pk_cubasdoc)    into netpk    from “
然后判断netpk>0如何处理，netpk =0 如何处理。

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) < '80' 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;

 目前通过一路帮助的insert 和itpub的update两个bd_cubasdoc的触发器解决客商问题

思路：新增客商不可以出现已有客商，修改客商保证幅度小于50%，这样确保客商的唯一。

 8.18 8:18更新：接上 在何涛的联通机房 搞定继续使用字段而非count，这里exception后面加了if

 

create or replace trigger UPDCUSTALL
  before update on bd_cubasdoc
  for each row

declare
  netpk varchar2(60) ;
begin
  --将从网银付款客商写入变量netname
  select  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 is not null then
      
    if utl_match.edit_distance_similarity(:old.custname, :new.custname) < '80' then
      raise_application_error(-20001, '已成功付款，不可任意修改');
    end if;   
 end if;
 
  --没有从网银付款客商
   EXCEPTION
  WHEN NO_DATA_FOUND THEN   
    if utl_match.edit_distance_similarity(:old.custname, :new.custname) < '50' then
      raise_application_error(-20001, '不可任意修改，请新增客商');
    end if; 
end;

 8.20日 更新PRAGMA AUTONOMOUS_TRANSACTION用法，这个放在了变量定义后面，这样就可以使用insert or update了。这里在update的时候，即使什么都不动，:new也等于:old,

这样导致修改客商即使什么都不动保存也保存的问题，解决的办法是在每个条件增加:new.<>:old.

备注：这样其实会和上面的触发器有冲突，并且这个触发器解决不了“是否网银支付”，修改客商名称比例的问题。

create or replace trigger ADDC1
  before insert or update on bd_cubasdoc
  for each row
declare
  -- local variables here
  i number(1);
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
 select count(1)into i from bd_cubasdoc
where (custname=:new.custname and :new.custname<>:old.custname)or
      (engname=:new.engname and length(:new.engname)>3 and :new.engname<>:old.engname) or
      (taxpayerid=:new.taxpayerid and length(:new.taxpayerid)>3 and :new.taxpayerid<>:old.taxpayerid);  
  if i>0
    or
    regexp_like(:new.custname,'[[:space:]]') 
     then
    begin
      raise_application_error(-20001,'客商已存在，银行账号请直接修改增行！');
      end;  
      end if;   
end;

 这里的PRAGMA AUTONOMOUS_TRANSACTION就是强大，不加修改客商不改动点击保存立马报错