两个标相同的字段的数据表同时更新增加的触发器-优快云博客

本文介绍了一个SQL触发器的创建过程，该触发器在特定条件下将数据从一张表（BZBOM_CPZL）同步到另一张表（CP_LB）。通过复杂的SQL语句实现了数据的匹配与插入。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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