----------------------
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