set
XACT_ABORT
ON
--
-如果不设置该项为ON,在sql中默认为OFF,那么只只回滚产生错误的 Transact-SQL 语句;设为ON,回滚整个事务
begin tran t1 -- -启动一个事务
update [ water ] . [ dbo ] . [ ErrorInf ]
set ErrorMessage = ' test '
where ID = 6
insert into [ water ] . [ dbo ] . [ ErrorInf ] ( [ ID ] ,ErrorMessage, [ Description ] )
Values ( 1 , ' test1 ' , ' test1 ' )
commit tran t1 -- -提交事务
begin tran t1 -- -启动一个事务
update [ water ] . [ dbo ] . [ ErrorInf ]
set ErrorMessage = ' test '
where ID = 6
insert into [ water ] . [ dbo ] . [ ErrorInf ] ( [ ID ] ,ErrorMessage, [ Description ] )
Values ( 1 , ' test1 ' , ' test1 ' )
commit tran t1 -- -提交事务
功能:实现begin tran 和commit tran之间的语句,任一如果出现错误,所有都不执
事务不是有错就回滚的,在不写rollback的情况下,并不是什么错误都会回滚事务,有时回滚当前语句,有时回滚整个事务
如例
begin
tran
insert into dbo.area values ( ' 1111 ' )
insert into dbo.area values ( ' 2222 ' )
select 1 / 0
insert into dbo.area values ( ' 333 ' )
commit
insert into dbo.area values ( ' 1111 ' )
insert into dbo.area values ( ' 2222 ' )
select 1 / 0
insert into dbo.area values ( ' 333 ' )
commit
像这样,就算中间有错,也不会回滚,结果会成功添加三条记录
但有人说,比如重大错误,这事务也会所有回滚,只是我无法重现重大错误罢了
普通错误如果想回滚整个事务,只要加个set XACT_ABORT on就可以了
set
XACT_ABORT
on
begin tran
insert into dbo.area values ( ' 1111 ' )
insert into dbo.area values ( ' 2222 ' )
select 1 / 0
insert into dbo.area values ( ' 333 ' )
commit
begin tran
insert into dbo.area values ( ' 1111 ' )
insert into dbo.area values ( ' 2222 ' )
select 1 / 0
insert into dbo.area values ( ' 333 ' )
commit
但也有人写一堆@@error,如
begin
tran
insert into dbo.area values ( ' 1111 ' )
if @@error > 0
rollback
insert into dbo.area values ( ' 2222 ' )
if @@error > 0
rollback
select 1 / 0
if @@error > 0
rollback
insert into dbo.area values ( ' 333 ' )
if @@error > 0
rollback
commit
insert into dbo.area values ( ' 1111 ' )
if @@error > 0
rollback
insert into dbo.area values ( ' 2222 ' )
if @@error > 0
rollback
select 1 / 0
if @@error > 0
rollback
insert into dbo.area values ( ' 333 ' )
if @@error > 0
rollback
commit
当然也行,不过写起来太麻烦了.
后来发现sql2005支持try
BEGIN
TRY
BEGIN TRANSACTION
insert into dbo.area values ( ' 1111 ' )
insert into dbo.area values ( ' 2222 ' )
select 1 / 0
insert into dbo.area values ( ' 333 ' )
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg nvarchar ( 4000 ), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR ( @ErrMsg , @ErrSeverity , 1 )
END CATCH
BEGIN TRANSACTION
insert into dbo.area values ( ' 1111 ' )
insert into dbo.area values ( ' 2222 ' )
select 1 / 0
insert into dbo.area values ( ' 333 ' )
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMsg nvarchar ( 4000 ), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR ( @ErrMsg , @ErrSeverity , 1 )
END CATCH