create or replace
trigger tr_nba_idu
after insert or delete or update on nba
for each row
begin
case
when inserting then
insert into nba2009
values(:new.id,:new.name,:new.groups,:new.wore);
when deleting then
delete nba2009 where id=:old.id;
when updating then
begin
delete nba2009 where id=:old.id;
insert into nba2009
values(:new.id,:new.name,:new.groups,:new.wore);
end;
end case;
end tr_nba_idu;上面after insert or delete or update on nba中,NBA为表,我只需nab中的两个字段,nba1中的两个字段,然后插入nba2009这个表中,请问可否能实现,具体的代码,谢谢
在你insert到nba2009之前select nba1的两个字段出来,放到变量里面,然后和nba表的两个字段一起插入nba2009就行了阿
只能是当修改nba 才把nab对应的表的数据插入到 nba2009
--可以考虑这个
merge into nba2009 c using(select a.ida,a.name,a.groups,a.wore from nba a,nba1 b where a.id=b.id) t on c.id=t.id
when matched then
update set c.id=t.id, c.name=t.name,c.groups=t.groups,c.wore=t.wore
when not matched then
insert (c.ida,c.name,c.groups,c.wore) values(t.ida,t.name,t.groups,t.wore)
SQL语查出显示的一样SELECT IPH_APPLY.IPHINFOID AS INFOID,IPH_APPLY.PERMITID AS SPSXBH,IPH_APPLY.PERMITID AS SPSXZXBH,IPH_APPLY.OPAPPLYNO AS YXTYWLSH,
ROW_NUMBER() OVER(PARTITION BY IPH_OPINION.IPHINFOID ORDER BY OPINIONDATE) AS SPBZH,
IPH_OPINION.STEPID AS SPHJDM,IPH_OPINION.STEPID AS SPHJMC,
IPH_OPINION.OPINIONUSER AS SPRXM,OPINIONUSER AS SPRZWDM,OPINIONUSER AS SPRZWMC,IPH_OPINION.OPINION AS SPYJ,IPH_OPINION.OPINIONDATE AS SPSJ
FROM IPH_APPLY
INNER JOIN IPH_OPINION ON IPH_APPLY.IPHINFOID=IPH_OPINION.IPHINFOID
请问这个触发器怎么写才能让,IPH_APPLY,IPH_OPINION表中所需数据实时更新到IPHTEMP表中?
--创建视图
create or replace view v_pt
as
SELECT IPH_APPLY.IPHINFOID AS INFOID,IPH_APPLY.PERMITID AS SPSXBH,IPH_APPLY.PERMITID AS SPSXZXBH,IPH_APPLY.OPAPPLYNO AS YXTYWLSH,
ROW_NUMBER() OVER(PARTITION BY IPH_OPINION.IPHINFOID ORDER BY OPINIONDATE) AS SPBZH,
IPH_OPINION.STEPID AS SPHJDM,IPH_OPINION.STEPID AS SPHJMC,
IPH_OPINION.OPINIONUSER AS SPRXM,OPINIONUSER AS SPRZWDM,OPINIONUSER AS SPRZWMC,IPH_OPINION.OPINION AS SPYJ,IPH_OPINION.OPINIONDATE AS SPSJ
FROM IPH_APPLY
INNER JOIN IPH_OPINION ON IPH_APPLY.IPHINFOID=IPH_OPINION.IPHINFOID
--用instead of 触发器
create or replace trigger tri_iniphtemp instead of insert or update or delete on v_pt for each row
begin
if inserting then
insert into IPHTEMP values(:new.INFOID,:new.SPSXBH,:new.SPSXZXBH,:new.YXTYWLSH,:new.SPBZH,:new.SPHJDM,
:new.SPHJMC,:new.SPRXM,:new.SPRZWDM,:new.SPRZWMC,:new.SPYJ,:new.SPSJ);
elsif updating then
update IPHTEMP set INFOID=:new.INFOID,SPSXBH=:new.SPSXBH....... where INFOID=:new.INFOID;
elsif deleting then
delete from IPHTEMP where INFOID=:old.INFOID;
end if;
end;
--创建视图
create or replace view v_pt
as
SELECT IPH_APPLY.IPHINFOID AS INFOID,IPH_APPLY.PERMITID AS SPSXBH,IPH_APPLY.PERMITID AS SPSXZXBH,IPH_APPLY.OPAPPLYNO AS YXTYWLSH,
ROW_NUMBER() OVER(PARTITION BY IPH_OPINION.IPHINFOID ORDER BY OPINIONDATE) AS SPBZH,
IPH_OPINION.STEPID AS SPHJDM,IPH_OPINION.STEPID AS SPHJMC,
IPH_OPINION.OPINIONUSER AS SPRXM,OPINIONUSER AS SPRZWDM,OPINIONUSER AS SPRZWMC,IPH_OPINION.OPINION AS SPYJ,IPH_OPINION.OPINIONDATE AS SPSJ
FROM IPH_APPLY
INNER JOIN IPH_OPINION ON IPH_APPLY.IPHINFOID=IPH_OPINION.IPHINFOID
--用instead of 触发器
create or replace trigger tri_iniphtemp instead of insert or update or delete on v_pt for each row
begin
if inserting then
insert into IPHTEMP values(:new.INFOID,:new.SPSXBH,:new.SPSXZXBH,:new.YXTYWLSH,:new.SPBZH,:new.SPHJDM,
:new.SPHJMC,:new.SPRXM,:new.SPRZWDM,:new.SPRZWMC,:new.SPYJ,:new.SPSJ);
elsif updating then
update IPHTEMP set INFOID=:new.INFOID,SPSXBH=:new.SPSXBH,SPSXZXBH=:new.SPSXZXBH,YXTYWLSH=:new.YXTYWLSH,SPBZH=:new.SPBZH,SPHJDM=:new.SPHJDM,SPHJMC=:new.SPHJMC,SPRXM=:new.SPRXM,SPRZWDM=:new.SPRZWDM,SPRZWMC=:new.SPRZWMC,SPYJ=:new.SPYJ,SPSJ=:new.SPSJ where INFOID=:new.INFOID;
elsif deleting then
delete from TABLE4 where INFOID=:old.INFOID;
end if;
end;根据上述步骤,先创建视图,然后创建了触发器,视图与触发器都是正常运行,
但在源中修改部他了数据,目标库IPHTEMP中没有更新,请问是怎么回事?
for each row
declare
num number;
begin
if inseting then
select count(1) into num from IPH_OPINION where IPHINFOID=:new.IPHINFOID;
if num >0 then
insert into IPHTEMP values(:new.IPHINFOID........);
end if;
elsif updating then
select count(1) into num from IPH_OPINION where IPHINFOID=:new.IPHINFOID;
if num >0 then
update IPHTEMP set SPSXBH=:new.SPSXBH....... where IPHINFOID=:new.IPHINFOID;
end if;
elsif deleting then
select count(1) into num from IPH_OPINION where IPHINFOID=:old.IPHINFOID;
if num >0 then
delete from IPHTEMP where IPHINFOID=:old.IPHINFOID;
end if;
end if;
end;
--这样是可以,但是耗资源,
建议还是不要用触发器 用存储过程来create or replace procedure p_up
as
begin
insert into IPHTEMP SELECT IPH_APPLY.IPHINFOID AS INFOID,IPH_APPLY.PERMITID AS SPSXBH,IPH_APPLY.PERMITID AS SPSXZXBH,IPH_APPLY.OPAPPLYNO AS YXTYWLSH,
ROW_NUMBER() OVER(PARTITION BY IPH_OPINION.IPHINFOID ORDER BY OPINIONDATE) AS SPBZH,
IPH_OPINION.STEPID AS SPHJDM,IPH_OPINION.STEPID AS SPHJMC,
IPH_OPINION.OPINIONUSER AS SPRXM,OPINIONUSER AS SPRZWDM,OPINIONUSER AS SPRZWMC,IPH_OPINION.OPINION AS SPYJ,IPH_OPINION.OPINIONDATE AS SPSJ
FROM IPH_APPLY
INNER JOIN IPH_OPINION ON IPH_APPLY.IPHINFOID=IPH_OPINION.IPHINFOID;
end;
if inserting then
for each row
declare
num number;
begin
if inseting then
select count(1) into num from IPH_OPINION where IPHINFOID=:new.IPHINFOID;
if num >0 then
insert into IPHTEMP values(:new.IPHINFOID........);
end if;
elsif updating then
select count(1) into num from IPH_OPINION where IPHINFOID=:new.IPHINFOID;
if num >0 then
update IPHTEMP set SPSXBH=:new.SPSXBH....... where IPHINFOID=:new.IPHINFOID;
end if;
elsif deleting then
select count(1) into num from IPH_OPINION where IPHINFOID=:old.IPHINFOID;
if num >0 then
delete from IPHTEMP where IPHINFOID=:old.IPHINFOID;
end if;
end if;
end;我调试了,这个触发器不能使用,create or replace procedure p_up
as
begin
insert into IPHTEMP SELECT IPH_APPLY.IPHINFOID AS INFOID,IPH_APPLY.PERMITID AS SPSXBH,IPH_APPLY.PERMITID AS SPSXZXBH,IPH_APPLY.OPAPPLYNO AS YXTYWLSH,
ROW_NUMBER() OVER(PARTITION BY IPH_OPINION.IPHINFOID ORDER BY OPINIONDATE) AS SPBZH,
IPH_OPINION.STEPID AS SPHJDM,IPH_OPINION.STEPID AS SPHJMC,
IPH_OPINION.OPINIONUSER AS SPRXM,OPINIONUSER AS SPRZWDM,OPINIONUSER AS SPRZWMC,IPH_OPINION.OPINION AS SPYJ,IPH_OPINION.OPINIONDATE AS SPSJ
FROM IPH_APPLY
INNER JOIN IPH_OPINION ON IPH_APPLY.IPHINFOID=IPH_OPINION.IPHINFOID;
end;
这个存储过程达不到我的要求,我所需要的如下描述
有两个库,base1,base2,
我在base1中由表A,B,C,创建了一个视图,查询三个表中想要的数据。
我在base2中创建了一个新表,表的字段与视图的字段一致。现在我在视图上想创建一个触发器,
想实现这样的一个功能:就是在更新,插入,删除,A,B,C,三表中的数据时,(是更新,插入,删除原表。)
能通过视图同步到base2中新建的表上。
如果不用视图又怎么解决呢?谢谢