SQL事务回滚问题 [英] SQL Transaction Rollback issues

问题描述

我在尝试弄清为什么回滚"无法正常工作的逻辑时遇到了麻烦.

I''m having a bit of trouble trying to get the logic of why my Rollback wont work.

BEGIN TRAN

UPDATE CustomerBankInfo
SET [Money] = ([Money] - @orderValue)
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
FROM Customer
WHERE FirstName = @custFName
AND LastName =@custLName) AND [Money] > @orderValue;

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN 10
END

问题是，即使orderValue小于Money，该过程的其余部分仍将运行.我完全错过了重点吗?我觉得我在，有人可以帮忙吗?


---新刊

The thing is, even if orderValue is less than Money the rest of the procedure still runs. Am I totally missing the point? I feel like I am, can someone help please!


--- New Issue

BEGIN TRAN

SELECT @currentBalance = [Money] 
FROM CustomerBankInfo 
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
FROM Customer
WHERE FirstName = @custFName
AND LastName =@custLName)
 
IF ((@currentBalance - @orderValue) < 0)
BEGIN
ROLLBACK TRAN 
RAISERROR ('Insuffienct funds available for customer',
               16, -- Severity.
               1 -- State.
               )
END

UPDATE CustomerBankInfo
SET [Money] = ([Money] - @orderValue)
WHERE CustomerBankInfo.CustomerID = (SELECT CustomerID
FROM Customer
WHERE FirstName = @custFName
AND LastName =@custLName)

现在，它什么也不做，只是出现了一个未处理的异常.在我的脑海中，逻辑是正确的，也许我虽然看到过去了.任何帮助将不胜感激.

Now it does nothing and just comes up with an unhanded exception. In my head the logic is right maybe im seeing past something though. Any help would be much appreciated.

推荐答案

只有在发生错误并且设置了@@ ERROR变量时，您才回滚事务，

您的SQL语句将运行正常，它不会产生错误(除非您在表触发器中或沿这些行内置了某种逻辑！)

您在说的是业务逻辑，它与SQL错误完全不同.在这些情况下，您需要定义逻辑并引发自己的错误...

You''re only rolling back your transaction if an error has occurred and the @@ERROR variable is set,

Your SQL statement will run OK, it won''t generate an error (unless you had some sort of logic built in to table triggers or along those lines!)

What you are talking about is business logic, which is completely different to an SQL Error. You need to define your logic and raise your own error in these cases...

SELECT @currentBalance = [Money] FROM CustomerBankInfo 
WHERE FirstName = @custFName
AND LastName =@custLName

IF ((@currentBalance - @orderValue) < 0)
BEGIN
    ROLLBACK TRAN 
    RAISERROR ('Insuffienct funds available for customer'
               16, -- Severity.
               1 -- State.
               )
END

-编辑-其他信息

如果从c#调用此方法，则需要在那里处理异常.流是这样的...

1)您从c#调用SQL过程，并传入参数
2)SQL过程运行并遇到业务逻辑错误(资金不足).引发错误
3)这会导致SQLException，您的C#需要处理该异常
4)在您的c#catch块中，查看异常对象并提取错误消息
5)向用户显示错误消息

例如

--Edit - additional info

If you calling this from c#, you need to handle the exception there. Flow is like this...

1) You call the SQL procedure from c#, passing in parameters
2) SQL procedure runs and encounters a business logic error (insufficent funds). Error is raised
3) This results in an SQLException, which your c# needs to handle
4) In your c# catch block, look at the exception object and extract error messages
5) Display error message to the user

e.g

try
{
    using (SqlConnection conn = new SqlConnection("SomeConnectionString"))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand("SomeStoredProcedure", conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}
catch (SqlException ex)
{
    // Your exception details here. Use your debugger, look at the exception object. Determine how to display exception details to the client
}

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