两表:
table1(A1,B1,C1)
table2(A2,B2,C2)
我现在我对table1建立一个Insert触发器;
想查询得到table1表某条件下平均值AVG(C1),
如果table2表中存在刚插入的值,就修改table2 C2=AVG(C1);否则插入记录Insert table2(A2,B2,C2) Values(A1,B1,C1) 实现有误的代码:
--table1(A1,B1,C1) primary(A1,B1)
--table2(A2,B2,C2) priamry(A2,B2)
--before
create or replace trigger tigInsertTest
before insert on table1
referencing new AS new_value old AS old_value
for each row
declare
-- local variables here
v_count number;
v_avg number;
begin
select AVG(C1) INTO v_avg from table1
where A1=:new_value.A1 AND B1=:new_value.B1;
select Count(*) INTO v_count from table2
where A2=:new_value.A1 and B2=:new_value.B1;
if v_count=0 then
insert into table2(A2,B2,C2)
values(:new_value.A1,:new_value.B1,v_avg);
else
update table2 set C2=v_avg
where A2=:new_value.A1 AND B2=:new_value.B2;
end if;
end tigInsertTest;
原因涉及到操作本身表(table1),我用Before行级触发器,不能计算新+的行;而用After.提示不能读错误;希望得到大侠们的帮助,拿出解决方案或例子;最好在上述实现过程中指正;
先谢谢了:)
table1(A1,B1,C1)
table2(A2,B2,C2)
我现在我对table1建立一个Insert触发器;
想查询得到table1表某条件下平均值AVG(C1),
如果table2表中存在刚插入的值,就修改table2 C2=AVG(C1);否则插入记录Insert table2(A2,B2,C2) Values(A1,B1,C1) 实现有误的代码:
--table1(A1,B1,C1) primary(A1,B1)
--table2(A2,B2,C2) priamry(A2,B2)
--before
create or replace trigger tigInsertTest
before insert on table1
referencing new AS new_value old AS old_value
for each row
declare
-- local variables here
v_count number;
v_avg number;
begin
select AVG(C1) INTO v_avg from table1
where A1=:new_value.A1 AND B1=:new_value.B1;
select Count(*) INTO v_count from table2
where A2=:new_value.A1 and B2=:new_value.B1;
if v_count=0 then
insert into table2(A2,B2,C2)
values(:new_value.A1,:new_value.B1,v_avg);
else
update table2 set C2=v_avg
where A2=:new_value.A1 AND B2=:new_value.B2;
end if;
end tigInsertTest;
原因涉及到操作本身表(table1),我用Before行级触发器,不能计算新+的行;而用After.提示不能读错误;希望得到大侠们的帮助,拿出解决方案或例子;最好在上述实现过程中指正;
先谢谢了:)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货