我理解你的说的是,对于原有单据的修改,因触发器判断唯一性而暴错.建议你在触发器中加一个判断,如果这是一个修改操作,就不判断唯一性,只有插入操作判断.你代码有下面片断: create or replace trigger CHECK_VOUCHER before insert or update on fts_voucher -- insert和update 分开判断 for each row
现在想只对表fts_voucher_b做触发器了,思路如下,大家看可行吗?是把:new作为了查询条件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 begin raise_application_error(-20001, '结算凭证账户和客商不一致'); end; 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); 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|| '不一致!');
create or replace trigger CHECK_VOUCHER before insert or update on fts_voucher -- insert和update 分开判断
for each row
以bug的形式提交给他们,不能算新需求,坚决不给他们钱。哈哈。
begin
raise_application_error(-20001, '结算凭证账户和客商不一致');
end;
end if; end 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;