SQL回滚事务性错误代码 [英] SQL Rollback Transactional Error Code

问题描述

这是对此问题的第二次修订.

This is the second amendment on this problem.

DECLARE @currentBalance int

BEGIN TRAN

SELECT @currentBalance = [Money] 
FROM CustomerBankInfo 
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
				     FROM Customer
				     WHERE FirstName = 'Trevor'
				     AND LastName = 'Sandoval')
 
IF ((@currentBalance - 1000) < 0)
BEGIN
ROLLBACK TRAN 
END

UPDATE CustomerBankInfo
SET [Money] = ([Money] - 1000)
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
				     FROM Customer
				     WHERE FirstName = 'Trevor'
				     AND LastName ='Sandoval')

UPDATE CompanyBankInfo
SET [Money] = ([Money] + 1000)
							 
									 
COMMIT TRAN 

我不明白为什么它不起作用.它带有:

(影响1行)
(影响1行)

"Msg 3902，第16级，状态1，第25行
COMMIT TRANSACTION请求没有相应的BEGIN TRANSACTION."

我不希望它在@current余额小于1000时执行更新，但仍然会影响它.

任何建议都是好的.

I don''t get why it wont work. It comes up with:

(1 rows(s) affected)
(1 rows(s) affected)

"Msg 3902, Level 16, State 1, Line 25
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."

I don''t want it to do the UPDATES if the @current balance is less than 1000 yet it still effects it.

Any suggestions would be good.

推荐答案

由于问题是与数据相关的(例如@orderValue始终为零)，因此实际上很难说出造成问题.尝试从过程中发送更多信息.例如:

Since the problem is data related (for example @orderValue being always zero) it''s actually hard to say what''s causing the problem. Try sending some more info from the procedure. For example:

BEGIN TRAN

SELECT @currentBalance = [Money]
FROM CustomerBankInfo
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
                     FROM Customer
                     WHERE FirstName = @custFName
                     AND LastName =@custLName)
PRINT N'currentbalance:' + CAST(@currentBalance AS nvarchar(10));
PRINT N'orderValue:' + CAST(@orderValue AS nvarchar(10));
IF ((@currentBalance - @orderValue) < 0)
...

这些值有意义吗?

Do the values make sense?

您的交易似乎失败了，回滚了，没有什么可提交的了.

检查此 [

It appears like your transaction failed, got rolled back and there is nothing to commit.

check this[^] out

hope it helps :)

这篇关于SQL回滚事务性错误代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！