现在要求当字段(tjdh)值相同的时候,不再添加,而是更新相应的字段,会的朋友帮忙修改一下,谢谢
CREATE trigger reloadTT on xsyjupdate for updateas
begin
if update (mo)
begin
declare @m int
set @m=0
select @m=cast(mo as int) from inserted
if @m=1
insert into A2008XSYJ (tjdh,ryxm,cpmc,xl01,je01,lr01) select tjdh,ryxm,cpmc,xszl,zxse,xslr from nyxscpdl
if @m=2
insert into A2008XSYJ (tjdh,ryxm,cpmc,xl02,je02,lr02) select tjdh,ryxm,cpmc,xszl,zxse,xslr from nyxscpdl
if @m=3
insert into A2008XSYJ (tjdh,ryxm,cpmc,xl03,je03,lr03) select tjdh,ryxm,cpmc,xszl,zxse,xslr from nyxscpdl
……end
end
CREATE trigger reloadTT on xsyjupdate for updateas
begin
if update (mo)
begin
declare @m int
set @m=0
select @m=cast(mo as int) from inserted
if @m=1
insert into A2008XSYJ (tjdh,ryxm,cpmc,xl01,je01,lr01) select tjdh,ryxm,cpmc,xszl,zxse,xslr from nyxscpdl
if @m=2
insert into A2008XSYJ (tjdh,ryxm,cpmc,xl02,je02,lr02) select tjdh,ryxm,cpmc,xszl,zxse,xslr from nyxscpdl
if @m=3
insert into A2008XSYJ (tjdh,ryxm,cpmc,xl03,je03,lr03) select tjdh,ryxm,cpmc,xszl,zxse,xslr from nyxscpdl
……end
end
begin
if update (mo)
begin
declare @m int
set @m=0
select @m=cast(mo as int) from inserted
if @m=1
update A2008XSYJ--更新存在的
set nyxscpdl.ryxm=i.ryxm,nyxscpdl.cpmci=i.cpmci,nyxscpdl.xl01=i.xl01,nyxscpdl.je01=i.je01,nyxscpdl.lr01=i.lr01
from nyxscpdl,inserted i where nyxscpdl.tjdh=i.tjdh insert into A2008XSYJ (tjdh,ryxm,cpmc,xl01,je01,lr01) --插入没有的
select tjdh,ryxm,cpmc,xszl,zxse,xslr from nyxscpdl where not exists(select 1 from inserted where tjdh=nyxscpdl.tjdh)
只要tjdh字段值相同就更新,否则为增加,楼上的太复杂了吧,帮忙修改下,谢谢了。
if exists(……)
begin
update A2008XSYJ end
else insert into A2008XSYJ (tjdh,ryxm,cpmc,xl01,je01,lr01) select tjdh,ryxm,cpmc,xszl,zxse,xslr from nyxscpdl