回滚多行 [英] Rollback multiple row
本文介绍了回滚多行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果第三个表i循环第二次并且插入失败我如何循环回到以前的数据包括tableA,tableB数据需要回滚。或者我只是不想插入任何东西,如果我遇到错误。
我尝试了什么:
i有3个表我要插入
第一次插入
if 3rd table i loop 2nd time and insert failed how i loop back to previous data include the tableA,tableB data need rollback.Or i just no want insert anything if i meet the error.
What I have tried:
i have 3 table that i want to insert
1st insert
insert into table (A) values (A)
第一次插入完成后的第二次插入
2nd insert after 1st insert complete
insert into tableB (B) values (B)
第3个插入是循环语句
3rd insert is loop statement
For value As Integer = 0 To 5
insert into tableC (C) values (value)
NEXT
推荐答案
在执行任何操作之前启动一个事务serts。请参阅 MySQL :: MySQL 5.7参考手册:: 13.3.1 START TRANSACTION,COMMIT和ROLLBACK语法 [ ^ ]
将整套插入包装在Try-Catch块中,并在Catch块中包含Rollback。
例如
Start a transaction before you do any of the inserts. See MySQL :: MySQL 5.7 Reference Manual :: 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax[^]
Wrap the entire set of inserts within a Try-Catch block and include a Rollback in the Catch block.
E.g.
Dim cmd As New MySqlCommand()
Dim trn As MySqlTransaction
trn = yourConnection.BeginTransaction(IsolationLevel.ReadCommitted)
cmd.Transaction = trn
Try
cmd.CommandText = "insert into table (A) values (A)"
cmd.ExecuteNonQuery()
cmd.CommandText = "insert into tableB (B) values (B)"
cmd.ExecuteNonQuery()
For value As Integer = 0 To 5
cmd.CommandText = "insert into tableC (C) values (value)"
cmd.ExecuteNonQuery()
NEXT
trn.Commit() 'all values will have been inserted
Catch e As Exception
trn.Rollback() 'no values will have been inserted
End Try
注意:我没有包含任何连接代码,也没有在回滚发生后正确处理错误(您可能希望让用户知道错误)。
此代码未经测试,仅用于向您显示原则 - 可能存在轻微错误。
您还用st标记了您的问题oredproc所以如果你想在MySQL存储过程中执行所有操作而不是VB,请查看来自wchiquito的解决方案链接 [ ^ ]
这篇关于回滚多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文