[2k] Transaction 과 에러 처리(@@error)

1. /**********************************************************************************************
   -- Title :[2k] Transaction 과 에러 처리(@@error)
   -- Reference : Books On-Line
   -- Key word : transaction, 트랜잭션, 에러, @@error
   **********************************************************************************************/
    /*
   -- 여러 insert시 최종 에러이전의 삽입된 데이터 까지 롤백
   */
   drop table #tmp;
    
   create table #tmp
   ( a int, b varchar(10))
   go
    
   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 (0,'1230000345340') --자리수 초과
   if (@@error<>0) rollback tran
   else commit tran
    
   select @@trancount
   
   select * from #tmp --왜 전부 롤백됐지..?? 밥튕~ @@error는 바로 위 문장만 체크하잖어..ㅡㅡ;;;
    
   
   /*
   -- @@ERROR를 사용하여 여러 명령문의 성공 확인
   */
   USE pubs
   GO
   
   DECLARE @del_error int, @ins_error int
   
   -- Start a transaction.
   BEGIN TRAN
    
   -- Execute the DELETE statement.
   DELETE authors
   WHERE au_id = '409-56-7088'
    
   -- Set a variable to the error value for the DELETE statement.
   SELECT @del_error = @@ERROR
    
   -- Execute the INSERT statement.
   INSERT authors
      VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
    
   -- Set a variable to the error value for the INSERT statement.
   SELECT @ins_error = @@ERROR
    
   -- Test the error values.
   IF @del_error = 0 AND @ins_error = 0
   BEGIN
      -- Success. Commit the transaction.
      PRINT 'The author information has been replaced'
      COMMIT TRAN
   END
   ELSE
   BEGIN
      -- An error occurred. Indicate which operation(s) failed and roll back the transaction.
      IF @del_error <> 0
         PRINT 'An error occurred during execution of the DELETE statement.'
      IF @ins_error <> 0
         PRINT 'An error occurred during execution of the INSERT statement.'
      ROLLBACK TRAN
   END
    
    
   /*
   -- @@ERROR를 사용하여 조건부로 프로시저 종료
   */
   USE pubs
   GO
    
   CREATE PROCEDURE add_author
   @au_id varchar(11),@au_lname varchar(40),
   @au_fname varchar(20),@phone char(12),
   @address varchar(40) = NULL,@city varchar(20) = NULL,
   @state char(2) = NULL,@zip char(5) = NULL,
   @contract bit = NULL
   AS
   -- Execute the INSERT statement.
   INSERT INTO authors
   (au_id,  au_lname, au_fname, phone, address,
    city, state, zip, contract) values
   (@au_id,@au_lname,@au_fname,@phone,@address,
    @city,@state,@zip,@contract)
    
   -- Test the error value.
   IF @@ERROR <> 0
   BEGIN
      -- Return 99 to the calling program to indicate failure.
      PRINT 'An error occurred loading the new author information'
      RETURN(99)
   END
   ELSE
   BEGIN
      -- Return 0 to the calling program to indicate success.
      PRINT 'The new author information has been loaded'
      RETURN(0)
   END
   GO