启元商品资料数据同步

----------------------

USE [qcky]
GO
/****** Object:  Trigger [dbo].[goodsdoctir]    Script Date: 09/01/2014 16:19:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[goodsdoctir]
   ON  [dbo].[GOODSDOC]
   for INSERT,DELETE,UPDATE
AS 
BEGIN
if exists(select 1 from inserted) and exists(select 1 from deleted)--修改
   begin 
--if not exists(select 1 from qckyls..GOODSDOC a join inserted b on a.GoodsId = b.goodsid)
begin
insert into
qckyls..goodsdoc(EntId,GoodsId,GoodsCode)
select a.EntId,a.GoodsId,a.GoodsCode
from inserted a
where not exists(
select 1 from qckyls..goodsdoc b
where a.goodsid = b.goodsid
)
end
begin
declare @ProductName nvarchar(50)
declare @GoodsId char(11)
if exists(select 1 from sysobjects where name = '#abc')
begin
drop table #abc
end
select * into #abc from inserted
declare pcurr cursor for select fdname from TbStru where tbname = 'goodsdoc'
open pcurr  
fetch next from pcurr into @ProductName  
while (@@fetch_status = 0)
begin
if exists (select 1 from qckyls..TbStru where tbname = 'goodsdoc' and fdname = @ProductName)
begin
declare pcurr1 cursor for select goodsid from #abc
open pcurr1
fetch next from pcurr1 into @GoodsId
while (@@fetch_status = 0)
begin
exec('update qckyls..goodsdoc set '+@ProductName
+' = (select '
+@ProductName+' from #abc where goodsid = '''
+@GoodsId+''') where goodsid = '''+@GoodsId+'''')
fetch next from pcurr into @GoodsId
end
close pcurr1
deallocate pcurr1
end
fetch next from pcurr into @ProductName  
end  
close pcurr
deallocate pcurr
end
begin
update qckyls..goodsdoc set entid = 'E16ZEQ2CAL7' 
where goodsid in (select GoodsId from #abc)
end  
   end
  else if exists(select * from inserted) --新增
   begin 
--if not exists(select 1 from qckyls..GOODSDOC a join inserted b on a.GoodsId = b.goodsid)
begin
insert into
qckyls..goodsdoc(EntId,GoodsId)
select a.EntId,a.GoodsId
from inserted a
where  not exists(
select 1 from qckyls..goodsdoc b
where a.goodsid = b.goodsid
)
end
begin
declare @ProductName1 nvarchar(50)
declare @GoodsId1 char(11)
if exists(select 1 from sysobjects where name = '#bcd')
begin
drop table #bcd
end
select * into #bcd from inserted

declare pcurr cursor for select fdname from TbStru where tbname = 'goodsdoc'
open pcurr  
fetch next from pcurr into @ProductName1  
while (@@fetch_status = 0)
begin
if exists (select 1 from qckyls..TbStru where tbname = 'goodsdoc' and fdname = @ProductName1)
begin
declare pcurr1 cursor for select goodsid from #bcd
open pcurr1
fetch next from pcurr1 into @GoodsId1
while (@@fetch_status = 0)
begin
exec('update qckyls..goodsdoc set '+@ProductName1
+' = (select '
+@ProductName1+' from #bcd where goodsid = '''+@GoodsId1+''')'
+'where goodsid = '''+@GoodsId1+'''')
fetch next from pcurr into @GoodsId1
end
close pcurr1
deallocate pcurr1
end
fetch next from pcurr into @ProductName1
end  
close pcurr
deallocate pcurr
end
begin
update qckyls..goodsdoc set entid = 'E16ZEQ2CAL7' 
where goodsid in (select GoodsId from #bcd)
end
   end
END

--------------------------------------goodsattr

USE [qcky]
GO
/****** Object:  Trigger [dbo].[goodsattrtir]    Script Date: 09/01/2014 16:20:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[goodsattrtir]
   ON  [dbo].[GOODSATTR]
   for INSERT,DELETE,UPDATE
AS 
BEGIN
if exists(select 1 from inserted) and exists(select 1 from deleted)--修改
   begin 
if not exists(select 1 from qckyls..GOODSAttr a join inserted b on a.GoodsId = b.goodsid)
begin
insert into 
qckyls..goodsdoc(EntId,GoodsId)
select a.EntId,a.GoodsId from inserted a
left join qckyls..goodsdoc b on a.goodsid = b.goodsid
where b.goodsid is null
end
begin
declare @ProductName nvarchar(50)
if exists(select 1 from sysobjects where name = '#abc')
begin
drop table #abc
end
select * into #abc from inserted

declare pcurr cursor for select fdname from TbStru where tbname = 'goodsAttr'
open pcurr  
fetch next from pcurr into @ProductName  
while (@@fetch_status = 0)
begin
if exists (select 1 from qckyls..TbStru where tbname = 'goodsattr' and fdname = @ProductName)
begin
exec('update qckyls..goodsAttr set '+@ProductName+' = (select top 1 '
+@ProductName+' from #abc) where goodsid in (select GoodsId from #abc)')
end
fetch next from pcurr into @ProductName  
end  
close pcurr
deallocate pcurr
end
begin
update qckyls..goodsattr set entid = 'E16ZEQ2CAL7' 
where goodsid in (select GoodsId from #abc)
end
   end
  else if exists(select * from inserted) --新增
   begin 
if not exists(select 1 from qckyls..GOODSAttr a join inserted b on a.GoodsId = b.goodsid)
begin
insert into 
qckyls..goodsdoc(EntId,GoodsId)
select a.EntId,a.GoodsId from inserted a
left join qckyls..goodsdoc b on a.goodsid = b.goodsid
where b.goodsid is null
end
begin
declare @ProductName1 nvarchar(50)
if exists(select 1 from sysobjects where name = '#bcd')
begin
drop table #bcd
end
select * into #bcd from inserted

declare pcurr cursor for select fdname from TbStru where tbname = 'goodsattr'
open pcurr  
fetch next from pcurr into @ProductName1  
while (@@fetch_status = 0)
begin
if exists (select 1 from qckyls..TbStru where tbname = 'goodsattr' and fdname = @ProductName1)
begin
exec('update qckyls..goodsattr set '+@ProductName1+' = (select top 1 '
+@ProductName1+' from #bcd) where goodsid in (select GoodsId from #bcd)')
end
fetch next from pcurr into @ProductName1
end  
close pcurr
deallocate pcurr
end
begin
update qckyls..goodsattr set entid = 'E16ZEQ2CAL7' 
where goodsid in (select GoodsId from #bcd)
end  
   end
END

------------------------pgprice

USE [qcky]
GO
/****** Object:  Trigger [dbo].[pgpricetir]    Script Date: 09/01/2014 16:20:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[pgpricetir]
   ON  [dbo].[PGPRICE]
   for INSERT,DELETE,UPDATE
AS 
BEGIN
if exists(select 1 from inserted) and exists(select 1 from deleted)--修改


   begin 
if not exists(select 1 from qckyls..pgprice a join inserted b on a.GoodsId = b.goodsid and a.packid = b.packid)
begin
insert into 
qckyls..pgprice(EntId,GoodsId,packid)
select a.EntId,a.GoodsId,a.packid from inserted a
left join qckyls..pgprice b on a.goodsid = b.goodsid and a.packid = b.packid
where b.goodsid is null
end  
begin
declare @ProductName nvarchar(50)
if exists(select 1 from sysobjects where name = '#abc')
begin
drop table #abc
end
select * into #abc from inserted

declare pcurr cursor for select fdname from TbStru where tbname = 'pgprice'
open pcurr  
fetch next from pcurr into @ProductName  
while (@@fetch_status = 0)
begin
if exists (select 1 from qckyls..TbStru where tbname = 'pgprice' and fdname = @ProductName)
begin
exec('update qckyls..pgprice set '+@ProductName+' = (select top 1 '
+@ProductName+' from #abc) where goodsid+packid in (select GoodsId+packid from #abc)')
end
fetch next from pcurr into @ProductName  
end  
close pcurr
deallocate pcurr
end
begin
update qckyls..pgprice set entid = 'E16ZEQ2CAL7' 
where goodsid+PackId in (select GoodsId+PackId from #abc)
end
   end
  else if exists(select * from inserted) --新增
   begin 
if not exists(select 1 from qckyls..pgprice a join inserted b on a.GoodsId = b.goodsid)
begin
insert into 
qckyls..pgprice(EntId,GoodsId,packid)
select a.EntId,a.GoodsId,a.packid from inserted a
left join qckyls..pgprice b on a.goodsid = b.goodsid and a.packid = b.packid
where b.goodsid is null
end
begin
declare @ProductName1 nvarchar(50)
if exists(select 1 from sysobjects where name = '#bcd')
begin
drop table #bcd
end
select * into #bcd from inserted

declare pcurr cursor for select fdname from TbStru where tbname = 'pgprice'
open pcurr  
fetch next from pcurr into @ProductName1  
while (@@fetch_status = 0)
begin
if exists (select 1 from qckyls..TbStru where tbname = 'pgprice' and fdname = @ProductName1)
begin
exec('update qckyls..pgprice set '+@ProductName1+' = (select top 1 '
+@ProductName1+' from #bcd) where goodsid+packid in (select GoodsId+packid from #bcd)')
end
fetch next from pcurr into @ProductName1
end  
close pcurr
deallocate pcurr
end
begin
update qckyls..pgprice set entid = 'E16ZEQ2CAL7' 
where goodsid+packid in (select GoodsId+packId from #bcd)
end
   end
END