create or replace trigger dbo.trg_insert_rkd
before insert on dbo.sj_rkd
for each row
declare
num number;
begin
select count(*) into num from dbo.sj_current_kc
where ksdh = :new.ksdh and sjdh = :new.sjdh and sjph = :new.sjph;
if num>0 then
update dbo.sj_current_kc set current_kc = current_kc + :new.sl
where ksdh = :new.ksdh and sjdh = :new.sjdh and sjph = :new.sjph;
else
insert into dbo.sj_current_kc
(ksdh,sjdh,sjph,current_kc)
values(:new.ksdh,:new.sjdh,:new.sjph,:new.sl);
end if
end
before insert on dbo.sj_rkd
for each row
declare
num number;
begin
select count(*) into num from dbo.sj_current_kc
where ksdh = :new.ksdh and sjdh = :new.sjdh and sjph = :new.sjph;
if num>0 then
update dbo.sj_current_kc set current_kc = current_kc + :new.sl
where ksdh = :new.ksdh and sjdh = :new.sjdh and sjph = :new.sjph;
else
insert into dbo.sj_current_kc
(ksdh,sjdh,sjph,current_kc)
values(:new.ksdh,:new.sjdh,:new.sjph,:new.sl);
end if
end
解决方案 »
- oracle大表联合查询,速度超级慢
- 求高人解答。为什么PL/SQL指定了其他表空间,但是还是导入到SYSTEM表空间里面去了?
- oracle中关于to_number用法
- 大数据量在查询的同时实时显示的问题
- 超大表IMP导入出错???求救
- Oracle 中跨库查询表的烦恼
- 急!在线等求个sql
- 查询速度出奇的慢,但是服务器的cpu和内存的利用率很低,怎样提高效率?充分的利用服务器?
- 一个表在用于记录软件工作日志的时候,是有主键效率高,还是没有的高???
- 关于新旧数据库间的数据导入
- 有关select查询速度的问题
- this question is very lower, I need you help! Thanks!!数据库启动问题
create or replace TRIGGER trg_insert_rkd
before insert ON sj_rkd
for each row
declare
v_ksdh char(10) --科室代号
v_sjdh varchar(30)--试剂编号
v_sjph varchar(30)--试剂批号
v_sl numeric(24,6)--出库数量
v_num number;
begin
v_ksdh := :new.ksdh;
v_sjdh := :new.sjdh;
v_sjph := :new.sjph;
v_sl := :new.rksl;select count(*) into v_num from sj_current_kc where ksdh=v_ksdh and sjdh = v_sjdh and sjph = v_sjph;
if v_num>0 then
update sj_current_kc set current_kc = current_kc + v_sl
where ksdh = v_ksdh and sjdh = v_sjdh and sjph = v_sjph;
else
insert into sj_current_kc
(ksdh,sjdh,sjph,current_kc)
values(v_ksdh,v_sjdh,v_sjph,v_sl);
end if;
end trg_insert_rkd;
/
after insert on sj_rkd
for each row
declare
num number;
begin
select count(*) into num from sj_current_kc
where ksdh = :new.ksdh and sjdh = :new.sjdh and sjph = :new.sjph;
if num <> 0 then
update sj_current_kc
set current_kc = current_kc + :new.sl
where ksdh = :new.ksdh and sjdh = :new.sjdh and sjph = :new.sjph;
else
insert into dbo.sj_current_kc(ksdh,sjdh,sjph,current_kc)
values(:new.ksdh,:new.sjdh,:new.sjph,:new.sl);
end if
end;
after insert on sj_rkd
for each row
declare
num number;
begin
select count(*) into num from sj_current_kc
where ksdh = :new.ksdh and sjdh = :new.sjdh and sjph = :new.sjph;
if num <> 0 then
update sj_current_kc
set current_kc = current_kc + :new.sl
where ksdh = :new.ksdh and sjdh = :new.sjdh and sjph = :new.sjph;
else
insert into dbo.sj_current_kc(ksdh,sjdh,sjph,current_kc)
values(:new.ksdh,:new.sjdh,:new.sjph,:new.sl);
end if
end;
/
after insert on dbo.sj_rkd
for each row
declare
-- local variables here
v_ksdh char(10);
v_sjdh varchar2(30);
v_sjph varchar2(30);
v_sl number(24,6);
v_cnt number;
begin
v_ksdh := :new.ksdh
v_sjdh := :new.sjdh
v_sjph := :new.sjph
v_sl := :new.sl
select count(*) into v_cnt from dbo.sj_current_kc where ksdh = v_ksdh and sjdh = v_sjdh and sjph = v_sjph;
if v_cnt > 0 then
update dbo.sj_current_kc set curretn_kc = current_kc + v_sl where ksdh = v_ksdh and sjdh = v_sjdh and sjph = v_sjph;
else
insert into dbo.sj_current_kc (ksdh,sjdh,sjph,current_kc) values (v_ksdh,v_sjdh,v_sjph,v_sl);
end if
end dbo.trg_insert_rkd;