- SQL code
create proc usp_ChangeHander @UserGUID VARCHAR(40), @EventGUID VARCHAR(40), @WorkL money, @Ru VARCHAR(40), --更改的人 @remarks VARCHAR(4000) AS DECLARE @count int DECLARE @strDepartment VARCHAR(40) DECLARE @strUserName VARCHAR(10) DECLARE @strGW VARCHAR(20) DECLARE @strDepartmentSelf VARCHAR(40) DECLARE @strUserNameSelf VARCHAR(10) DECLARE @strGWSelf VARCHAR(20) DECLARE @date VARCHAR(24) begin SET @date = getdate() SET @count =0 BEGIN TRANSACTION --SET @count = 0 --SELECT @strUserName =UserName, @strDepartment=CONVERT(varchar(40),DepartmentGUID),@strGW =JobTitle FROM myuser WHERE USERGUID = @UserGUID SELECT @strUserName =UserName,@strDepartment=RoleOrgName,@strGW =RoleName FROM [v_OrgRoleUser] WHERE USERGUID = @UserGUID --接受的人 SELECT @strUserNameSelf =UserName,@strDepartmentSelf=RoleOrgName,@strGWSelf =RoleName FROM [v_OrgRoleUser] WHERE USERGUID = @Ru --更改的人 BEGIN ---插入数据到处理过程 INSERT INTO Itsm_ProcessReport (ProcessGUID,EventGUID,ProcessTime,ProcessContent,Hander,Receiver,Remarks) VALUES (newid(),@EventGUID,@date,'更改处理人',@strUserNameSelf,@strUserName,@remarks) SET @count = @count + @@ERROR END BEGIN ---插入数据到工作量表 INSERT INTO Itsm_WorkLoad (WorkLoadGUID,EventGUID,UserName,Department,WorkQuatity,WorkGw,IsZf,Remarks) VALUES (newid(),@EventGUID,@strUserNameSelf,@strDepartmentSelf,@WorkL,@strGWSelf,0,'更改处理人') SET @count = @count + @@ERROR END BEGIN ---更新事件单信息 UPDATE Itsm_EventInfo SET [email protected] WHERE [email protected] SET @count = @count + @@ERROR END print @count IF @count<>0 begin ROLLBACK TRANSACTION SELECT 'FAILED' end ELSE begin COMMIT TRANSACTION SELECT 'SUCCESS' end end
我想问下，我的这个事务如果第一个insert 出错， 就会报 事务无 回滚 或者 提交，然后就把表锁死了。。。
难道不能这样写？
------解决方案--------------------
将 BEGIN TRANSACTION 更改为SET XACT_ABORT ON
------解决方案--------------------
try this,
- SQL code
create proc usp_ChangeHander @UserGUID VARCHAR(40), @EventGUID VARCHAR(40), @WorkL money, @Ru VARCHAR(40), --更改的人 @remarks VARCHAR(4000) AS DECLARE @count int DECLARE @strDepartment VARCHAR(40) DECLARE @strUserName VARCHAR(10) DECLARE @strGW VARCHAR(20) DECLARE @strDepartmentSelf VARCHAR(40) DECLARE @strUserNameSelf VARCHAR(10) DECLARE @strGWSelf VARCHAR(20) DECLARE @date VARCHAR(24) begin SET @date = getdate() SET @count =0 set xact_abort on BEGIN TRANSACTION --SET @count = 0 --SELECT @strUserName =UserName, @strDepartment=CONVERT(varchar(40),DepartmentGUID),@strGW =JobTitle FROM myuser WHERE USERGUID = @UserGUID SELECT @strUserName =UserName,@strDepartment=RoleOrgName,@strGW =RoleName FROM [v_OrgRoleUser] WHERE USERGUID = @UserGUID --接受的人 SELECT @strUserNameSelf =UserName,@strDepartmentSelf=RoleOrgName,@strGWSelf =RoleName FROM [v_OrgRoleUser] WHERE USERGUID = @Ru --更改的人 BEGIN ---插入数据到处理过程 INSERT INTO Itsm_ProcessReport (ProcessGUID,EventGUID,ProcessTime,ProcessContent,Hander,Receiver,Remarks) VALUES (newid(),@EventGUID,@date,'更改处理人',@strUserNameSelf,@strUserName,@remarks) SET @count = @count + @@ERROR END BEGIN ---插入数据到工作量表 INSERT INTO Itsm_WorkLoad (WorkLoadGUID,EventGUID,UserName,Department,WorkQuatity,WorkGw,IsZf,Remarks) VALUES (newid(),@EventGUID,@strUserNameSelf,@strDepartmentSelf,@WorkL,@strGWSelf,0,'更改处理人') SET @count = @count + @@ERROR END BEGIN ---更新事件单信息 UPDATE Itsm_EventInfo SET [email protected] WHERE [email protected] SET @count = @count + @@ERROR END print @count IF @count<>0 begin ROLLBACK TRANSACTION SELECT 'FAILED' end ELSE begin COMMIT TRANSACTION SELECT 'SUCCESS' end end