表t_crop_basic
-- Create table
create table T_CROP_BASIC
(
ORG_ID NUMBER(8) not null,
GT_NUM NUMBER(8),
GRDZ_NUM NUMBER(8),
HH_NUM NUMBER(8),
SYGS_NUM NUMBER(8),
NZGS_NUM NUMBER(8),
WZQY_NUM NUMBER(8)
)
tablespace NETBAISDATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 16
minextents 1
maxextents unlimited
);
alter table T_CROP_BASIC
add primary key (ORG_ID)
using index
tablespace NETBAISDATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 256K
minextents 1
maxextents unlimited
);
insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1689, 4933, 1, 0, 8572, 912, 1103);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1692, 5572, 198, 8, 644, 72, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1699, 15608, 593, 18, 1121, 110, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1706, 6241, 202, 6, 281, 17, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1712, 17278, 1165, 23, 1851, 448, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1729, 13956, 1148, 14, 1612, 305, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1744, 14674, 518, 15, 1297, 234, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1768, 9200, 271, 24, 1161, 230, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1778, 1619, 153, 7, 2765, 234, 0);表二t_crop_basic1是表t_crop_baisc的映像;
求一个触发器两表实现同步 并且表二t_crop_baisc1中最后有一条org_id=1111的记录是各个字段的汇总
也就是(1111,sum(gt_num),sum(grdz_num),....)
本人写了一个得不到最后那条汇总信息
-- Create table
create table T_CROP_BASIC
(
ORG_ID NUMBER(8) not null,
GT_NUM NUMBER(8),
GRDZ_NUM NUMBER(8),
HH_NUM NUMBER(8),
SYGS_NUM NUMBER(8),
NZGS_NUM NUMBER(8),
WZQY_NUM NUMBER(8)
)
tablespace NETBAISDATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 16
minextents 1
maxextents unlimited
);
alter table T_CROP_BASIC
add primary key (ORG_ID)
using index
tablespace NETBAISDATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 256K
minextents 1
maxextents unlimited
);
insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1689, 4933, 1, 0, 8572, 912, 1103);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1692, 5572, 198, 8, 644, 72, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1699, 15608, 593, 18, 1121, 110, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1706, 6241, 202, 6, 281, 17, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1712, 17278, 1165, 23, 1851, 448, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1729, 13956, 1148, 14, 1612, 305, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1744, 14674, 518, 15, 1297, 234, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1768, 9200, 271, 24, 1161, 230, 0);insert into t_crop_basic (ORG_ID, GT_NUM, GRDZ_NUM, HH_NUM, SYGS_NUM, NZGS_NUM, WZQY_NUM)
values (1778, 1619, 153, 7, 2765, 234, 0);表二t_crop_basic1是表t_crop_baisc的映像;
求一个触发器两表实现同步 并且表二t_crop_baisc1中最后有一条org_id=1111的记录是各个字段的汇总
也就是(1111,sum(gt_num),sum(grdz_num),....)
本人写了一个得不到最后那条汇总信息
after insert or update or delete on t_crop_basic
for each row
declare
v_org_id number(8);
v_gt_num number(8);
v_grdz_num number(8);
v_hh_num number(8);
v_sygs_num number(8);
v_nzgs_num number(8);
v_wzqy_num number(8);
v_v_gt_num number(8);
v_v_grdz_num number(8);
v_v_hh_num number(8);
v_v_sygs_num number(8);
v_v_nzgs_num number(8);
v_v_wzqy_num number(8);
a char(2);
begin
select sum(GT_NUM) into v_V_GT_NUM from t_crop_basic1;
select sum(GRDZ_NUM) into v_V_GRDZ_NUM from t_crop_basic1;
select sum(HH_NUM) into v_V_HH_NUM from t_crop_basic1;
select sum(SYGS_NUM) into v_V_SYGS_NUM from t_crop_basic1;
select sum(NZGS_NUM) into v_V_NZGS_NUM from t_crop_basic1;
select sum(WZQY_NUM) into v_V_WZQY_NUM from t_crop_basic1;
if inserting then
v_org_id :=:new.org_id;
v_gt_num :=:new.gt_num;
v_grdz_num :=:new.grdz_num;
v_hh_num :=:new.hh_num;
v_sygs_num :=:new.sygs_num;
v_nzgs_num :=:new.nzgs_num;
v_wzqy_num :=:new.wzqy_num;
a :='i';
elsif deleting then
v_org_id :=:old.org_id;
v_gt_num :=:old.gt_num;
v_grdz_num :=:old.grdz_num;
v_hh_num :=:old.hh_num;
v_sygs_num :=:old.sygs_num;
v_nzgs_num :=:old.nzgs_num;
v_wzqy_num :=:old.wzqy_num;
a :='d';
elsif updating then
v_org_id :=:new.org_id;
v_gt_num :=:new.gt_num;
v_grdz_num :=:new.grdz_num;
v_hh_num :=:new.hh_num;
v_sygs_num :=:new.sygs_num;
v_nzgs_num :=:new.nzgs_num;
v_wzqy_num :=:new.wzqy_num;
a :='u';
end if;
if a = 'i' then
INSERT INTO t_crop_basic1 VALUES (v_org_id,V_GT_NUM,V_GRDZ_NUM,V_HH_NUM,V_SYGS_NUM,V_NZGS_NUM,V_WZQY_NUM);
update t_crop_basic1 set org_id=v_org_id,gt_num=V_GT_NUM,grdz_num=V_GRDZ_NUM,hh_num=V_HH_NUM,SYGS_NUM=V_SYGS_NUM,NZGS_NUM=V_NZGS_NUM,WZQY_NUM=V_WZQY_NUM
where org_id=v_org_id;
elsif a = 'd' then
delete from t_crop_basic1 where (org_id=v_org_id);
elsif a = 'u' then
update t_crop_basic1 set org_id=v_org_id,gt_num=V_GT_NUM,grdz_num=V_GRDZ_NUM,hh_num=V_HH_NUM,SYGS_NUM=V_SYGS_NUM,NZGS_NUM=V_NZGS_NUM,WZQY_NUM=V_WZQY_NUM
where org_id=v_org_id;
end if;
if V_ORG_ID <>V_GT_NUM then
SUM_T_CROP_BASIC(v_v_gt_num,v_v_grdz_num,v_v_hh_num,v_v_sygs_num,v_v_nzgs_num,v_v_wzqy_num);
end if;
END;
create or replace procedure SUM_T_CROP_BASIC (
v_v_gt_num number,
v_v_grdz_num number,
v_v_hh_num number,
v_v_sygs_num number,
v_v_nzgs_num number,
v_v_wzqy_num number) IS
v_v_org_id number:=1111;
begin
insert into t_crop_basic1 values (v_v_org_id,v_V_GT_NUM,v_V_GRDZ_NUM,v_V_HH_NUM,v_V_SYGS_NUM,v_V_NZGS_NUM,v_V_WZQY_NUM);
end SUM_T_CROP_BASIC;
create or replace trigger trg_t_crop_basic
after insert or update or delete on t_crop_basic
for each row
declare
v_org_id number(8);
v_gt_num number(8);
v_grdz_num number(8);
v_hh_num number(8);
v_sygs_num number(8);
v_nzgs_num number(8);
v_wzqy_num number(8);
v_v_gt_num number(8);
v_v_grdz_num number(8);
v_v_hh_num number(8);
v_v_sygs_num number(8);
v_v_nzgs_num number(8);
v_v_wzqy_num number(8);
a char(2);
begin
select sum(GT_NUM) into v_V_GT_NUM from t_crop_basic;
select sum(GRDZ_NUM) into v_V_GRDZ_NUM from t_crop_basic;
select sum(HH_NUM) into v_V_HH_NUM from t_crop_basic;
select sum(SYGS_NUM) into v_V_SYGS_NUM from t_crop_basic;
select sum(NZGS_NUM) into v_V_NZGS_NUM from t_crop_basic;
select sum(WZQY_NUM) into v_V_WZQY_NUM from t_crop_basic;
if inserting then
v_org_id :=:new.org_id;
v_gt_num :=:new.gt_num;
v_grdz_num :=:new.grdz_num;
v_hh_num :=:new.hh_num;
v_sygs_num :=:new.sygs_num;
v_nzgs_num :=:new.nzgs_num;
v_wzqy_num :=:new.wzqy_num;
a :='i';
elsif deleting then
v_org_id :=:old.org_id;
v_gt_num :=:old.gt_num;
v_grdz_num :=:old.grdz_num;
v_hh_num :=:old.hh_num;
v_sygs_num :=:old.sygs_num;
v_nzgs_num :=:old.nzgs_num;
v_wzqy_num :=:old.wzqy_num;
a :='d';
elsif updating then
v_org_id :=:new.org_id;
v_gt_num :=:new.gt_num;
v_grdz_num :=:new.grdz_num;
v_hh_num :=:new.hh_num;
v_sygs_num :=:new.sygs_num;
v_nzgs_num :=:new.nzgs_num;
v_wzqy_num :=:new.wzqy_num;
a :='u';
end if;
if a = 'i' then
INSERT INTO t_crop_basic1 VALUES (v_org_id,V_GT_NUM,V_GRDZ_NUM,V_HH_NUM,V_SYGS_NUM,V_NZGS_NUM,V_WZQY_NUM);
update t_crop_basic1 set org_id=v_org_id,gt_num=V_GT_NUM,grdz_num=V_GRDZ_NUM,hh_num=V_HH_NUM,SYGS_NUM=V_SYGS_NUM,NZGS_NUM=V_NZGS_NUM,WZQY_NUM=V_WZQY_NUM
where org_id=v_org_id;
elsif a = 'd' then
delete from t_crop_basic1 where (org_id=v_org_id);
elsif a = 'u' then
update t_crop_basic1 set org_id=v_org_id,gt_num=V_GT_NUM,grdz_num=V_GRDZ_NUM,hh_num=V_HH_NUM,SYGS_NUM=V_SYGS_NUM,NZGS_NUM=V_NZGS_NUM,WZQY_NUM=V_WZQY_NUM
where org_id=v_org_id;
end if;
if V_ORG_ID <>V_GT_NUM then
SUM_T_CROP_BASIC(v_v_gt_num,v_v_grdz_num,v_v_hh_num,v_v_sygs_num,v_v_nzgs_num,v_v_wzqy_num);
end if;
END;
v_v_gt_num number,
v_v_grdz_num number,
v_v_hh_num number,
v_v_sygs_num number,
v_v_nzgs_num number,
v_v_wzqy_num number) IS
v_v_org_id number:=1111;
begin
delete from t_crop_basic1 where org_id = v_v_org_id;
insert into t_crop_basic1 values (v_v_org_id,v_V_GT_NUM,v_V_GRDZ_NUM,v_V_HH_NUM,v_V_SYGS_NUM,v_V_NZGS_NUM,v_V_WZQY_NUM);
end SUM_T_CROP_BASIC;
create or replace trigger trg_t_crop_basic
after insert or update or delete on t_crop_basic
for each row
declare
v_org_id number(8);
v_gt_num number(8);
v_grdz_num number(8);
v_hh_num number(8);
v_sygs_num number(8);
v_nzgs_num number(8);
v_wzqy_num number(8);
v_v_gt_num number(8);
v_v_grdz_num number(8);
v_v_hh_num number(8);
v_v_sygs_num number(8);
v_v_nzgs_num number(8);
v_v_wzqy_num number(8);
a char(2);
begin
if inserting then
v_org_id :=:new.org_id;
v_gt_num :=:new.gt_num;
v_grdz_num :=:new.grdz_num;
v_hh_num :=:new.hh_num;
v_sygs_num :=:new.sygs_num;
v_nzgs_num :=:new.nzgs_num;
v_wzqy_num :=:new.wzqy_num;
a :='i';
elsif deleting then
v_org_id :=:old.org_id;
v_gt_num :=:old.gt_num;
v_grdz_num :=:old.grdz_num;
v_hh_num :=:old.hh_num;
v_sygs_num :=:old.sygs_num;
v_nzgs_num :=:old.nzgs_num;
v_wzqy_num :=:old.wzqy_num;
a :='d';
elsif updating then
v_org_id :=:new.org_id;
v_gt_num :=:new.gt_num;
v_grdz_num :=:new.grdz_num;
v_hh_num :=:new.hh_num;
v_sygs_num :=:new.sygs_num;
v_nzgs_num :=:new.nzgs_num;
v_wzqy_num :=:new.wzqy_num;
a :='u';
end if;
if a = 'i' then
INSERT INTO t_crop_basic1 VALUES (v_org_id,V_GT_NUM,V_GRDZ_NUM,V_HH_NUM,V_SYGS_NUM,V_NZGS_NUM,V_WZQY_NUM);
update t_crop_basic1 set org_id=v_org_id,gt_num=V_GT_NUM,grdz_num=V_GRDZ_NUM,hh_num=V_HH_NUM,SYGS_NUM=V_SYGS_NUM,NZGS_NUM=V_NZGS_NUM,WZQY_NUM=V_WZQY_NUM
where org_id=v_org_id;
elsif a = 'd' then
delete from t_crop_basic1 where (org_id=v_org_id);
elsif a = 'u' then
update t_crop_basic1 set org_id=v_org_id,gt_num=V_GT_NUM,grdz_num=V_GRDZ_NUM,hh_num=V_HH_NUM,SYGS_NUM=V_SYGS_NUM,NZGS_NUM=V_NZGS_NUM,WZQY_NUM=V_WZQY_NUM
where org_id=v_org_id;
end if;
select sum(GT_NUM) into v_V_GT_NUM from t_crop_basic1 b where b.org_id<>1111 ;
select sum(GRDZ_NUM) into v_V_GRDZ_NUM from t_crop_basic1 b where b.org_id<>1111 ;
select sum(HH_NUM) into v_V_HH_NUM from t_crop_basic1 b where b.org_id<>1111 ;
select sum(SYGS_NUM) into v_V_SYGS_NUM from t_crop_basic1 b where b.org_id<>1111 ;
select sum(NZGS_NUM) into v_V_NZGS_NUM from t_crop_basic1 b where b.org_id<>1111 ;
select sum(WZQY_NUM) into v_V_WZQY_NUM from t_crop_basic1 b where b.org_id<>1111 ;
SUM_T_CROP_BASIC(v_v_gt_num,v_v_grdz_num,v_v_hh_num,v_v_sygs_num,v_v_nzgs_num,v_v_wzqy_num);
END;
create or replace procedure SUM_T_CROP_BASIC (
v_v_gt_num number,
v_v_grdz_num number,
v_v_hh_num number,
v_v_sygs_num number,
v_v_nzgs_num number,
v_v_wzqy_num number) IS
v_v_org_id number:=1111;
begin
delete from t_crop_basic1 where org_id = v_v_org_id;
insert into t_crop_basic1 values (v_v_org_id,v_V_GT_NUM,v_V_GRDZ_NUM,v_V_HH_NUM,v_V_SYGS_NUM,v_V_NZGS_NUM,v_V_WZQY_NUM);
end SUM_T_CROP_BASIC;