if object_id('表a') is not null drop table 表a go if object_id('表b') is not null drop table 表b go create table 表a(name varchar(20) not null) create table 表b(name varchar(20))begin try begin tran insert into 表a select null insert into 表b select 'c' commit tran end try begin catch rollback end catch --全部回滚set xact_abort on begin try begin tran insert into 表a select 'a' insert into 表b select 'c' select * from oooo --设置xact_abort回滚 commit tran end try begin catch rollback end catchselect * from 表a select * from 表b
两个update包含在一个事务里面,update2不成功就回退
begin tran update table1 ... if @@error <>0 goto err_handle update table2 ... if @@error <>0 goto err_handle commit tran return 0err_handle: rollback tran return -1
2005以上可以用1楼的try .. catch
SET XACT_ABORT on BEGIN TRAN UPDATE 1 UPDATE 2 COMMIT TRAN
create table tab1 ( id int primary key identity(1,1), topic varchar(50) )create table tab2 ( id int primary key identity(1,1), topic varchar(50) )insert into tab1 values('nxx') insert into tab2 values('nxx') go create proc upxx as begin tran update tab1 set topic='updatehou' update tab2 set topic='updatehou' if @@error=0 commit tran else rollback tran goexecute upxx
drop table 表a
go
if object_id('表b') is not null
drop table 表b
go
create table 表a(name varchar(20) not null)
create table 表b(name varchar(20))begin try
begin tran
insert into 表a select null
insert into 表b select 'c'
commit tran
end try
begin catch
rollback
end catch
--全部回滚set xact_abort on
begin try
begin tran
insert into 表a select 'a'
insert into 表b select 'c'
select * from oooo --设置xact_abort回滚
commit tran
end try
begin catch
rollback
end catchselect * from 表a
select * from 表b
update table1 ...
if @@error <>0
goto err_handle
update table2 ...
if @@error <>0
goto err_handle
commit tran
return 0err_handle:
rollback tran
return -1
BEGIN TRAN
UPDATE 1
UPDATE 2
COMMIT TRAN
create table tab1
(
id int primary key identity(1,1),
topic varchar(50)
)create table tab2
(
id int primary key identity(1,1),
topic varchar(50)
)insert into tab1 values('nxx')
insert into tab2 values('nxx')
go
create proc upxx
as
begin tran
update tab1 set topic='updatehou'
update tab2 set topic='updatehou'
if @@error=0
commit tran
else
rollback tran
goexecute upxx
仅仅是我的table1
table2