急..关于oracle 触发器

[my1982]

有两张表,A 跟B
A表有字段
ID AID NAME VALUE (表中有重复的AID)

B表有字段
AID NAME VALUE (表中没有重复的AID)

想得到这样一个功能..

A表插每个数据..对应的B表做一个update操作 使得每个B表中的AID对应的数据是A表中AID的最新数据

意思就是A表插入每一条数据..对应AID中的B表就update一下..便B表的AID保持最新的数据

[wangrui4917]

create or replace trigger tri_a
after insert on A
declare
v_aid integer;
begin
select count(b.aid) into v_aid from b where b.aid=:new.aid;
if v_aid >=1 then
update B
set (name,value)=(:new.name,:new.value);
where b.aid=:new.aid;
else
insert into b values(:new.aid,:new.name,:new.value);
end if;
end;

[xredleaf]

up





_______________________________
DBA请进群QQ群：88039805

[jackluo1981]

[Quote=引用 1 楼 wangrui4917 的回复:]
create or replace trigger tri_a
after insert on A
declare
v_aid integer;
begin
select count(b.aid) into v_aid from b where b.aid=:new.aid;
if v_aid >=1 then
update B
set (name,value)=(:new.name,:new.value);
where b.aid=:new.aid;
else
insert into b values(:new.aid,:new.name,:new.value);
end if;
end;
[/Quote]
顶这个，考虑的比较全面。

[suncrafted]

应该加入FOR EACH ROW
才可以使用:new

create or replace trigger tri_a
after insert on A
FOR EACH ROW
declare
v_aid integer;
begin
select count(b.aid) into v_aid from b where b.aid=:new.aid;
if v_aid >=1 then
update B
set (name,value)=(:new.name,:new.value);
where b.aid=:new.aid;
else
insert into b values(:new.aid,:new.name,:new.value);
end if;
end;

[bzcnc]

create table ZZW_TEMP10
(
ID NUMBER,
AID NUMBER(8) not null,
NAME VARCHAR2(64),
VALUE NUMBER(8) not null
)


select * from zzw_temp10;
/*
ID AID NAME VALUE
1 10 0
2 10 dialin001 1
3 11 0
4 11 dialin_0001 2
5 90 0
6 90 dialin_0002 3
7 13 0
8 13 dialin_0003 4
9 14 0
10 14 dialin_0004 5
*/

create sequence seq_test
start with 10
increment by 1;

select seq_test.nextval from dual;

create table ZZW_TEMP11
(
AID NUMBER(8) not null,
NAME VARCHAR2(64),
VALUE NUMBER(8) not null
)

select * from zzw_temp11;
/*
AID NAME VALUE
10 dialin001 1
11 dialin_0001 2
90 dialin_0002 3
13 dialin_0003 4
14 dialin_0004 5
*/


create or replace trigger trig_test
before insert on zzw_temp10 for each row
begin

update zzw_temp11 a
set a.name=:new.name,a.value=:new.value
where a.aid=:new.aid;

end;

------下面进行测试：

insert into zzw_temp10
values(seq_test.nextval,90,'test',5);

select * from zzw_temp10;
/*
ID AID NAME VALUE
1 10 0
2 10 dialin001 1
3 11 0
4 11 dialin_0001 2
5 90 0
6 90 dialin_0002 3
7 13 0
8 13 dialin_0003 4
9 14 0
10 14 dialin_0004 5
14 90 test 5
*/
select * from zzw_temp11;
/*
AID NAME VALUE
10 dialin001 1
11 dialin_0001 2
90 test 5
13 dialin_0003 4
14 dialin_0004 5
*/
commit;