两个标相同的字段的数据表同时更新增加的触发器

CREATE TRIGGER gdq ON [dbo].[BZBOM_CPBOMGC]
FOR INSERT
AS
if exists(select CP_LB.ddh,CP_LB.hm,CP_LB.hh from (select cpxh,cpmc,cpbs,i.bm from BZBOM_CPZL left join inserted i on i.tip_id=BZBOM_CPZL.id) m,CP_LB where CP_LB.cpxh=m.cpxh and CP_LB.cpmc=m.cpmc and CP_LB.cplx=m.cpbs and CP_LB.bm=m.bm) ---
begin
select * into #temp0 from (select BZBOM_CPZL.id,cpxh,cpmc,cpbs as cplx,i.bm from BZBOM_CPZL right OUTER join inserted i on i.tip_id=BZBOM_CPZL.id) m ---模拟为第二张视图bz_bom
select * into #temp1 from (select distinct(cp_lb.ddh),cp_lb.hm,cp_lb.hh,#temp0.cpxh,#temp0.cpmc,#temp0.cplx,cp_lb.bm from #temp0 left join cp_lb on cp_lb.cpxh=#temp0.cpxh and cp_lb.cpmc=#temp0.cpmc and cp_lb.cplx=#temp0.cplx and cp_lb.bm=#temp0.bm ) a  ---把第二张表所查询到的数据放到临时表中备用
select * into #temp2 from (select cpxh,cpmc,cplx,#temp0.bm,bjmc,bjdz as dz,bjmj as mj,cpyl,clgg,bzdj as cpgj from inserted i left join #temp0 on #temp0.id=i.tip_id) b
select * into #temp3 from (select #temp1.ddh,#temp1.hm,#temp1.hh,#temp1.cpxh,#temp1.cpmc,#temp1.cplx,#temp1.bm,bjmc,#temp2.dz,#temp2.mj,#temp2.cpyl,#temp2.clgg,#temp2.cpgj from #temp2 left JOIN #temp1 on #temp1.cpxh=#temp2.cpxh and #temp1.cpmc=#temp2.cpmc and #temp1.cplx=#temp2.cplx and #temp1.bm=#temp2.bm) c

 

insert into CP_LB(ddh,hm,hh,cpxh,cpmc,cplx,bm,bjmc,dz,mj,cpyl,clgg,cpgj) select ddh,hm,hh,cpxh,cpmc,cplx,bm,bjmc,dz,mj,cpyl,clgg,cpgj from #temp3


end

 

 

展开阅读全文
博主设置当前文章不允许评论。

没有更多推荐了,返回首页