创建触发器就发生错误,无法在此会话中启动更多的事务。

[春风十里耶耶耶]

创建触发器后，更改表中的信息，就发生错误,无法在此会话中启动更多的事务。

我怀疑不是触发器有问题，而是进程中启动了事务，并锁定或过多而导致，不然不能报这个错误.

所以上图请大家帮忙分析...







请各位高手帮忙看看..

[AcHerat]

触发器怎么写的。

[天-笑]

你就在查询分析器里更改也出问题？

你这个报错，应该不是数据库报出的吧，好好检查你的代码

[xuam]

发生什么错误?

[春风十里耶耶耶]

进程ID为52 打开2个事务且正在运行.

可能上面图片中没有体现出来.

[--小F--]

试试重新启动所有的SQL服务

[春风十里耶耶耶]

[Quote=引用 5 楼 fredrickhu 的回复:]
试试重新启动所有的SQL服务
[/Quote]

重启过很多次了...


都表示没有作用.

[NBDBA]

贴出触发器代码

触发器中不能开事务，很可能是这个问题

[--小F--]

set    xact_abort   on  
begin  distributed   tran 

[--小F--]

触发器中加

set    xact_abort   on  
begin  distributed   tran 

[春风十里耶耶耶]

[Quote=引用 2 楼 shmilywcd 的回复:]
你就在查询分析器里更改也出问题？

你这个报错，应该不是数据库报出的吧，好好检查你的代码
[/Quote]
我在查询分析器中更改可以成功的，而且前面的问题也没有了.

在软件中也可以登录成功了，也能修改信息了。

只是触发器没有作用了。

[春风十里耶耶耶]

[Quote=引用 9 楼 fredrickhu 的回复:]
SQL code
触发器中加

set xact_abort on
begin distributed tran
[/Quote]

这个我已经在查询分析器中执行过了..

貌似已经开启了..

[春风十里耶耶耶]

[Quote=引用 7 楼 nbdba 的回复:]
贴出触发器代码

触发器中不能开事务，很可能是这个问题
[/Quote]

之前触发器的帖子就是我发的，谢谢你的代码，帮忙优化，下面这段是完整的,希望您帮忙看看.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER trigger [EditUsers] on [dbo].[ZlEmployee] for update,insert
AS 
BEGIN
	
	SET NOCOUNT ON;
	set XACT_ABORT ON--启用分布式查询

	if update(code) or update(name) or update(Dept) or update(CardNo)
		begin
			declare @UserCode nvarchar(10)
			declare @UserName nvarchar(10)
			declare @CardNumber nvarchar(12)
			declare @DepartmentID nvarchar(10)
			declare @DepartmentName nvarchar(20)
--			declare @DateTimeBegin nvarchar(20)
			set @UserCode=''
			set @UserName=''
			set @CardNumber=''
			set @DepartmentID=''
--			set @DateTimeBegin=''

			declare my_cursor cursor for select Code,Name,CardNo,Dept from Inserted
			open my_cursor
			FETCH my_cursor INTO @UserCode,@UserName,@CardNumber,@DepartmentID
			while @@fetch_status=0 
				begin
					------------------------

					set @UserCode=ltrim(rtrim(@UserCode))
					set @UserName=ltrim(rtrim(@UserName))
					set @CardNumber=ltrim(rtrim(@CardNumber))
					set @DepartmentID=ltrim(rtrim(@DepartmentID))

					-------------------状态变更退餐---------ERROR RPC设置错误---------------
--					if update(State)
--						begin
--							set @DateTimeBegin=convert(nvarchar(20),getdate(),23)
--
--							exec  DBVIP.[Hitachi].[dbo].Meal_Update
--								  @TypeID=10,
--								  @MealDateBegin=@DateTimeBegin,
--								  @OperatorName='人事系统',
--								  @ComputerName='人事系统',
--								  @ComputerIp='0.0.0.0',
--								  @UserCode=@UserCode
--						end

					-------------------部门ID转换------------------------

					if @DepartmentID='8400000000' 
						begin
							set @DepartmentID='16'
						end
					else if @DepartmentID='8400000001' 
						begin
							set @DepartmentID='5'
						end
					else if @DepartmentID='8400000003' 
						begin
							set @DepartmentID='4'
						end
					else if @DepartmentID='8400000004' 
						begin
							set @DepartmentID='6'
						end
					else if @DepartmentID='8400000005' 
						begin
							set @DepartmentID='2'
						end
					else if @DepartmentID='8400000006' 
						begin
							set @DepartmentID='1'
						end
					else if @DepartmentID='8400000007' 
						begin
							set @DepartmentID='17'
						end
					else if @DepartmentID='8400000008' 
						begin
							set @DepartmentID='3'
						end
					else if @DepartmentID='8400000010' 
						begin
							set @DepartmentID='19'
						end
					else if @DepartmentID='8400000011' 
						begin
							set @DepartmentID='20'
						end

					----------------已存在信息进行修改或添加信息-------------------
					if exists(select 1 from DBVIP.Hitachi.dbo.Users where UserCode=@UserCode)
						begin--修改
							if exists(select 1 from DBVIP.Hitachi.dbo.Users where UserCode=@UserCode and DepartmentID<>@DepartmentID)
								begin
									set @DepartmentName=''
									select @DepartmentName=DepartmentName from DBVIP.Hitachi.dbo.Department where DepartmentID=@DepartmentID
									update DBVIP.Hitachi.dbo.Users set DepartmentID=@DepartmentID,GroupID='30',IsBaocanDaiding='0' where UserCode=@UserCode										
									update DBVIP.Hitachi.dbo.MealNight set DepartmentName=@DepartmentName where UserCode=@UserCode
									update DBVIP.Hitachi.dbo.MealReportPerson set DepartmentName=@DepartmentName where UserCode=@UserCode
									delete from DBVIP.Hitachi.dbo.MealFriendAll where UserCodeD=@UserCode or UserCodeP=@UserCode
									delete from DBVIP.Hitachi.dbo.MealFriends where UserCodeM=@UserCode or UserCodeF=@UserCode
									delete DBVIP.Hitachi.dbo.MsgNoticeNum where UserCode=@UserCode
--									update DBVIP.Hitachi.dbo.MsgInfo set Department=@DepartmentName,GroupName='未知课别' where UserName=@UserName
								end
									
							if (@CardNumber<>'' and @CardNumber is not null)--卡号不能为空
								begin
									if exists(select 1 from DBVIP.[Hitachi].[dbo].[Users] where UserCode=@UserCode and CardNumber<>@CardNumber)--此人卡号不对
										begin
											update DBVIP.[Hitachi].[dbo].Users set CardNumber=@CardNumber where UserCode=@UserCode
											update DBVIP.[Hitachi].[dbo].MealNight set CardNumber=@CardNumber where UserCode=@UserCode
											update DBVIP.[Hitachi].[dbo].MealReportPerson set CardNumber=@CardNumber where UserCode=@UserCode
										end
								end

								insert into DBVIP.Hitachi.dbo.UpDateCardLog(UserCode,UserName,DepartmentID,CardNumber,CardType) values (@UserCode,@UserName,@DepartmentID,@CardNumber,1)--添加日志
						end
					else if @UserCode<>'' and @UserCode is not null--添加
						begin						
							if exists(select 1 from DBVIP.Hitachi.dbo.Users where UserName=@UserName)
								begin
									set @UserName=@UserName+@UserCode
								end

							insert into DBVIP.Hitachi.dbo.Users(UserCode,UserName,DepartmentID,CardNumber) values (@UserCode,@UserName,@DepartmentID,@CardNumber)
							insert into DBVIP.Hitachi.dbo.UpDateCardLog(UserCode,UserName,DepartmentID,CardNumber,CardType) values (@UserCode,@UserName,@DepartmentID,@CardNumber,0)--添加日志
						end
					fetch next from my_cursor into @UserCode,@UserName,@CardNumber,@DepartmentID
				end
			close my_cursor --关闭游标
			deallocate my_cursor --释放游标资源
		end
END