问题:
在批量插入数据的时候数据丢失(该表有触发器),单条数据插入的时候是没有问题。
还请各位高手帮忙看看分析下,,小弟在此谢过。。触发器代码:
CREATE TRIGGER "ajk_insert_109659440" ON "dbo"."ajk"
FOR insert AS --创建触发器
BEGIN
declare @std1 varchar(200) --定义变量,用来保存从游标中取得的值
declare cursor_insert cursor for select "_Identify" from inserted
open cursor_insert ---打开游标
fetch cursor_insert into @std1 while @@fetch_status=0 ---循环读取值
begin
exec BCG_QueueMsg @MsgTypeName = 'default_ajk_do', @MsgServiceName ='exp' , @OperationType ='insert' , @MsgParam1= @std1 fetch cursor_insert into @std1
end
close cursor_insert deallocate cursor_insert
END 存储过程代码:create proc BCG_QueueMsg
@MsgTypeName varchar(255),
@MsgServiceName varchar(255),
@OperationType varchar(10),
@MsgParam1 varchar(255) = NULL,
@MsgParam2 varchar(255) = NULL,
@MsgParam3 varchar(255) = NULL,
@MsgParam4 varchar(255) = NULL,
@MsgParam5 varchar(255) = NULL,
@MsgParam6 varchar(255) = NULL,
@MsgParam7 varchar(255) = NULL,
@MsgParam8 varchar(255) = NULL,
@MsgParam9 varchar(255) = NULL,
@MsgParam10 varchar(255) = NULL as BEGIN declare @ErrorCode int,
@ErrorMessage varchar(255),
@NumRows int declare @MsgServiceId int,
@MsgTypeId int select @ErrorCode = 0
select @MsgTypeId = MsgTypeId from BCG_MsgType where MsgTypeName = @MsgTypeName if (@@rowcount = 0) BEGIN return 1 END
select @MsgServiceId = MsgServiceId from BCG_MsgService where MsgServiceName = @MsgServiceName if (@@rowcount = 0) BEGIN return 1 END
insert BCG_Msg ( MsgServiceId, MsgTypeId, MsgQueuedAt, MsgStatus, MsgDeliveredAt, MsgFailedText,OperationType, MsgParam1, MsgParam2, MsgParam3, MsgParam4, MsgParam5, MsgParam6, MsgParam7, MsgParam8, MsgParam9, MsgParam10, MsgId )
select @MsgServiceId, @MsgTypeId, getdate(), 'NEW', NULL, NULL,@OperationType, @MsgParam1, @MsgParam2, @MsgParam3, @MsgParam4, @MsgParam5, @MsgParam6, @MsgParam7, @MsgParam8, @MsgParam9, @MsgParam10,NEWID()
select @ErrorCode = @@error, @NumRows = @@rowcount return @ErrorCode END
GO
在批量插入数据的时候数据丢失(该表有触发器),单条数据插入的时候是没有问题。
还请各位高手帮忙看看分析下,,小弟在此谢过。。触发器代码:
CREATE TRIGGER "ajk_insert_109659440" ON "dbo"."ajk"
FOR insert AS --创建触发器
BEGIN
declare @std1 varchar(200) --定义变量,用来保存从游标中取得的值
declare cursor_insert cursor for select "_Identify" from inserted
open cursor_insert ---打开游标
fetch cursor_insert into @std1 while @@fetch_status=0 ---循环读取值
begin
exec BCG_QueueMsg @MsgTypeName = 'default_ajk_do', @MsgServiceName ='exp' , @OperationType ='insert' , @MsgParam1= @std1 fetch cursor_insert into @std1
end
close cursor_insert deallocate cursor_insert
END 存储过程代码:create proc BCG_QueueMsg
@MsgTypeName varchar(255),
@MsgServiceName varchar(255),
@OperationType varchar(10),
@MsgParam1 varchar(255) = NULL,
@MsgParam2 varchar(255) = NULL,
@MsgParam3 varchar(255) = NULL,
@MsgParam4 varchar(255) = NULL,
@MsgParam5 varchar(255) = NULL,
@MsgParam6 varchar(255) = NULL,
@MsgParam7 varchar(255) = NULL,
@MsgParam8 varchar(255) = NULL,
@MsgParam9 varchar(255) = NULL,
@MsgParam10 varchar(255) = NULL as BEGIN declare @ErrorCode int,
@ErrorMessage varchar(255),
@NumRows int declare @MsgServiceId int,
@MsgTypeId int select @ErrorCode = 0
select @MsgTypeId = MsgTypeId from BCG_MsgType where MsgTypeName = @MsgTypeName if (@@rowcount = 0) BEGIN return 1 END
select @MsgServiceId = MsgServiceId from BCG_MsgService where MsgServiceName = @MsgServiceName if (@@rowcount = 0) BEGIN return 1 END
insert BCG_Msg ( MsgServiceId, MsgTypeId, MsgQueuedAt, MsgStatus, MsgDeliveredAt, MsgFailedText,OperationType, MsgParam1, MsgParam2, MsgParam3, MsgParam4, MsgParam5, MsgParam6, MsgParam7, MsgParam8, MsgParam9, MsgParam10, MsgId )
select @MsgServiceId, @MsgTypeId, getdate(), 'NEW', NULL, NULL,@OperationType, @MsgParam1, @MsgParam2, @MsgParam3, @MsgParam4, @MsgParam5, @MsgParam6, @MsgParam7, @MsgParam8, @MsgParam9, @MsgParam10,NEWID()
select @ErrorCode = @@error, @NumRows = @@rowcount return @ErrorCode END
GO
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货