看来需要继续研究结算中心账户客商触发器了|OK||更新if else用法|

最新推荐文章于 2024-11-01 12:20:35 发布

weixin_30443895

最新推荐文章于 2024-11-01 12:20:35 发布

阅读量71

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/sumsen/archive/2012/05/29/2524745.html

版权

itpub和csdn上面的求助原文：

oracle数据库是用在我们公司用友NC财务软件上，NC版本是5.02。
我是公司的NC的系统管理员。
我们使用了资金管理系统，需要使用一个功能是填制结算凭证，如下图：

上图红色方框标示的是需要特别注意的，系统的要求必须“账户”和“客商辅助”必须一致，也就是这里的“账户”是北京公司，那么后面的“客商辅助”也必须选择北京公司，否则会出现账务和系统上的麻烦。
备注：我们公司对同一个分公司，账户和客商是同一个编码，比如北京公司，账户和客商编码都是010201，上海公司都是010301.

我经过查看后台数据库，在填制结算凭证的时候，影响到的是两个表，分包是结算分录fts_voucher_b，结算凭证fts_voucher，这两个表通过字段pk_voucher关联。
上图中的“账户”和“客商辅助”是存在fts_voucher_b中，但是不是直接的代码，比如010201，而是两个pk代码值。
经过几天的研究，我打算通过触发器完成这个账户和客商一致的问题，可是上面也说到，在填制结算凭证，后台存入fts_voucher_b中的是pk值，必须要凭证保存到数据库之后才能查询到不一致的情况。
查询结算分录账户和客商不一致的情况，我sql如下：

select

       bd_accid.accidcode,

       gl_freevalue.valuecode

  from fts_voucher_b, bd_accsubj, gl_freevalue, bd_accid

where fts_voucher_b.pk_subject = bd_accsubj.pk_accsubj

   and fts_voucher_b.pk_ass = gl_freevalue.freevalueid

   and fts_voucher_b.dr = '0'

   and fts_voucher_b.pk_corp = '1162'

   and fts_voucher_b.pk_account = bd_accid.pk_accid

   and length(gl_freevalue.valuecode) = '6'

   and substr(gl_freevalue.valuecode, 0, 1) = '0'

   and bd_accid.accidcode != gl_freevalue.valuecode

复制代码
这里为了将账户和客商的pk值转化为数字代码（上面备注说到的北京公司010201），通过了4个表的关联查询。
之后触发器把上面的代码作为了游标，触发器如下：

create or replace trigger CHECK_VOUCHER

before insert or update on fts_voucher  

  for each row

declare

  -- local variables here

  pk_bas char(20);

  accode char(6);

  vcode char(6);

cursor basjob(id char) is

    select fts_voucher_b.pk_voucher,bd_accid.accidcode, gl_freevalue.valuecode

      from fts_voucher_b,bd_accid, gl_freevalue

     where fts_voucher_b.pk_voucher = id

       and     fts_voucher_b.pk_ass = gl_freevalue.freevalueid

               and fts_voucher_b.pk_account = bd_accid.pk_accid

               and length(gl_freevalue.valuecode) = '6'

               and substr(gl_freevalue.valuecode, 0, 1) = '0';

begin

  open basjob(:new.pk_voucher);

  loop

fetch basjob

      into pk_bas, accode , vcode;

    exit when basjob%notfound;

    --raise_application_error(-20001,pk_accidcode||'hhh');

  end loop;

  close basjob;

  if pk_bas is not null then

    if accode<>vcode then

begin

raise_application_error(-20001,'结算凭证号'||:new.cent_typeid||'账户和客商不一致，请修改！');

    end;

  end if;

  end if;

end CHECK_VOUCHER;

复制代码

解释：触发器是写在了表结算凭证fts_voucher上而不是写在结算分录fts_voucher_b上，因为上面提到凭证没有保存的时候是不会相应游标里面的sql。这里借用了fts_voucher和fts_voucher_b共同有一个同样的值pk_voucher。触发器是在审核结算凭证的时候引起触发（审核的时候fts_voucher会有新值传入）
目前，这个触发器已经完成了如果填制的结算凭证账户和客商不一致引起触发报错不给审核额通过。
但是很快发现了问题，就是审核发现错误之后，让制单人回到填制结算凭证那里修改账户和客商保存一致，保存的时候同意会报错，不能保存。
基本的原因是知道的，在修改结算凭证的时候，pk_voucher并没有变动，错误的结算凭证还在fts_voucher_b里面，系统在保存之前和过程中还是识别的这个没有改的错误的结算凭证，因此即使修改了也是不让保存。
目前我只要让同事删除这个结算凭证，重新制单。

有几点疑问：
1，这个问题还有解决的可能性吗？就是触发器在触发报错之后，制单人可以直接原有基础上修改而不用删除重新制单。
2，可否使用after触发器，让在保存之后报错，这时候触发器直接写在fts_voucher_b上？
3，是不是我的触发器写的不够好，还有其他的优化？
如果方便，可以加我qq344529501，谢谢！

下面是搞定的sql，感谢一位高手

create or replace trigger fts_v_b

  before insert or update on   fts_voucher_b

  for each row

declare

  -- local variables here

  accode char(6);

  vcode char(6);

  vRowsCount number;

begin

select nvl(count(*),0) into vRowsCount from fts_voucher_b, gl_freevalue, bd_accid

             where  gl_freevalue.freevalueid=:new.pk_ass

               and  bd_accid.pk_accid=:new.pk_account

               and length(gl_freevalue.valuecode) = '6'

               and substr(gl_freevalue.valuecode, 0, 1) = '0';

if vRowsCount > 0 then

   select distinct bd_accid.accidcode,  gl_freevalue.valuecode into accode,vcode

   from fts_voucher_b, gl_freevalue, bd_accid

             where  gl_freevalue.freevalueid=:new.pk_ass

               and  bd_accid.pk_accid=:new.pk_account

               and length(gl_freevalue.valuecode) = '6'

               and substr(gl_freevalue.valuecode, 0, 1) = '0';  

else

    return;

end if;

if  accode<>vcode   then

       raise_application_error(-20001,'结算凭证账户'||accode||'和客商'||vcode|| '不一致!');

end if;  

end  fts_v_b;

复制代码

本以为触发器已经完美。

create or replace trigger CHECK_VOUCHER

  before insert or update on fts_voucher  

  for each row

declare

  -- local variables here

  pk_bas char(20);

 

  cursor basjob(id char) is

    select pk_voucher

      from fts_voucher_b

     where pk_voucher = id

       and pk_voucher in

           (select fts_voucher_b.pk_voucher

              from fts_voucher_b, gl_freevalue, bd_accid

             where fts_voucher_b.pk_ass = gl_freevalue.freevalueid

               and fts_voucher_b.pk_account = bd_accid.pk_accid

               and length(gl_freevalue.valuecode) = '6'

               and substr(gl_freevalue.valuecode, 0, 1) = '0'

               and bd_accid.accidcode <> gl_freevalue.valuecode);

 

begin

  open basjob(:new.pk_voucher);

  loop

  

    fetch basjob

      into pk_bas;

    exit when basjob%notfound;

    --raise_application_error(-20001,pk_accidcode||'hhh');

  end loop;

  close basjob;

 

  if pk_bas is not null then

    begin

      raise_application_error(-20001,

                              '结算凭证'|| :new.cent_typeid ||'账户和客商不一致，请删除，之后重新填写结算凭证！');

    end;

  end if;

end CHECK_VOUCHER;

可是今天孙娜填写了一个错误的结算凭证，就是账户和客商不一致，审核的时候报错。
但是孙娜在结算凭证那里改正这个错误的凭证，之后报错却同样的触发了这个trigger。
后来临时的解决办法是删除这个结算凭证，重新手工填写。

基本的原因是知道的，在修改结算凭证的时候，pk_voucher并没有变动，因为之前的结算凭证还在fts_voucher_b里面，系统在保存之前和过程中还是识别的这个没有改的错误的结算凭证，因此即使修改了也是不让保存。
怎么解决呢
但是奇怪啊，我明明写的是before insert or update on fts_voucher ，修改结算凭证是针对fts_voucher_b的，为什么这里会触发呢

4.1 23:13更新：after触发器试一下
4.3 1:34 update:

create or replace trigger CHECK_VOUCHER
 before insert or update on fts_voucher

  for each row
declare

  -- local variables here

  pk_bas char(20);

  accode char(6);
  vcode char(6);

  cursor basjob(id char) is
    select fts_voucher_b.pk_voucher,bd_accid.accidcode, gl_freevalue.valuecode
      from fts_voucher_b,bd_accid, gl_freevalue
     where fts_voucher_b.pk_voucher = id

       and     fts_voucher_b.pk_ass = gl_freevalue.freevalueid
               and fts_voucher_b.pk_account = bd_accid.pk_accid
               and length(gl_freevalue.valuecode) = '6'
               and substr(gl_freevalue.valuecode, 0, 1) = '0';    

 begin
  open basjob(:new.pk_voucher);
  loop
    fetch basjob
      into pk_bas, accode , vcode;

    exit when basjob%notfound;

    --raise_application_error(-20001,pk_accidcode||'hhh');
   end loop;
  close basjob; 

  if pk_bas is not null then

    if accode<>vcode then

    begin

      raise_application_error(-20001,:new.pk_voucher||:old.pk_voucher||'结算凭证号：'||:new.cent_typeid||'的账户'||accode||

      '和客商辅助'||vcode||'不一致，请修改！');

  update fts_voucher set pk_voucher='1162N51000000001'||substr(:new.pk_voucher,17,4)

  where pk_voucher=:old.pk_voucher;

  update fts_voucher_b set pk_voucher='1162N51000000001'||substr(:new.pk_voucher,17,4)

  where pk_voucher=:old.pk_voucher;

    end;
  end if;

  end if;
end CHECK_VOUCHER;

4.6 0:34 更新，明天继续问一路

create or replace trigger fts_v_b

  before insert OR update on   fts_voucher_b

  for each row

 

begin

 select bd_accid.accidcode,  gl_freevalue.valuecode from fts_voucher_b, gl_freevalue, bd_accid

             where :new.pk_ass = gl_freevalue.freevalueid

               and :new.pk_account= bd_accid.pk_accid

               and length(gl_freevalue.valuecode) = '6'

               and substr(gl_freevalue.valuecode, 0, 1) = '0'

  if bd_accid.accidcode<>gl_freevalue.valuecode

   then aise_application_error(-20001,

                              '结算凭证'||'账户和客商不一致，请删除，之后重新填写结算凭证！');

  end;

  end if;

 

end fts_v_b;

更新4.6 13：38

create or replace trigger fts_v_b
  before insert or update on   fts_voucher_b
  for each row 
declare
  -- local variables here
    pk_bas char(20);
 
begin
 select distinct :new.pk_voucher into pk_bas
 from fts_voucher_b, gl_freevalue, bd_accid
             where  gl_freevalue.freevalueid=:new.pk_ass
               and  bd_accid.pk_accid=:new.pk_account
               and length(gl_freevalue.valuecode) = '6'
               and substr(gl_freevalue.valuecode, 0, 1) = '0'
               and bd_accid.accidcode != gl_freevalue.valuecode;   
  if  pk_bas is not null  then 
       raise_application_error(-20001,'结算凭证账户和客商不一致!');
  
  
   
 end if;  
 end  fts_v_b;

第二种：

create or replace trigger fts_v_b
  before insert or update on   fts_voucher_b
  for each row 
declare
  -- local variables here
    accode char(6);
  vcode char(6);
begin
 select distinct bd_accid.accidcode,  gl_freevalue.valuecode into accode,vcode 
 from fts_voucher_b, gl_freevalue, bd_accid
             where  gl_freevalue.freevalueid=:new.pk_ass
               and  bd_accid.pk_accid=:new.pk_account
               and length(gl_freevalue.valuecode)='6'
               and substr(gl_freevalue.valuecode,0,1)='0';   
  if nvl(accode,'')<>nvl(vcode,'')   then 
       raise_application_error(-20001,'结算凭证账户'||accode||'和客商'||vcode|| '不一致!');
 end if;  

 end  fts_v_b;

2012.4.6 14:15更新：

create or replace trigger fts_v_b
  before insert or update on   fts_voucher_b
  for each row 
declare
  -- local variables here
  accode char(6);
  vcode char(6);
  vRowsCount number;
begin
 select nvl(count(*),0) into vRowsCount from fts_voucher_b, gl_freevalue, bd_accid
             where  gl_freevalue.freevalueid=:new.pk_ass
               and  bd_accid.pk_accid=:new.pk_account
               and length(gl_freevalue.valuecode) = '6'
               and substr(gl_freevalue.valuecode, 0, 1) = '0';
 
 if vRowsCount > 0 then
   select distinct bd_accid.accidcode,  gl_freevalue.valuecode into accode,vcode 
   from fts_voucher_b, gl_freevalue, bd_accid
             where  gl_freevalue.freevalueid=:new.pk_ass
               and  bd_accid.pk_accid=:new.pk_account
               and length(gl_freevalue.valuecode) = '6'
               and substr(gl_freevalue.valuecode, 0, 1) = '0';  
 else
    return;
 end if;
 
 if  accode<>vcode   then 
       raise_application_error(-20001,'次结算凭证账户'||accode||'和客商'||vcode|| '不一致，请修改！');
  
 end if;  
 end  fts_v_b;

上面的vRowsCount > 0是判断数据存在的。
感谢把触发器说透的作者，在今天他一个中午的陪伴下，完成了。

4.6 18:28更新，这样也行。在accode<>vocde之前先增加 if accode=vcode (实测前后没有影响)

 1 create or replace trigger FTS2
 2   before insert or update on   fts_voucher_b
 3   for each row
 4 declare
 5   -- local variables here
 6     accode char(6);
 7   vcode char(6);
 8 begin
 9   
10  select distinct bd_accid.accidcode,  gl_freevalue.valuecode into accode,vcode
11  from fts_voucher_b, gl_freevalue, bd_accid
12              where  gl_freevalue.freevalueid=:new.pk_ass
13                and  bd_accid.pk_accid=:new.pk_account
14                and length(gl_freevalue.valuecode)='6'
15                and substr(gl_freevalue.valuecode,0,1)='0';   
16                
17   if accode=vcode then   
18        RETURN;
19        END IF;        
20                
21   if accode<>vcode then
22        raise_application_error(-20001,'此结算凭证账户：'||accode||'和客商辅助：'||vcode|| '不一致，请修改!');
23  end if;  
24 
25  end FTS2;

18:43 更新，使用 else if

create or replace trigger FTS2
before insert or update on   fts_voucher_b
for each row
declare
-- local variables here
    accode char(6);
vcode char(6);
begin

select distinct bd_accid.accidcode, gl_freevalue.valuecode into accode,vcode
from fts_voucher_b, gl_freevalue, bd_accid
             where gl_freevalue.freevalueid=:new.pk_ass
               and bd_accid.pk_accid=:new.pk_account
               and length(gl_freevalue.valuecode)='6'
               and substr(gl_freevalue.valuecode,0,1)='0';

if accode=vcode then
       RETURN;

else if accode<>vcode then
       raise_application_error(-20001,'此结算凭证账户：'||accode||'和客商辅助：'||vcode|| '不一致，请修改!');
end if;
end if;
end FTS2;

4.9 9:09更新

刚刚杨洋做了一笔资金下拨，两家的，结果保存的时候报错。

改用eric hu的触发器之后解决。

今天有时间研究两个客商为什么不行

4.9 14:23更新：中午测试了一下，发现不使用count*数量变量的触发器下面，如果结算凭证分录里面都是涉及到1003，也就是有客商和账户的，没有问题，但是只要有一行是其他科目，没有账户和客商辅助，就不能保存，触发器提示没有数据。

更新了一下eric hu的sql，更容易看懂（可以看出accode<>vcode受到前面 vRowsCount > 0的影响，否则accode和vcode就没有传值）

 1 create or replace trigger fts_v_b
 2   before insert or update on   fts_voucher_b
 3   for each row 
 4 declare
 5   -- local variables here
 6   accode char(6);
 7   vcode char(6);
 8   vRowsCount number;
 9 begin
10  select nvl(count(*),0) into vRowsCount from fts_voucher_b, gl_freevalue, bd_accid
11              where  gl_freevalue.freevalueid=:new.pk_ass
12                and  bd_accid.pk_accid=:new.pk_account
13                and length(gl_freevalue.valuecode) = '6'
14                and substr(gl_freevalue.valuecode, 0, 1) = '0';
15  
16  if vRowsCount > 0 then
17    select distinct bd_accid.accidcode,  gl_freevalue.valuecode into accode,vcode 
18    from fts_voucher_b, gl_freevalue, bd_accid
19              where  gl_freevalue.freevalueid=:new.pk_ass
20                and  bd_accid.pk_accid=:new.pk_account
21                and length(gl_freevalue.valuecode) = '6'
22                and substr(gl_freevalue.valuecode, 0, 1) = '0';  
23                
24          if  accode<>vcode   then 
25     raise_application_error(-20001,'此结算凭证账户：'||accode||'和客商辅助：'||vcode|| '不一致，请修改！');
26   
27  end if;  
28  else
29     return;
30  end if;
31  end  fts_v_b;

2012.4.13 14：42 更新exception用法，这样可以不用那个数量变量了（exception必须要放在最后）

create or replace trigger FTS2
  before insert or update on   fts_voucher_b
  for each row
declare
  -- local variables here
  accode char(6);
  vcode  char(6);
begin

  select distinct bd_accid.accidcode, gl_freevalue.valuecode
    into accode, vcode
    from fts_voucher_b, gl_freevalue, bd_accid
   where gl_freevalue.freevalueid = :new.pk_ass
     and bd_accid.pk_accid = :new.pk_account
     and length(gl_freevalue.valuecode) = '6'
     and substr(gl_freevalue.valuecode, 0, 1) = '0';

  
  if accode = vcode then
    return;
  end if;
  if accode <> vcode then raise_application_error(-20001,'次结算凭证账户'||accode||'和客商'||vcode|| '不一致，请修改！');
  end if;

 exception
  when no_data_found then  
    return;
end FTS2;

2012.6.21更新

下面是if else的用法，简化了一些sql

 1 create or replace trigger FTS2
 2   before insert or update on   fts_voucher_b
 3   for each row
 4 declare
 5   -- local variables here
 6   accode char(6);
 7   vcode  char(6);
 8 begin
 9 
10   select distinct bd_accid.accidcode, gl_freevalue.valuecode
11     into accode, vcode
12     from fts_voucher_b, gl_freevalue, bd_accid
13    where gl_freevalue.freevalueid = :new.pk_ass
14      and bd_accid.pk_accid = :new.pk_account
15      and length(gl_freevalue.valuecode) = '6'
16      and substr(gl_freevalue.valuecode, 0, 1) = '0';
17 
18   
19   if accode = vcode then
20     return;
21  else
22     raise_application_error(-20001,'次结算凭证账户'||accode||'和客商'||vcode|| '不一致，请修改！');
23   end if;
24 
25  exception
26   when no_data_found then  
27     return;
28 end FTS2;

更新4.14 22:50，看看一周前略显幼稚的写法，不过思路已经成型：

 1 create or replace trigger fts_v_b
 2 
 3   before insert OR update on   fts_voucher_b
 4 
 5   for each row
 6 
 7  
 8 
 9 begin
10 
11  select bd_accid.accidcode,  gl_freevalue.valuecode from fts_voucher_b, gl_freevalue, bd_accid
12 
13              where :new.pk_ass = gl_freevalue.freevalueid
14 
15                and :new.pk_account= bd_accid.pk_accid
16 
17                and length(gl_freevalue.valuecode) = '6'
18 
19                and substr(gl_freevalue.valuecode, 0, 1) = '0'
20 
21   if bd_accid.accidcode<>gl_freevalue.valuecode
22 
23    then aise_application_error(-20001,
24 
25                               '结算凭证'||'账户和客商不一致，请删除，之后重新填写结算凭证！');
26 
27   end;
28 
29   end if;
30 
31 end fts_v_b;