--创建表：
GO
CREATE TABLE [dbo].[tb1](
[Id] [int] NOT NULL,
[c1] [nvarchar](50) NULL,
[c2] [datetime] NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )
ON [PRIMARY]
GO
--解决方案（一）
declare @iErrorCount int
set @iErrorCount = 0
begin tran Tran_2015_09_30
insert into tb1(Id, c1) values(1,'1')
set @iErrorCount=@iErrorCount+@@error
insert into tb1(Id, c1) values(2,'2')
set @iErrorCount=@iErrorCount+@@error
insert into tb1(Id, c1) values('xxxx3','3')
set @iErrorCount=@iErrorCount+@@error
insert into tb1(Id, c1) values(4,'4')
set @iErrorCount=@iErrorCount+@@error
insert into tb1(Id, c1) values(5,'5')
set @iErrorCount=@iErrorCount+@@error
if @iErrorCount=0
begin
COMMIT TRAN Tran_2015_09_30 --执行事务
end
else
begin
ROLLBACK TRAN Tran_2015_09_30 --回滚事务
end
GO
--解决方案（二）
begin try
begin tran Tran_2015_09_30
insert into tb1(Id, c1) values(1,'1')
insert into tb1(Id, c1) values(2,'2')
insert into tb1(Id, c1) values('xxxx3','3')
insert into tb1(Id, c1) values(4,'4')
insert into tb1(Id, c1) values(5,'5')
COMMIT TRAN Tran_2015_09_30
end try
begin catch
raiserror 50005N'出错了'
ROLLBACK TRAN Tran_2015_09_30 --回滚事务
end catch
GO
--解决方案（三）
--set XACT_ABORT ON ---如果不设置该项为ON，在sql中默认为OFF，那么只只回滚产生错误的 Transact-SQL 语句；设为ON，回滚整个事务
SET XACT_ABORT ON --语句产生运行时错误，则整个事务将终止并回滚。
Begin Tran
INSERT INTO tb1(Id, c1) VALUES(1,'1')
INSERT INTO tb1(Id, c1) VALUES('XX2','2') --此句产生错误时，就会回滚整个事务
Commit Tran
GO
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 ---提交事务
--//参考:
1. EmanLee, Eman Lee's Space (blog, website)
SQL Server 事务处理 回滚事务 ;
2. 代码泪 SQL Server 事务及回滚事务