SQL Server 事务处理 回滚事务

lygyh9985825

　　 创建表：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t1](
    [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]
　　
　　 解决方案（一）


declare   @iErrorCount   int
set @iErrorCount = 0
begin tran Tran_2008_10_07

insert into t1(Id, c1) values(1,'1')
set @iErrorCount=@iErrorCount+@@error

insert into t1(Id, c1) values(2,'2')
set @iErrorCount=@iErrorCount+@@error

insert into t1(Id, c1) values('xxxx3','3')
set @iErrorCount=@iErrorCount+@@error

insert into t1(Id, c1) values(4,'4')
set @iErrorCount=@iErrorCount+@@error

insert into t1(Id, c1) values(5,'5')
set @iErrorCount=@iErrorCount+@@error

if @iErrorCount=0
  begin   
    COMMIT TRAN Tran_2008_10_07
  end
else   
  begin   
    ROLLBACK TRAN Tran_2008_10_07
  end

　　
　　 解决方案（二）

begin try
    begin tran Tran_2008_10_07

        insert into t1(Id, c1) values(1,'1')

        insert into t1(Id, c1) values(2,'2')

        insert into t1(Id, c1) values('xxxx3','3')

        insert into t1(Id, c1) values(4,'4')

        insert into t1(Id, c1) values(5,'5')

    COMMIT TRAN Tran_2008_10_07
end try
begin catch
    raiserror 50005N'出错了'
    ROLLBACK TRAN Tran_2008_10_07
end catch