----原子性测试---

--表t1，id只能等于1
create table t1
(id int constraint chk_id check(id=1)
)
--表t2
create table t2
(id int 
)

--测试数据
insert into t2 values(1)
insert into t2 values(2)
insert into t2 values(3)
insert into t2 values(4)
insert into t2 values(5)

--情况1
set XACT_ABORT on
begin tran
    insert into t1 values(1)
    insert into t1 select id from t2
    insert into t1 values(1)
    
commit
--结果：表中没有插入记录
--说明：set XACT_ABORT 在语句失败时自动回滚


--情况2 transaction
begin tran

    insert into t1 select id from t2
    insert into t1 values(1)
    
commit

--结果：t1中插入一条记录
--说明：单独的begin tran，commit不具有原子性


--情况3 try catch
begin try
    begin tran
    insert into t1 values(1)
    insert into t1 select id from t2
    insert into t1 values(1)
    commit
end try
begin catch
  rollback
   print 'error'
  return
end catch

--结果：表中没有插入记录
--说明: try catch 使语句具有原子性