Question

需要在ORCALE中写个触发器，现在有2个表A和B，当向A表插入数据时，把新的数据同步插入到B表中

A和B表中字段完全相同，如下
A id,name,money,pid
B id,name,money,pid
若A中删除数据，B表不变
若A中重新插入B中已有Id时，B表不变
B表中字段会比A表多，求大神``

Answer 1

create or replace trigger trig_in after insert on A
for each row
begin
insert into B values(:new.id,:new.name,:new.money,:new.pid)
where :new.id not in (seleect id from B);
end trig_in;

追问

where 语句这么写会报错

追答

把insert into 语句改为如下：
insert into B select :new.id,:new.name,:new.money,:new.pid from dual
where :new.id not in (select id from B);

追问

谢谢了~！

Answer 2

create trigger tri_backup_a
after insert on a for each row

declare cnt number;

begin
if inserting then
select count(*) into cnt from b where b.id=:new.id;
if cnt=0 then
insert into b values(:new.id,:new.name,:new.money,:new.pid);
end if;
end if;
end;