如何处理事务SQL [英] How to handle transaction SQL

问题描述

创建Proc TranTest

AS

BEGIN TRAN



INSERT INTO [authors]（[au_id] ，

[au_lname]，

[au_fname]，

[电话]，

[合约]）< br $>
VALUES（'172-32-1176'，

'盖茨'，

'比尔'，

' 800-BUY-MSFT'，

1）



- 首次更新查询

UPDATE作者

SET au_fname ='Johnzzz'

WHERE au_id ='172'





- 第二次更新查询

UPDATE作者

SET au_fname ='Johnzzz'

WHERE au_id ='175'



- 第三次更新查询

更新作者

SET au_fname ='Johnzzz'

WHERE au_id =' 178'



COMMIT TRAN

GO



以上我写的三个更新语句，如果假设在第二个更新语句中获取错误，是否第一个更新s tatement记录得到更新。



如何处理使用transact sql



我是什么尝试过：



创建Proc TranTest

AS

BEGIN TRAN



INSERT INTO [作者]（[au_id]，

[au_lname]，

[au_fname]，

[电话]，

[合约]）

VALUES（'172-32-1176'，

'盖茨'，

'Bill'，

'800-BUY-MSFT'，

1）



- 第一次更新查询

更新作者

SET au_fname ='Johnzzz'

WHERE au_id ='172'



- 第二次更新查询

更新作者

SET au_fname ='Johnzzz'

WHERE au_id ='175'



- 第三次更新查询

更新作者

SET au_fname ='Johnzzz'

WHERE au_id ='178'



COMMIT TRAN

G O



在上面我写的三个更新语句中，如果在第二个更新语句中假设得到错误，是否第一个更新语句记录得到更新。



如何处理使用transact sql

解决方案

有用的方法是 TRY ... CATCH 处理回滚事务时阻塞。此外，如果您需要确定错误来源，您将获得 @@ ERROR 系统函数。这种方法可以组合如下：

 使用 YourDatabase ; 
  GO  
 
 创建  Proc  TranTest 
  AS  
  BEGIN   TRANSACTION ; 
 
   -   声明并初始化变量以保存@@ ERROR。 
  DECLARE   @ ErrorSave0   INT ;  @ ErrorSave1   INT ， @ ErrorSave2   INT ; 
  SET   @ ErrorSave1  =  0 ; 
  SET   @ ErrorSave2  =  0 ; 
 
  BEGIN  TRY 
 
  INSERT   INTO  [authors]（[au_id]，
 [au_lname]，
 [au_fname]，
 [电话]，
 [合约] ]）
  VALUES （'  172-32 -1176'，
 ' 盖茨'，
 '  Bill'，
 ' < span class =code-string> 800-BUY-MSFT'，
  1 ）
 
   -   在INSERT的第一个局部变量中保存@@ ERROR值 
  SET   @ ErrorSave0  =  @@ ERROR ; 
 
   -   首次更新查询 
  UPDATE  authors 
  SET  au_fname = '  Johnzzz' 
  WHERE  au_id = ' < span class =code-string> 172' 
 
  SET   @ ErrorSave1  =  @@ ERROR ; 
 
   -    second update query  
  UPDATE  authors 
  SET  au_fname = '  Johnzzz' 
  WHERE  au_id = ' < span class =code-string> 175' 
 
  SET   @ ErrorSave2  =  @@ ERROR ; 
 
  / *   ...另一句话
 here ... 
 * /  
  END  TRY 
 
  BEGIN  CATCH 
   -   打印任何错误 
  PRINT  N ' 错误0 =' + CAST（ @ ErrorSave0   AS   NVARCHAR （ 8 ））; 
  PRINT  N ' 错误1 =' + CAST（ @ ErrorSave1   AS   NVARCHAR （ 8 ））; 
  PRINT  N ' 错误2 =' + CAST（ @ ErrorSave2   AS   NVARCHAR （ 8 ））; 
 
  IF   @@ TRANCOUNT >  0  
  ROLLBACK   TRANSACTION ; 
 
  END  CATCH; 
 
  IF   @@ TRANCOUNT >  0  
  COMMIT   TRANSACTION ; 
  GO  

有几种不同的方法，例如检查每个语句后使用Try Catch并设置选项时出错。这篇文章有点冗长，似乎很好地涵盖了你的选择。



SQL Server中的错误和事务处理 [ ^

Create Proc TranTest
AS
BEGIN TRAN

INSERT INTO [authors]([au_id],
[au_lname],
[au_fname],
[phone],
[contract])
VALUES ('172-32-1176',
'Gates',
'Bill',
'800-BUY-MSFT',
1)

-- First Update query
UPDATE authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172'


-- second Update query
UPDATE authors
SET au_fname = 'Johnzzz'
WHERE au_id = '175'

--third Update query
UPDATE authors
SET au_fname = 'Johnzzz'
WHERE au_id = '178'

COMMIT TRAN
GO

in the above i written three update statement, if suppose in the second update statment gets error, whether the first update statement record gets updated.

for that how to handle using transact sql

What I have tried:

Create Proc TranTest
AS
BEGIN TRAN

INSERT INTO [authors]([au_id],
[au_lname],
[au_fname],
[phone],
[contract])
VALUES ('172-32-1176',
'Gates',
'Bill',
'800-BUY-MSFT',
1)

--First Update query
UPDATE authors
SET au_fname = 'Johnzzz'
WHERE au_id = '172'

--second Update query
UPDATE authors
SET au_fname = 'Johnzzz'
WHERE au_id = '175'

--Third update query
UPDATE authors
SET au_fname = 'Johnzzz'
WHERE au_id = '178'

COMMIT TRAN
GO

in the above i written three update statement, if suppose in the second update statment gets error, whether the first update statement record gets updated.

for that how to handle using transact sql

解决方案

Useful way is TRY ... CATCH Block when you handle with rollback transactions. Also, if you need identify the source of error, you will get the @@ERROR system function. An approach of this can be combined as follows:

Use YourDatabase;
GO

Create Proc TranTest
AS
	BEGIN TRANSACTION;
	
	-- Declare and initialize a variable to hold @@ERROR.
	DECLARE @ErrorSave0 INT; @ErrorSave1 INT, @ErrorSave2 INT;
	SET @ErrorSave1 = 0;
	SET @ErrorSave2 = 0;
	
	BEGIN TRY
	 
		INSERT INTO [authors]([au_id], 
		[au_lname], 
		[au_fname], 
		[phone], 
		[contract])
		VALUES	('172-32-1176', 
		'Gates', 
		'Bill', 
		'800-BUY-MSFT', 
		1)

		-- Save @@ERROR value in first local variable for INSERT
		SET	@ErrorSave0  = @@ERROR;
		
		-- First Update query
		UPDATE authors
		SET	au_fname = 'Johnzzz'
		WHERE	au_id = '172'

		SET @ErrorSave1 = @@ERROR;

		-- second Update query
		UPDATE authors
		SET	au_fname = 'Johnzzz'
		WHERE	au_id = '175'

		SET @ErrorSave2 = @@ERROR;
		
		/* ... another sentences 
		        here ...
		*/
	END TRY

	BEGIN CATCH
		-- Print any error
		PRINT N'Error 0 = ' + CAST(@ErrorSave0 AS NVARCHAR(8));
		PRINT N'Error 1 = ' + CAST(@ErrorSave1 AS NVARCHAR(8));
        PRINT N'Error 2 = ' + CAST(@ErrorSave2 AS NVARCHAR(8));
        
        IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
			
	END CATCH;

	IF @@TRANCOUNT > 0
		COMMIT TRANSACTION;
GO

There are several different ways of doing it such as checking for an error after each statement, using Try Catch, and setting an option. This article, a bit lengthy, seems to cover your options pretty well.

Error and Transaction Handling in SQL Server[^]

这篇关于如何处理事务SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！