SQLServer事务、异常

--开始事务
begin transaction tran_bank;
declare @tran_error int;
    set @tran_error = 0;
    begin try
        update bank set totalMoney = totalMoney - 10000 where userName = 'jack';        
        set @tran_error = @tran_error + @@error;
        update bank set totalMoney = totalMoney + 10000 where userName = 'jason';
        set @tran_error = @tran_error + @@error;
    end try
    begin catch        
        print '出现异常，错误编号：' + convert(varchar, error_number()) + '， 错误消息：' + error_message(); 
        set @tran_error = @tran_error + 1;
    end catch
if (@tran_error > 0)
    begin
        --执行出错，回滚事务
        rollback tran;
        print '转账失败，取消交易';
    end
else
    begin
        --没有异常，提交事务
        commit tran;
        print '转账成功';
    end
go

if (object_id('proc_error_info') is not null)
    drop procedure proc_error_info
go
create proc proc_error_info
as
    select 
        error_number() '错误编号',
        error_message() '错误消息',
        error_severity() '严重性',
        error_state() '状态好',
        error_line() '错误行号',
        error_procedure() '错误对象(存储过程或触发器)名称';
go

begin try
    select 1 / 0;
end try
begin catch
    exec proc_error_info; --调用错误消息存储过程
end catch
go

begin try
    select * * from student;
end try
begin catch
    exec proc_error_info;
end catch
go

begin try
    select * from st;
end try
begin catch
    exec proc_error_info;
end catch
go

if (object_id('proc_select') is not null)
    drop procedure proc_select
go
create proc proc_select
as
    select * from st;
go
begin try
    exec proc_select;
end try
begin catch    
    exec proc_error_info;
end catch
go 

--创建临时用表
if (object_id('temp_tab', 'u') is not null)
    drop table temp_tab
go
create table temp_tab(
    id int primary key identity(100000, 1),
    name varchar(200)
)
go

begin try
    begin tran;
    --没有createTime字段
    alter table temp_tab drop column createTime;
    commit tran;
end try
begin catch
    exec proc_error_info;--显示异常信息
    if (xact_state() = -1)
    begin
        print '会话具有活动事务，但出现了致使事务被归类为无法提交的事务的错误。'
            + '会话无法提交事务或回滚到保存点；它只能请求完全回滚事务。'
            + '会话在回滚事务之前无法执行任何写操作。会话在回滚事务之前只能执行读操作。'
            + '事务回滚之后，会话便可执行读写操作并可开始新的事务。';
    end
    else if (xact_state() = 0)
    begin
        print '会话没有活动事务。';
    end
    else if (xact_state() = 1)
    begin
        print '会话具有活动事务。会话可以执行任何操作，包括写入数据和提交事务。';
    end
end catch
go

--异常、错误信息表
if (object_id('errorLog', 'U') is not null)
    drop table errorLog
go
create table errorLog(
    errorLogID int primary key identity(100, 1),    --ErrorLog 行的主键。
    errorTime datetime default getDate(),           --发生错误的日期和时间。
    userName sysname default current_user,          --执行发生错误的批处理的用户。
    errorNumber int,                                --发生的错误的错误号。
    errorSeverity int,                              --发生的错误的严重性。
    errorState int,                                 --发生的错误的状态号。
    errorProcedure nvarchar(126),                   --发生错误的存储过程或触发器的名称。
    errorLine int,                                  --发生错误的行号。
    errorMessage nvarchar(4000)
)
go

--存储过程：添加异常日志信息
if (object_id('proc_add_exception_log', 'p') is not null)
    drop proc proc_add_exception_log
go
create proc proc_add_exception_log(@logId int = 0 output)
as
begin
    set nocount on;
    set @logId = 0;
    begin try
        if (error_number() is null)
            return;
        
        if (xact_state() = -1)
        begin
            print '会话具有活动事务，但出现了致使事务被归类为无法提交的事务的错误。'
                + '会话无法提交事务或回滚到保存点；它只能请求完全回滚事务。'
                + '会话在回滚事务之前无法执行任何写操作。会话在回滚事务之前只能执行读操作。'
                + '事务回滚之后，会话便可执行读写操作并可开始新的事务。';
        end
        else if (xact_state() = 0)
        begin
            print '会话没有活动事务。';
        end
        else if (xact_state() = 1)
        begin
            print '会话具有活动事务。会话可以执行任何操作，包括写入数据和提交事务。';
        end
        
        --添加日志信息
        insert into errorLog values(getDate(), 
            current_user, error_number(), 
            error_severity(), error_state(), 
            error_procedure(), 
            error_line(), error_message());
        --设置自增值
        select @logId = @@identity;
    end try
    begin catch
        print '添加异常日志信息出现错误';
        exec proc_error_info;--显示错误信息
        return -1;
    end catch
end
go

--处理异常信息示例
declare @id int;
begin try
    begin tran;
    --删除带有外键的记录信息
    delete classes where id = 1;
    commit tran;
end try
begin catch
    exec proc_error_info;--显示错误信息
    if (xact_state() <> 0)
    begin
        rollback tran;
    end
    exec proc_add_exception_log @id output
end catch
select * from errorLog where errorLogID = @id;
go