set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <BOB>
-- Create date: <>
-- Description: <从虚表导入实表 研究生资料>
-- =============================================
ALTER PROCEDURE [dbo].[InsertFinishedPracticalTeachingWorkloads_FromVitual]
@Creator nvarchar(50)
AS
begin
declare @DelID int --需要在虚表中删除的数据ID值
declare @BackID int
declare @PracticeCategory int
declare @Definition nvarchar(50)
declare @StartTime datetime
declare @EndTime datetime
declare @Professional nvarchar(100)
declare @Number int
declare @Coeffcient float
declare @ScheduleClassHours float
declare @StandardClassHours float
declare @StaffID int
declare @Re nvarchar(1000)--以上参数是可以由外部传入的参数
declare @StaffWorkloadID int
declare @WorkloadType int
declare @State int
declare @TableName nvarchar(80)
declare @WorkTaskID int
declare @StaffName nvarchar(100)
declare @DepartmentID int
set @WorkloadType =113
set @State=1
DECLARE @TranStarted bit
SET @TranStarted = 0 IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SET ANSI_WARNINGS OFF
declare @AllIsRightData int --所有虚表数据是否正确值
declare @IsRightData int --确认导入时执行数据检查set @AllIsRightData=1
declare CheckData cursor for
select IsRight from FinishedPracticalTeachingWorkloads_Virtual where Creator=@Creatoropen CheckData
fetch from CheckData into @IsRightData
while(@@fetch_status=0)
begin
if(@IsRightData=0)
BEGIN
set @AllIsRightData=0
RAISERROR('还存在未修改完的错误数据,请检查后重新导入!',16,1)
Break
END
fetch next from CheckData into @IsRightData
end
close CheckData
deallocate CheckDatadeclare @LoginStaffName nvarchar(20)
select @LoginStaffName=Contact.Name from Contact inner join Staff ON
Contact.ContactID=Staff.ContactID WHERE Staff.StaffID=@Creatorif(@AllIsRightData=1)
BEGIN
select @WorkTaskID=ID from WorkTasks
where ParentID is null and TaskStatus in (1,2)
declare SureInsertAll cursor for
select ID,PracticeCategory,Definition,StartTime,EndTime,Professional,Number,Coeffcient,ScheduleClassHours,StandardClassHours,StaffName,DepartmentID,StaffID
from FinishedPracticalTeachingWorkloads_Virtual where Creator=@Creator
set transaction isolation level serializable
begin tran
alter table StaffWorkLoads----------------------
disable trigger InsertSumTeachWorkloads----------
alter table FinishedPracticalTeachingWorkloads----------------------
disable trigger SumFinishedPracticalTeachingWorkloadsInsert----------
open SureInsertAll
fetch from SureInsertAll into @DelID,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,@StaffName,@DepartmentID,@StaffID
while(@@fetch_status=0)
BEGIN select @StaffID=StaffID from staff inner join contact
on staff.contactid=contact.contactid
where contact.name=@StaffName and contact.departmentid=@DepartmentID
if exists(
select ID from StaffWorkLoads
where WorkTaskID=@WorkTaskID and WorkloadType=@WorkloadType and StaffID=@StaffID
)
begin
select @StaffWorkloadID=ID from StaffWorkLoads
where WorkTaskID=@WorkTaskID and WorkloadType=@WorkloadType and StaffID=@StaffID
endelse
begin
insert into StaffWorkLoads(StaffID,WorkTaskID,WorkloadType,Workload,State,UsedFlag,Creator,CreateTime,Editor,EditTime,Re)
values (@StaffID,@WorkTaskID,@WorkloadType,0,1,1,@Creator,getdate(),@Creator,getdate(),null)
set @StaffWorkloadID=IDENT_CURRENT('StaffWorkLoads')
IF( @@ERROR <> 0 )
GOTO Cleanup
endinsert into FinishedPracticalTeachingWorkloads
(
StaffWorkloadID,WorkloadType,PracticeCategory,Class,StartTime,EndTime,Professional,Number,Coeffcient,ScheduleClassHours,StandardClassHours,State,UsedFlag,Creator,CreateTime,Editor,EditTime,
Re,DataOrigin
)
values
(
@StaffWorkloadID,@WorkloadType,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,2,1,@Creator,getdate(),@Creator,getdate(),
null,1
)
set @BackID=IDENT_CURRENT('FinishedPracticalTeachingWorkloads')
set @TableName='FinishedPracticalTeachingWorkloads'-- 更新工作流数据。
DECLARE @InstanceID uniqueidentifier
SET @InstanceID = NEWID()
INSERT INTO eIvy_WorkflowInstances
(ID, WorkflowName, EntityID, CurrentState)
VALUES
(@InstanceID, @TableName, @BackID, 2)INSERT INTO eIvy_WorkflowInstanceTracings
(TracingID, InstanceID, Transit, Executer, ExecuteTime, Re)
VALUES
(NEWID(), @InstanceID, 11,@LoginStaffName, GETDATE(), NULL)
IF( @@ERROR <> 0 )
GOTO Cleanup/*insert into WorkloadAssign
(Entity,EntityID,StaffID,Workload,SelfRank,State,UsedFlag,Creator,CreateTime,Editor,EditTime,Re)
values
(@TableName,@BackID,@StaffID,0,1,1,1,@Creator,getdate(),@Creator,getdate(),@Re)*/delete from FinishedPracticalTeachingWorkloads_Virtual where ID=@DelID
IF( @@ERROR <> 0 )
GOTO Cleanup
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
ENDfetch next from SureInsertAll into @DelID,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,@StaffName,@DepartmentID,@StaffID
END
close SureInsertAll
deallocate SureInsertAll
alter table FinishedPracticalTeachingWorkloads-----
enable trigger SumFinishedPracticalTeachingWorkloadsInsert-----
alter table StaffWorkLoads--------------------
enable trigger InsertSumTeachWorkloads----------------commit tran
END
Cleanup: IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END RETURN -1
end这段代码为什么插入到虚表的时候是截断的插入的呀,不是连续插入的,导致不能一次性吧数据全部插入SQL事务
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <BOB>
-- Create date: <>
-- Description: <从虚表导入实表 研究生资料>
-- =============================================
ALTER PROCEDURE [dbo].[InsertFinishedPracticalTeachingWorkloads_FromVitual]
@Creator nvarchar(50)
AS
begin
declare @DelID int --需要在虚表中删除的数据ID值
declare @BackID int
declare @PracticeCategory int
declare @Definition nvarchar(50)
declare @StartTime datetime
declare @EndTime datetime
declare @Professional nvarchar(100)
declare @Number int
declare @Coeffcient float
declare @ScheduleClassHours float
declare @StandardClassHours float
declare @StaffID int
declare @Re nvarchar(1000)--以上参数是可以由外部传入的参数
declare @StaffWorkloadID int
declare @WorkloadType int
declare @State int
declare @TableName nvarchar(80)
declare @WorkTaskID int
declare @StaffName nvarchar(100)
declare @DepartmentID int
set @WorkloadType =113
set @State=1
DECLARE @TranStarted bit
SET @TranStarted = 0 IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
SET ANSI_WARNINGS OFF
declare @AllIsRightData int --所有虚表数据是否正确值
declare @IsRightData int --确认导入时执行数据检查set @AllIsRightData=1
declare CheckData cursor for
select IsRight from FinishedPracticalTeachingWorkloads_Virtual where Creator=@Creatoropen CheckData
fetch from CheckData into @IsRightData
while(@@fetch_status=0)
begin
if(@IsRightData=0)
BEGIN
set @AllIsRightData=0
RAISERROR('还存在未修改完的错误数据,请检查后重新导入!',16,1)
Break
END
fetch next from CheckData into @IsRightData
end
close CheckData
deallocate CheckDatadeclare @LoginStaffName nvarchar(20)
select @LoginStaffName=Contact.Name from Contact inner join Staff ON
Contact.ContactID=Staff.ContactID WHERE Staff.StaffID=@Creatorif(@AllIsRightData=1)
BEGIN
select @WorkTaskID=ID from WorkTasks
where ParentID is null and TaskStatus in (1,2)
declare SureInsertAll cursor for
select ID,PracticeCategory,Definition,StartTime,EndTime,Professional,Number,Coeffcient,ScheduleClassHours,StandardClassHours,StaffName,DepartmentID,StaffID
from FinishedPracticalTeachingWorkloads_Virtual where Creator=@Creator
set transaction isolation level serializable
begin tran
alter table StaffWorkLoads----------------------
disable trigger InsertSumTeachWorkloads----------
alter table FinishedPracticalTeachingWorkloads----------------------
disable trigger SumFinishedPracticalTeachingWorkloadsInsert----------
open SureInsertAll
fetch from SureInsertAll into @DelID,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,@StaffName,@DepartmentID,@StaffID
while(@@fetch_status=0)
BEGIN select @StaffID=StaffID from staff inner join contact
on staff.contactid=contact.contactid
where contact.name=@StaffName and contact.departmentid=@DepartmentID
if exists(
select ID from StaffWorkLoads
where WorkTaskID=@WorkTaskID and WorkloadType=@WorkloadType and StaffID=@StaffID
)
begin
select @StaffWorkloadID=ID from StaffWorkLoads
where WorkTaskID=@WorkTaskID and WorkloadType=@WorkloadType and StaffID=@StaffID
endelse
begin
insert into StaffWorkLoads(StaffID,WorkTaskID,WorkloadType,Workload,State,UsedFlag,Creator,CreateTime,Editor,EditTime,Re)
values (@StaffID,@WorkTaskID,@WorkloadType,0,1,1,@Creator,getdate(),@Creator,getdate(),null)
set @StaffWorkloadID=IDENT_CURRENT('StaffWorkLoads')
IF( @@ERROR <> 0 )
GOTO Cleanup
endinsert into FinishedPracticalTeachingWorkloads
(
StaffWorkloadID,WorkloadType,PracticeCategory,Class,StartTime,EndTime,Professional,Number,Coeffcient,ScheduleClassHours,StandardClassHours,State,UsedFlag,Creator,CreateTime,Editor,EditTime,
Re,DataOrigin
)
values
(
@StaffWorkloadID,@WorkloadType,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,2,1,@Creator,getdate(),@Creator,getdate(),
null,1
)
set @BackID=IDENT_CURRENT('FinishedPracticalTeachingWorkloads')
set @TableName='FinishedPracticalTeachingWorkloads'-- 更新工作流数据。
DECLARE @InstanceID uniqueidentifier
SET @InstanceID = NEWID()
INSERT INTO eIvy_WorkflowInstances
(ID, WorkflowName, EntityID, CurrentState)
VALUES
(@InstanceID, @TableName, @BackID, 2)INSERT INTO eIvy_WorkflowInstanceTracings
(TracingID, InstanceID, Transit, Executer, ExecuteTime, Re)
VALUES
(NEWID(), @InstanceID, 11,@LoginStaffName, GETDATE(), NULL)
IF( @@ERROR <> 0 )
GOTO Cleanup/*insert into WorkloadAssign
(Entity,EntityID,StaffID,Workload,SelfRank,State,UsedFlag,Creator,CreateTime,Editor,EditTime,Re)
values
(@TableName,@BackID,@StaffID,0,1,1,1,@Creator,getdate(),@Creator,getdate(),@Re)*/delete from FinishedPracticalTeachingWorkloads_Virtual where ID=@DelID
IF( @@ERROR <> 0 )
GOTO Cleanup
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
ENDfetch next from SureInsertAll into @DelID,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,@StaffName,@DepartmentID,@StaffID
END
close SureInsertAll
deallocate SureInsertAll
alter table FinishedPracticalTeachingWorkloads-----
enable trigger SumFinishedPracticalTeachingWorkloadsInsert-----
alter table StaffWorkLoads--------------------
enable trigger InsertSumTeachWorkloads----------------commit tran
END
Cleanup: IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END RETURN -1
end这段代码为什么插入到虚表的时候是截断的插入的呀,不是连续插入的,导致不能一次性吧数据全部插入SQL事务
id 1-80插入了 然后 80-200不插入 然后又是一段插入,一段不插入 就是我说的截断插入文笔不太行嘻嘻 教教我怎么办吧
去掉不就行了吗.
这两张表里面都有两个触发器,锁住这两张表不让其他人使用,然后我禁用触发器,提高效率,
所以我就用可序列化来做结果就出现这种问题了
declare CheckData cursor for
select IsRight from FinishedPracticalTeachingWorkloads_Virtual where Creator=@Creator如果还是不行,那就只能debugging了。