CREATE TABLE A ( COL1 NUMBER(2), COL2 NUMBER(2) );CREATE OR REPLACE TRIGGER trigger_name AFTER INSERT OR UPDATE OR DELETE ON A FOR EACH ROW DECLAREBEGIN IF INSERTING OR UPDATING OR DELETING THEN INSERT B(COL1,COL2) VALUES(:NEW.COL1,:NEW.COL2); END IF;
CREATE OR REPLACE TRIGGER "YOUR_USER"."SAME_BIU" BEFORE INSERT OR UPDATE ON "YOUR_USER"."YOUR_TABLE_A" FOR EACH ROW begin insert into your_table_b ( field1, field2, ..., fieldn) (select :new.field1, :new.field2, ..., :new.fieldn from your_table_a); end SAME_BIU;注意:此两表中主关键是不同的。
BlueskyWide(谈趣者)您好! 您讲的很好,不过我还有一个问题,须要判断一下table A 中是insert 还是update 还是delete 相应的table B 中也对相应的行进行insert update,delete. 谢谢!
可以用INSERTING,UPDATEING,DELETING if INSERTING then......
参考这个,实现同步添加、更新、删除记录 create or replace trigger tri_syn after insert or update or delete on a for each row begin if sqlcode=0 then case when inserting then insert into b values(:new.id,:new.sex,:new.dept); when updating then update b t set t.id=:new.id,t.sex=:new.sex,t.dept=:new.dept where t.id=:old.id; when deleting then delete from b t where t.id=:old.id; end case; end if; end tri_syn;
各位大哥! 我用了一个触发器,但是我table B 中有很多重复的行,请个位指教一,二。 CREATE OR REPLACE TRIGGER zh_test BEFORE INSERT OR UPDATE ON yz.clk FOR EACH ROW begin if INSERTING then insert into yz.zh_clk ( CID,CZ,CZJM,YZ,CH,ZIZ,HC,ZAZ,DZH,DZM,FXH,DJ,PM,XGD,SHR,SHRJM,FZH,FZM,PB,JSL,DDCC,DDRQ,DDSJ, YFBZ,HCBZ,ZYBZ,HPID,HPZL,CT,ZYCID,ZYID,CZBZ,CZLB,DFJ,ZDZH,KZBZ,ZLBZ,JYBZ,HSBZ,BQBZ,QTBZ, FYYM,SFH,HPH) (select :new.CID,:new.CZ,:new.CZJM,:new.YZ,:new.CH,:new.ZIZ,:new.HC,:new.ZAZ,:new.DZH,:new.DZM, :new.FXH,:new.DJ,:new.PM,:new.XGD,:new.SHR,:new.SHRJM,:new.FZH,:new.FZM,:new.PB,:new.JSL, :new.DDCC,:new.DDRQ,:new.DDSJ,:new.YFBZ,:new.HCBZ,:new.ZYBZ,:new.HPID,:new.HPZL,:new.CT, :new.ZYCID,:new.ZYID,:new.CZBZ,:new.CZLB,:new.DFJ,:new.ZDZH,:new.KZBZ,:new.ZLBZ,:new.JYBZ, :new.HSBZ,:new.BQBZ,:new.QTBZ,:new.FYYM,:new.SFH,:new.HPH from yz.clk); end if; if updating then update yz.zh_clk t set t.CID=:new.CID,t.CZ=:new.CZ,t.CZJM=:new.CZJM,t.YZ=:new.YZ,t.CH=:new.CH,t.ZIZ=:new.ZIZ, t.HC=:new.HC,t.ZAZ=:new.ZAZ,t.DZH=:new.DZH,t.DZM=:new.dzm,t.FXH=:new.FXH,t.DJ=:new.DJ, t.PM=:new.PM,t.XGD=:new.XGD,t.SHR=:new.SHR,t.SHRJM=:new.SHRJM,t.FZH=:new.FZH,t.FZM=:new.FZM, t.PB=:new.PB,t.JSL=:new.JSL,t.DDCC=:new.DDCC,t.DDRQ=:new.DDRQ,t.DDSJ=:new.DDSJ,t.YFBZ=:new.YFBZ, t.HCBZ=:new.HCBZ,t.ZYBZ=:new.ZYBZ,t.HPID=:new.HPID,t.HPZL=:new.HPZL,t.CT=:new.CT, t.ZYCID=:new.ZYCID,t.ZYID=:new.ZYID,t.CZBZ=:new.CZBZ,t.CZLB=:new.CZLB,t.DFJ=:new.DFJ, t.ZDZH=:new.ZDZH,t.KZBZ=:new.KZBZ,t.ZLBZ=:new.ZLBZ,t.JYBZ=:new.JYBZ,t.HSBZ=:new.HSBZ, t.BQBZ=:new.BQBZ,t.QTBZ=:new.QTBZ,t.FYYM=:new.FYYM,t.SFH=:new.SFH,t.HPH=:new.HPH where t.cid=:old.cid; end if; end zh_test;
"table B 中有很多重复的行", 是对的。 因为A表修改后,也insert到B表中。
CREATE OR REPLACE TRIGGER zh_gdsy BEFORE INSERT OR UPDATE ON yz.gdsy_copy FOR EACH ROW begin if INSERTING then insert into yz.zh_gdsy (GDM,SWH,CID,DRRQ,DRSJ) (select :new.GDM,:new.SWH,:new.CID,:new.DRRQ,:new.DRSJ from yz.gdsy); end if; if updating then update yz.zh_gdsy t set t.GDM=:new.GDM,t.SWh=:new.SWH,t.CID=:new.CID,t.DRRQ=:new.DRRQ, t.DRSJ=:new.DRSJ where t.GDM=:old.GDM and t.CID=:old.CID and t.DRRQ=:old.DRRQ; end if; end zh_gdsy; 后发现zh_gdsy 中有重复的记录!
(
COL1 NUMBER(2),
COL2 NUMBER(2)
);CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON A FOR EACH ROW
DECLAREBEGIN
IF INSERTING OR UPDATING OR DELETING THEN
INSERT B(COL1,COL2)
VALUES(:NEW.COL1,:NEW.COL2);
END IF;
END;
/
INSERTING、UPDATING、DELETING分别记录,这样会更清晰一些
INSERT OR UPDATE ON "YOUR_USER"."YOUR_TABLE_A" FOR EACH ROW
begin
insert into your_table_b (
field1,
field2,
...,
fieldn)
(select :new.field1,
:new.field2,
...,
:new.fieldn from your_table_a);
end SAME_BIU;注意:此两表中主关键是不同的。
您讲的很好,不过我还有一个问题,须要判断一下table A 中是insert 还是update 还是delete
相应的table B 中也对相应的行进行insert update,delete.
谢谢!
if INSERTING then......
参考这个,实现同步添加、更新、删除记录
create or replace trigger tri_syn
after insert or update or delete on a
for each row
begin
if sqlcode=0 then
case
when inserting then
insert into b values(:new.id,:new.sex,:new.dept);
when updating then
update b t set t.id=:new.id,t.sex=:new.sex,t.dept=:new.dept where
t.id=:old.id;
when deleting then
delete from b t where t.id=:old.id;
end case;
end if; end tri_syn;
我用了一个触发器,但是我table B 中有很多重复的行,请个位指教一,二。
CREATE OR REPLACE TRIGGER zh_test BEFORE
INSERT OR UPDATE ON yz.clk FOR EACH ROW
begin
if INSERTING then
insert into yz.zh_clk (
CID,CZ,CZJM,YZ,CH,ZIZ,HC,ZAZ,DZH,DZM,FXH,DJ,PM,XGD,SHR,SHRJM,FZH,FZM,PB,JSL,DDCC,DDRQ,DDSJ,
YFBZ,HCBZ,ZYBZ,HPID,HPZL,CT,ZYCID,ZYID,CZBZ,CZLB,DFJ,ZDZH,KZBZ,ZLBZ,JYBZ,HSBZ,BQBZ,QTBZ,
FYYM,SFH,HPH)
(select :new.CID,:new.CZ,:new.CZJM,:new.YZ,:new.CH,:new.ZIZ,:new.HC,:new.ZAZ,:new.DZH,:new.DZM, :new.FXH,:new.DJ,:new.PM,:new.XGD,:new.SHR,:new.SHRJM,:new.FZH,:new.FZM,:new.PB,:new.JSL,
:new.DDCC,:new.DDRQ,:new.DDSJ,:new.YFBZ,:new.HCBZ,:new.ZYBZ,:new.HPID,:new.HPZL,:new.CT,
:new.ZYCID,:new.ZYID,:new.CZBZ,:new.CZLB,:new.DFJ,:new.ZDZH,:new.KZBZ,:new.ZLBZ,:new.JYBZ,
:new.HSBZ,:new.BQBZ,:new.QTBZ,:new.FYYM,:new.SFH,:new.HPH from yz.clk);
end if;
if updating then
update yz.zh_clk t set t.CID=:new.CID,t.CZ=:new.CZ,t.CZJM=:new.CZJM,t.YZ=:new.YZ,t.CH=:new.CH,t.ZIZ=:new.ZIZ,
t.HC=:new.HC,t.ZAZ=:new.ZAZ,t.DZH=:new.DZH,t.DZM=:new.dzm,t.FXH=:new.FXH,t.DJ=:new.DJ,
t.PM=:new.PM,t.XGD=:new.XGD,t.SHR=:new.SHR,t.SHRJM=:new.SHRJM,t.FZH=:new.FZH,t.FZM=:new.FZM,
t.PB=:new.PB,t.JSL=:new.JSL,t.DDCC=:new.DDCC,t.DDRQ=:new.DDRQ,t.DDSJ=:new.DDSJ,t.YFBZ=:new.YFBZ,
t.HCBZ=:new.HCBZ,t.ZYBZ=:new.ZYBZ,t.HPID=:new.HPID,t.HPZL=:new.HPZL,t.CT=:new.CT,
t.ZYCID=:new.ZYCID,t.ZYID=:new.ZYID,t.CZBZ=:new.CZBZ,t.CZLB=:new.CZLB,t.DFJ=:new.DFJ,
t.ZDZH=:new.ZDZH,t.KZBZ=:new.KZBZ,t.ZLBZ=:new.ZLBZ,t.JYBZ=:new.JYBZ,t.HSBZ=:new.HSBZ,
t.BQBZ=:new.BQBZ,t.QTBZ=:new.QTBZ,t.FYYM=:new.FYYM,t.SFH=:new.SFH,t.HPH=:new.HPH
where t.cid=:old.cid;
end if;
end zh_test;
因为A表修改后,也insert到B表中。
INSERT OR UPDATE ON yz.gdsy_copy FOR EACH ROW
begin
if INSERTING then
insert into yz.zh_gdsy (GDM,SWH,CID,DRRQ,DRSJ)
(select
:new.GDM,:new.SWH,:new.CID,:new.DRRQ,:new.DRSJ from yz.gdsy);
end if;
if updating then
update yz.zh_gdsy t set
t.GDM=:new.GDM,t.SWh=:new.SWH,t.CID=:new.CID,t.DRRQ=:new.DRRQ,
t.DRSJ=:new.DRSJ
where t.GDM=:old.GDM and t.CID=:old.CID and t.DRRQ=:old.DRRQ;
end if;
end zh_gdsy;
后发现zh_gdsy 中有重复的记录!