你这个要求不能用简单sql实现,需要用plsql处理。 declare newval number(10,2); begin begin select ACTIVEQUA-"+ornum+" into newval from ware_res where resid='004215'; exception when no_data_gound then Raise_application_error(-20001,'no_data_found'); end; if newval < 0 then Raise_application_error(-20002,'error,allready < 0 '); else update ware_res set ACTIVEQUA=ACTIVEQUA-"+ornum+" where resid='004215' end if; end;如果不需要错误提示一个sql可以实现小于0时不更新,只需要增加一个where条件。:)
create trigger test_tri before update on ware_res for each row begin if :new.ACTIVEQUA-ornum<=0 then raise_application_error(-20001,'出错'); end if; end; /
declare
newval number(10,2);
begin
begin
select ACTIVEQUA-"+ornum+"
into newval
from ware_res
where resid='004215';
exception when no_data_gound then
Raise_application_error(-20001,'no_data_found');
end;
if newval < 0 then
Raise_application_error(-20002,'error,allready < 0 ');
else
update ware_res set ACTIVEQUA=ACTIVEQUA-"+ornum+" where resid='004215'
end if;
end;如果不需要错误提示一个sql可以实现小于0时不更新,只需要增加一个where条件。:)
before update on ware_res
for each row
begin
if :new.ACTIVEQUA-ornum<=0 then
raise_application_error(-20001,'出错');
end if;
end;
/