如何在sql server中提交和回滚事务? [英] How to commit and rollback transaction in sql server?
本文介绍了如何在sql server中提交和回滚事务?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个巨大的脚本,用于从一台服务器创建表和移植数据.所以这个sceipt基本上有 -
I have a huge script for creating tables and porting data from one server. So this sceipt basically has -
- 为表创建语句.
- 插入以将数据移植到这些新创建的表中.
- 为存储过程创建语句.
所以我有这个代码但它基本上不起作用@@ERROR 我认为总是零..
So I have this code but it does not work basically @@ERROR is always zero I think..
BEGIN TRANSACTION
--CREATES
--INSERTS
--STORED PROCEDURES CREATES
-- ON ERROR ROLLBACK ELSE COMMIT THE TRANSACTION
IF @@ERROR != 0
BEGIN
PRINT @@ERROR
PRINT 'ERROR IN SCRIPT'
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
PRINT 'COMMITTED SUCCESSFULLY'
END
GO
任何人都可以帮我写一个事务,如果一切正常,它基本上会回滚错误并提交..我可以使用 RaiseError 不知何故在这里..
Can anyone help me write a transaction which will basically rollback on error and commit if everything is fine..Can I use RaiseError somehow here..
推荐答案
不要使用 @@ERROR
,而是使用 BEGIN TRY/BEGIN CATCH
.请参阅这篇文章:异常处理和嵌套事务示例程序:
Don't use @@ERROR
, use BEGIN TRY/BEGIN CATCH
instead. See this article: Exception handling and nested transactions for a sample procedure:
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
return;
end catch
end
这篇关于如何在sql server中提交和回滚事务?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文