如何检查内部交易或外部是否发生错误？ [英] How to check if an error occured Inside Transaction or outside ?

问题描述

您好，

我创建了一个存储过程并使用了Transaction Inside Try Catch块。我在事务之前放了一些代码行。如果发生错误，它将被catch块捕获。现在如果我把rollback tran放在catch中并且在tran之前发生错误，那么这是一个错误。如何在tran内部发生错误？



例如这里是一个示例程序

Hello ,
I have created a stored Procedure and have used Transaction Inside Try Catch block. I have put some lines of code before transaction to. if an error occurs it will be caught by the catch block. Now if i put rollback tran inside catch and error occurs before tran , then it is a mistake. how will i know if the error occurred inside tran ?

For example here is a sample procedure

Create Procedure Test
@Input varchar(10)
As
Begin
 Begin Try
    Declare @Value int;
    Set @Value = @Input

     Begin Tran
      Insert into abc value (@Value);
     
     Commit Tran
 End Try

 Begin Catch
      Rollback Tran
 End Catch     

End

<pre>

Here an error will occur because @Value is of type int and i am assigning it a string value.
SO Execution will pass on to the catch block. There i have put rollback tran, but the transaction nerver started so how can it rollback ??

推荐答案

这是因为一个Transaction是为了使用多个命令：例如，将相关数据插入到表中。

如果其中一个插入失败，您不希望数据库中的任何一个数据，因为它不会有它的匹配数据。考虑发票：您有一个包含发票标题的表（客户，发票号，日期）和一个单独的表，其中包含客户购买的每个产品的行。如果标题失败（因为日期可能是2111111年2月31日），那么您不希望数据库中的产品行，因为它们没有标题可以将它们连接在一起。同样，如果其中一个产品不存在，您也不希望标题生成发票，因为缺少某些信息。



所以你在整套INSERT语句中使用一个Transaction：

It's because a Transaction is intended to work with more than one command: inserting related data into to tables for example.
If either one of the inserts fails, you don't want any of the data from either in the DB because it wouldn't have it's matching data. Think about an invoice: you have one table containing the invoice header (customer, invoice number, date) and a separate table containing a row for each of the individual products the customer bought. If the header fails (because the date is 31st Feb 2111111 perhaps) then you don't want the product rows in your DB because they don't have a header to join them together. Equally, if one of the products doesn't exist, you don't want the header to generate an invoice either, because some of the information is missing.

So you use a Transaction around the whole set of INSERT statements:

Begin Try
    Begin Tran
     INSERT INTO Invoices (InvoiceNumber, CustomerID, InvoiceDate) VALUES (@IN, @CID, @IDT);
     INSERT INTO InvoiceLines (InvoiceNumber, ProductID) VALUES (@IN, 1234)
     INSERT INTO InvoiceLines (InvoiceNumber, ProductID) VALUES (@IN, 1235)
    Commit Tran
End Try

Begin Catch
     Rollback Tran
End Catch

当任何事情失败时，ROLLBACK撤消它们。

And when anything fails, the ROLLBACK undoes them all.

这篇关于如何检查内部交易或外部是否发生错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！