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 rowdeclare  -- 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 rowdeclare  -- local variables here  i number(1);begin select count(1)into i from bd_cubasdocwhere 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 rowdeclare  -- 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 rowdeclare   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 rowdeclare  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 rowdeclare  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 rowdeclare  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 rowdeclare  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 rowdeclare  -- local variables here  i number(1);  PRAGMA AUTONOMOUS_TRANSACTION;begin select count(1)into i from bd_cubasdocwhere (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就是强大，不加修改客商不改动点击保存立马报错