oralce中没有if exists这种判断语句! 可以用select count(*) into v_num from .....where .....; if v_num > 0 then else end if; 来进行判断是否有数据!
create or replace trigger tests before insert on dbo.bponderation for each row declare num number; begin select count(*) into num from bmonth,inserted where bmonth.fbreedid=:new.fbreedid and bmonth.fprounitid=:new.fprounitid and bmonth.fmonth=to_char(sysdate,'mm') and bmonth.fyear=to_char(sysdate,'yyyy') if num>0 then update... else insert into bmonth (fyear,fmonth,fcarcount,fbreedid,fprounitid, fnetweight,foverweight,ftax,fovermoney) values(to_char(sysdate,'yyyy'), to_char(sysdate,'mm'),'1',:new.fbreedid,:new.fprounitid, :new.fnetweight,:new.overweight,:new.taxmoney,:new.overqry ); end if; end;
可以用select count(*) into v_num from .....where .....;
if v_num > 0 then else end if;
来进行判断是否有数据!
before insert on dbo.bponderation
for each row
declare
num number;
begin
select count(*) into num
from bmonth,inserted
where bmonth.fbreedid=:new.fbreedid and bmonth.fprounitid=:new.fprounitid and
bmonth.fmonth=to_char(sysdate,'mm') and bmonth.fyear=to_char(sysdate,'yyyy')
if num>0 then
update... else
insert into bmonth (fyear,fmonth,fcarcount,fbreedid,fprounitid,
fnetweight,foverweight,ftax,fovermoney) values(to_char(sysdate,'yyyy'),
to_char(sysdate,'mm'),'1',:new.fbreedid,:new.fprounitid,
:new.fnetweight,:new.overweight,:new.taxmoney,:new.overqry ); end if;
end;