数据来源于表A;表B只做修改操作,表C做表B的变动日志记录;
1)检索表A有insert或update操作时对表B做upate操作,条件是: A.CSGBH和B. PM_MP_MACHINE_CSGID相匹配,更新除B.PM_MP_MACHINE_WSJID、B.PM_MP_MACHINE_CSGID、B.DC_PB_MACHINEGROUPID之外的所有字段;表C做相同的insert操作,数据来源表B。
2)提示:表A、表B的数据都只有32条记录,不会增加减少;表C则根据表B的变动而增加。表A : MT_DC_PB_CSG
表B : MT_DC_PB_CSGCHANGE
表C: MT_DC_PB_CSGCHANGELOG表A:
CGCLBL NUMBER
CGKSSJ VARCHAR2(20)
CGPCH VARCHAR2(20)
CGJSSJ VARCHAR2(20)
JGGDH VARCHAR2(20)
JGJLBL NUMBER
JGKSSJ VARCHAR2(20)
JGJSSJ VARCHAR2(20)
JGZT VARCHAR2(20)
JGPH VARCHAR2(20)
CSGBH VARCHAR2(20)表B: 数据来源表A/条件匹配
PM_MP_MACHINE_WSJID VARCHAR2(20) <--不变
PM_MP_MACHINE_CSGID VARCHAR2(20) <--匹配A.CSGBH
DC_PB_MACHINEGROUPID VARCHAR2(20) <--不变
JGGDH VARCHAR2(20) <--A.JGGDH
JGPH VARCHAR2(20) <--A.JGPH
CGPCH VARCHAR2(20) <--A.CGPCH
CGKSSJ VARCHAR2(20) <--A.CGKSSJ
CGJSSJ VARCHAR2(20) <--A.CGJSSJ
JGL VARCHAR2(20) <--空
CCL VARCHAR2(20) <--空
JGZT VARCHAR2(20) <--A.JGZT
CGZT VARCHAR2(20) <--空表C: 数据来源表B
PM_MP_MACHINE_WSJID VARCHAR2(20)
PM_MP_MACHINE_CSGID VARCHAR2(20)
DC_PB_MACHINEGROUPID VARCHAR2(20)
JGGDH VARCHAR2(20)
JGPH VARCHAR2(20)
CGPCH VARCHAR2(20)
CGKSSJ VARCHAR2(20)
CGJSSJ VARCHAR2(20)
JGL VARCHAR2(20)
CCL VARCHAR2(20)
JGZT VARCHAR2(20)
CGZT VARCHAR2(20)
HP_ID VARCHAR2(36) 〈-- 不唯一值
RECORDTIME VARCHAR2(20) 〈-- 记录日期时间
1)检索表A有insert或update操作时对表B做upate操作,条件是: A.CSGBH和B. PM_MP_MACHINE_CSGID相匹配,更新除B.PM_MP_MACHINE_WSJID、B.PM_MP_MACHINE_CSGID、B.DC_PB_MACHINEGROUPID之外的所有字段;表C做相同的insert操作,数据来源表B。
2)提示:表A、表B的数据都只有32条记录,不会增加减少;表C则根据表B的变动而增加。表A : MT_DC_PB_CSG
表B : MT_DC_PB_CSGCHANGE
表C: MT_DC_PB_CSGCHANGELOG表A:
CGCLBL NUMBER
CGKSSJ VARCHAR2(20)
CGPCH VARCHAR2(20)
CGJSSJ VARCHAR2(20)
JGGDH VARCHAR2(20)
JGJLBL NUMBER
JGKSSJ VARCHAR2(20)
JGJSSJ VARCHAR2(20)
JGZT VARCHAR2(20)
JGPH VARCHAR2(20)
CSGBH VARCHAR2(20)表B: 数据来源表A/条件匹配
PM_MP_MACHINE_WSJID VARCHAR2(20) <--不变
PM_MP_MACHINE_CSGID VARCHAR2(20) <--匹配A.CSGBH
DC_PB_MACHINEGROUPID VARCHAR2(20) <--不变
JGGDH VARCHAR2(20) <--A.JGGDH
JGPH VARCHAR2(20) <--A.JGPH
CGPCH VARCHAR2(20) <--A.CGPCH
CGKSSJ VARCHAR2(20) <--A.CGKSSJ
CGJSSJ VARCHAR2(20) <--A.CGJSSJ
JGL VARCHAR2(20) <--空
CCL VARCHAR2(20) <--空
JGZT VARCHAR2(20) <--A.JGZT
CGZT VARCHAR2(20) <--空表C: 数据来源表B
PM_MP_MACHINE_WSJID VARCHAR2(20)
PM_MP_MACHINE_CSGID VARCHAR2(20)
DC_PB_MACHINEGROUPID VARCHAR2(20)
JGGDH VARCHAR2(20)
JGPH VARCHAR2(20)
CGPCH VARCHAR2(20)
CGKSSJ VARCHAR2(20)
CGJSSJ VARCHAR2(20)
JGL VARCHAR2(20)
CCL VARCHAR2(20)
JGZT VARCHAR2(20)
CGZT VARCHAR2(20)
HP_ID VARCHAR2(36) 〈-- 不唯一值
RECORDTIME VARCHAR2(20) 〈-- 记录日期时间
after insert or update on tab_a
for each row
begin
update tab_b set JGGDH=:new.JGGDH,
JGPH=:new.JGPH,
CGPCH=:new.CGPCH,
CGKSSJ=:new.CGKSSJ,
CGJSSJ=:new.CGJSSJ,
JGZT=:new.A.JGZT
where PM_MP_MACHINE_CSGID=:new.CSGBH和B;
end;
/create or repace trigger tab_b_trigger
after update on tab_b
for each row
begin
insert into tab_c
(
PM_MP_MACHINE_WSJID,PM_MP_MACHINE_CSGID,DC_PB_MACHINEGROUPID,JGGDH,
JGPH,CGPCH,CGKSSJ,CGJSSJ,JGL,CCL,JGZT,CGZT,HP_ID,RECORDTIME
)
values
(
:new.PM_MP_MACHINE_WSJID,:new.PM_MP_MACHINE_CSGID,:new.DC_PB_MACHINEGROUPID,:new.JGGDH,
:new.JGPH,:new.CGPCH,:new.CGKSSJ,:new.CGJSSJ,:new.JGL,:new.CCL,:new.JGZT,:new.CGZT,
:new.HP_ID,:new.RECORDTIME,不唯一值哪里来,to_char(sysdate,'yyyymmdd hh24:mi:ss')
);end;
/
after insert or update on MT_DC_PB_CSG
for each row
begin
update MT_DC_PB_CSGCHANGE set JGGDH=:new.JGGDH,
JGPH=:new.JGPH,
CGPCH=:new.CGPCH,
CGKSSJ=:new.CGKSSJ,
CGJSSJ=:new.CGJSSJ,
JGZT=:new.A.JGZT
where PM_MP_MACHINE_CSGID=:new.CSGBH和B;
end;
/create or repace trigger tab_b_trigger
after update on MT_DC_PB_CSGCHANGE
for each row
begin
insert MT_DC_PB_CSGCHANGELOG
(
PM_MP_MACHINE_WSJID,PM_MP_MACHINE_CSGID,DC_PB_MACHINEGROUPID,JGGDH,
JGPH,CGPCH,CGKSSJ,CGJSSJ,JGL,CCL,JGZT,CGZT,HP_ID,RECORDTIME
)
values
(
:new.PM_MP_MACHINE_WSJID,:new.PM_MP_MACHINE_CSGID,:new.DC_PB_MACHINEGROUPID,:new.JGGDH,
:new.JGPH,:new.CGPCH,:new.CGKSSJ,:new.CGJSSJ,:new.JGL,:new.CCL,:new.JGZT,:new.CGZT,
:new.HP_ID,:new.RECORDTIME,'不唯一值哪里来',to_char(sysdate,'yyyymmdd hh24:mi:ss')
);end;
/
表c的字段HP_ID的值从哪里得?
自已在修改吧
在触发器中产生不唯一值!请写一下! :)
INCREMENT BY 1
START WITH 1
MAXVALUE 999999
/create or replace trigger tab_a_trigger
after insert or update
on MT_DC_PB_CSG
for each row
begin
update MT_DC_PB_CSGCHANGE
set JGGDH=:new.JGGDH,
JGPH=:new.JGPH,
CGPCH=:new.CGPCH,
CGKSSJ=:new.CGKSSJ,
CGJSSJ=:new.CGJSSJ,
JGZT=:new.A.JGZT
where PM_MP_MACHINE_CSGID=:new.CSGBH和B;
end;
/create or repace trigger tab_b_trigger
after update
on MT_DC_PB_CSGCHANGE
for each row
begin
insert MT_DC_PB_CSGCHANGELOG
(
PM_MP_MACHINE_WSJID,PM_MP_MACHINE_CSGID,DC_PB_MACHINEGROUPID,JGGDH,
JGPH,CGPCH,CGKSSJ,CGJSSJ,JGL,CCL,JGZT,CGZT,HP_ID,RECORDTIME
)
values
(
:new.PM_MP_MACHINE_WSJID,:new.PM_MP_MACHINE_CSGID,:new.DC_PB_MACHINEGROUPID,
:new.JGGDH,:new.JGPH,:new.CGPCH,:new.CGKSSJ,:new.CGJSSJ,:new.JGL,:new.CCL,
:new.JGZT,:new.CGZT,:new.HP_ID,:new.RECORDTIME,seq_01.nextval,
to_char(sysdate,'yyyymmdd hh24:mi:ss')
);end;
/
INCREMENT BY 1
START WITH 1
MAXVALUE 999999
/create or replace trigger tab_a_trigger
after insert or update
on MT_DC_PB_CSG
for each row
begin
update MT_DC_PB_CSGCHANGE
set JGGDH=:new.JGGDH,
JGPH=:new.JGPH,
CGPCH=:new.CGPCH,
CGKSSJ=:new.CGKSSJ,
CGJSSJ=:new.CGJSSJ,
JGZT=:new.A.JGZT
where PM_MP_MACHINE_CSGID=:new.CSGBH;
end;
/create or repace trigger tab_b_trigger
after update
on MT_DC_PB_CSGCHANGE
for each row
begin
insert MT_DC_PB_CSGCHANGELOG
(
PM_MP_MACHINE_WSJID,PM_MP_MACHINE_CSGID,DC_PB_MACHINEGROUPID,JGGDH,
JGPH,CGPCH,CGKSSJ,CGJSSJ,JGL,CCL,JGZT,CGZT,HP_ID,RECORDTIME
)
values
(
:new.PM_MP_MACHINE_WSJID,:new.PM_MP_MACHINE_CSGID,:new.DC_PB_MACHINEGROUPID,
:new.JGGDH,:new.JGPH,:new.CGPCH,:new.CGKSSJ,:new.CGJSSJ,:new.JGL,:new.CCL,
:new.JGZT,:new.CGZT,:new.HP_ID,:new.RECORDTIME,seq_01.nextval,
to_char(sysdate,'yyyymmdd hh24:mi:ss')
);end;
/
请去第2帖 发个话
http://community.csdn.net/Expert/topic/4777/4777933.xml?temp=.4494135