你可以试试在上面的事务中加一个错误看看是不是只执行一句或全执行。我在vb.net中用事务时会锁定当前数据表,如果中途出错,则什么也不执行。真正执行的是commit语句。 declare @a int begin tran test delete table_a.... set @a=1/0 --除零错误 delete table_b... if @@error=0 commit test --事务成功,则提交 else rollback test --回滚该事务 执行后是不是会删除table_a呢?你试试就知道了
当才写错了,上面的事务处理应该改成这样 每执行一句就查一下是不是有错,有错则回滚退出。 begin transaction test delete from everydaymenulist if @@error<>0 begin rollback transaction test return end delete from everydaymenuorder if @@error<>0 begin rollback transaction test return end commit transaction test 你如果用vb.net或delphi就好了,那样的工具里有try...catch...finally之类的语句。就可能写成这样 Dim mycommand As SqlClient.SqlCommand = Connection.CreateCommand Dim myTrans As SqlClient.SqlTransaction Dim mystr as string Connection.Open() myTrans = Connection.BeginTransaction() '开始事务处理 mycommand.Connection = Connection mycommand.Transaction = myTrans Try mystr="delete from table_a" mycommand.CommandText = mystr mycommand.ExecuteNonQuery() mystr="delete from table_b" mycommand.CommandText = mystr mycommand.ExecuteNonQuery() myTrans.Commit() MsgBox("Ok") Catch ex As Exception MsgBox(ex.Message) myTrans.Rollback() '有错则向上回滚 Finally Connection.Close() End Try
declare @a int
begin tran test
delete table_a....
set @a=1/0 --除零错误
delete table_b...
if @@error=0
commit test --事务成功,则提交
else rollback test --回滚该事务
执行后是不是会删除table_a呢?你试试就知道了
每执行一句就查一下是不是有错,有错则回滚退出。
begin transaction test
delete from everydaymenulist
if @@error<>0
begin
rollback transaction test
return
end
delete from everydaymenuorder
if @@error<>0
begin
rollback transaction test
return
end
commit transaction test
你如果用vb.net或delphi就好了,那样的工具里有try...catch...finally之类的语句。就可能写成这样
Dim mycommand As SqlClient.SqlCommand = Connection.CreateCommand
Dim myTrans As SqlClient.SqlTransaction
Dim mystr as string Connection.Open()
myTrans = Connection.BeginTransaction() '开始事务处理
mycommand.Connection = Connection
mycommand.Transaction = myTrans Try
mystr="delete from table_a"
mycommand.CommandText = mystr
mycommand.ExecuteNonQuery() mystr="delete from table_b"
mycommand.CommandText = mystr
mycommand.ExecuteNonQuery() myTrans.Commit()
MsgBox("Ok")
Catch ex As Exception
MsgBox(ex.Message)
myTrans.Rollback() '有错则向上回滚
Finally
Connection.Close()
End Try