vs2005C#
SqlBulkCopy sbc = new SqlBulkCopy(strCon.ToString(), SqlBulkCopyOptions.FireTriggers);
触发器为:
AFTER INSERT,UPDATE AS
DECLARE @GROUP VARCHAR(200) SELECT @GROUP= COMPANY FROM Statistic_DAY
IF (@GROUP NOT IN (SELECT DISTINCT COMGROUP FROM GROUPBUSSNES))
BEGIN
INSERT GROUPBUSSNES (
COMGROUP,
[EDGE],
[HWL],
[GPRS]
)
SELECT DISTINCT COMPANY,'1','1','1' FROM Statistic_DAY where COMPANY=@GROUP
END
else
begin
update GROUPBUSSNES set [EDGE]='1',[HWL]='1',[GPRS]='1' where COMGROUP=@GROUP
end 应该是SqlBulkCopy批量导入的时候的触发问题,一条条插入。没问题。请教,如何修改!
SqlBulkCopy sbc = new SqlBulkCopy(strCon.ToString(), SqlBulkCopyOptions.FireTriggers);
触发器为:
AFTER INSERT,UPDATE AS
DECLARE @GROUP VARCHAR(200) SELECT @GROUP= COMPANY FROM Statistic_DAY
IF (@GROUP NOT IN (SELECT DISTINCT COMGROUP FROM GROUPBUSSNES))
BEGIN
INSERT GROUPBUSSNES (
COMGROUP,
[EDGE],
[HWL],
[GPRS]
)
SELECT DISTINCT COMPANY,'1','1','1' FROM Statistic_DAY where COMPANY=@GROUP
END
else
begin
update GROUPBUSSNES set [EDGE]='1',[HWL]='1',[GPRS]='1' where COMGROUP=@GROUP
end 应该是SqlBulkCopy批量导入的时候的触发问题,一条条插入。没问题。请教,如何修改!
AFTER INSERT,UPDATE
AS
DECLARE @GROUP VARCHAR(200)
if exists(select 1 from inserted i,GROUPBUSSNES g where ....)--..... --这个地方加关联条件
update GROUPBUSSNES
set [EDGE]='1',[HWL]='1',[GPRS]='1'
from inserted i,GROUPBUSSNES g where 1=1
else
INSERT GROUPBUSSNES select COMGROUP,[EDGE],[HWL],[GPRS] from inserted i
where COMGROUP not in(SELECT DISTINCT COMGROUP FROM GROUPBUSSNES)
go
[id] [int] IDENTITY (1, 1) NOT NULL ,
[COMGROUP] [nvarchar] (200) NULL ,
[IPGPRS] [int] NULL ,
[IPVOICE] [int] NULL ,
[IPDATA] [int] NULL ,
[MESSAGE] [int] NULL ,
[EDGE] [int] NULL ,
[HWL] [int] NULL ,
[GPRS] [int] NULL ,
[REMARK] [varchar] (200) NULL ,
PRIMARY KEY (id)
)
GO
CREATE TABLE [dbo].[Statistic_DAY] (
[COMPANY] [varchar] (250) NULL ,
[CELL] [varchar] (200) NOT NULL ,
[SDATE] [smalldatetime] NOT NULL ,
[EDGE] [decimal](18, 3) NULL ,
[GPRS] [decimal](18, 3) NULL ,
[PDCH] [decimal](18, 3) NULL ,
[PDCHF] [decimal](18, 3) NULL ,
[HWL] [decimal](18, 3) NULL ,
[TYPE] [int] NULL ,
primary key(cell,sdate)
)
GO
要求就是在Statistic_DAY表中导入数据的时候,更新GROUPBUSSNES中的列COMGROUP=Statistic_DAY,插入EDGE,HWL,GPRS字段为‘1’,我上面写的句子,单独插入的可以用,但是碰到用SqlBulkCopy导入数据时,就不行了。已经加上SqlBulkCopyOptions.FireTriggers引发触发器,但是还是没作用。