[2k] @@ERROR과 SET XACT_ABORT 옵션의 사용 비교

1. /**********************************************************************************************
   -- Title : [2k] @@ERROR과 SET XACT_ABORT 옵션의 사용 비교
   -- Reference : dbRang.com
   -- Key word : xact_abort, @@error, distributed transaction, 트랜잭션
   **********************************************************************************************/
   drop table #tmp;
   
   create table #tmp
   ( a int primary key not null
   , b varchar(50)
   );
   
   -- test 1 : 맨 끝에 PK 에러일 시
   truncate table #tmp
   
   begin tran
   
   insert into #tmp values (1,'12345670')
   insert into #tmp values (2,'123000040')
   insert into #tmp values (3,'1230000340')
   insert into #tmp values (4,'125345340')
   insert into #tmp values (5,'12000340')
   insert into #tmp values (6,'125345340')
   insert into #tmp values (7,'123000040')
   insert into #tmp values (8,'12300005340')
   insert into #tmp values (9,'123000045340')
   insert into #tmp values (9,'1230000345340')  -- PK 중복 오류
   
   if (@@error<>0) rollback tran
   else commit tran
   
   select * from #tmp
   select @@trancount
   
   -- test 2 : 중간에 PK 오류일 시
   truncate table #tmp
   
   begin tran
   
   insert into #tmp values (1,'12345670')
   insert into #tmp values (2,'123000040')
   insert into #tmp values (3,'1230000340')
   insert into #tmp values (4,'125345340')
   insert into #tmp values (5,'12000340')
   insert into #tmp values (6,'125345340')
   insert into #tmp values (6,'123000040') -- PK 중복 오류
   insert into #tmp values (8,'12300005340')
   insert into #tmp values (9,'123000045340')
   insert into #tmp values (0,'1230000345340')
   
   if (@@error<>0) rollback tran
   else commit tran
   
   select * from #tmp
   select @@trancount
   
   -- test 3 : 중간에 PK 오류가 있고 하단에 XACT 결과 처리 존재시
   truncate table #tmp
   
   begin tran
   
   insert into #tmp values (1,'12345670')
   insert into #tmp values (2,'123000040')
   insert into #tmp values (3,'1230000340')
   insert into #tmp values (4,'125345340')
   insert into #tmp values (5,'12000340')
   insert into #tmp values (6,'125345340')
   insert into #tmp values (6,'123000040') -- PK 중복 오류
     if (@@error<>0) rollback tran
     else commit tran
   insert into #tmp values (8,'12300005340')
   insert into #tmp values (9,'123000045340')
   insert into #tmp values (0,'1230000345340')
   
   if (@@error<>0) rollback tran
   else commit tran
   
   select * from #tmp
   select @@trancount
   
   -- test 4 : xact_abort 옵션 사용시
   truncate table #tmp
   set xact_abort on
   
   begin tran
   
   insert into #tmp values (1,'12345670')
   insert into #tmp values (2,'123000040')
   insert into #tmp values (3,'1230000340')
   insert into #tmp values (4,'125345340')
   insert into #tmp values (5,'12000340')
   insert into #tmp values (6,'125345340')
   insert into #tmp values (6,'123000040') -- PK 중복 오류
   insert into #tmp values (8,'12300005340')
   insert into #tmp values (9,'123000045340')
   insert into #tmp values (0,'1230000345340')
   
   commit
   
   select * from #tmp
   select @@trancount
   
   set xact_abort off
    
    
   /*
   -- begin distributed tran의 사용
   */
   USE pubs
   GO
   
   BEGIN DISTRIBUTED TRANSACTION
   
   UPDATE authors
     SET au_lname = 'McDonald'
   WHERE au_id = '409-56-7008'
   
   EXECUTE remote_srv.pubs.dbo.changeauth_lname '409-56-7008','McDonald'
   
   COMMIT TRAN
   GO