[2k5] 트랜잭션에서의 TRY ~ CATCH 처리

/*******************************************************************************************
-- Title : [2k5] 트랜잭션에서의 TRY ~ CATCH 처리
-- Reference : hanbitbook.co.kr
-- Key word : try catch begin tran commit rollback
*******************************************************************************************/
use tempdb;
go

drop table bankbook;
go

create table bankbook
 (name nvarchar(10),
  money int ,
  constraint ck_money
  check  (money >= 0)
 );
go

insert into bankbook values ('우재남', 1000);
insert into bankbook values ('당탕이', 0);
go

select * from bankbook;
go

update bankbook set money = money - 500 where name = '우재남';
update bankbook set money = money + 500 where name = '당탕이';
go

select * from bankbook;
go

begin tran
 update bankbook set money = money - 500 where name = '우재남';
commit tran
go

select * from bankbook;
go

begin tran
 update bankbook set money = money + 500 where name = '당탕이';
commit tran
go

select * from bankbook;
go

update bankbook set money = money - 600 where name = '우재남'; --처리안됨
update bankbook set money = money + 600 where name = '당탕이'; --처리됨
go

select * from bankbook;
go

begin tran -- 1번트랜잭션
 update bankbook set money = money - 600 where name = '우재남';
    -- 오류가 발생되어 수행이 안됨. (롤백이 일어날 것으로 예상됨)
commit tran
go

select * from bankbook;
go

begin tran -- 2번트랜잭션
 update bankbook set money = money + 600 where name = '당탕이';
 -- 정상적으로수행됨
commit tran
go

select * from bankbook;
go

update bankbook set money = money - 600 where name = '당탕이';
go

select * from  bankbook;
go

begin tran
 update bankbook set money = money - 600 where name = '우재남'; --처리안됨
 update bankbook set money = money + 600 where name = '당탕이'; --처리됨
commit tran
go

select * from bankbook;
go

update bankbook set money = money - 600 where name = '당탕이';
go

begin try --하나도 처리 안됨
 begin tran
  update bankbook set money = money - 600 where name = '우재남';
  update bankbook set money = money + 600 where name = '당탕이';
 commit tran
end try
begin catch
 rollback tran
end catch;
go

begin tran;
begin try
 update bankbook set money = money - 600 where name = '우재남';
 update bankbook set money = money + 600 where name = '당탕이';
end try
begin catch
 rollback tran
end catch
commit tran /* 여기에 맞는 begin tran이 없어서 에러남 */
go
/*
메시지 3902, 수준 16, 상태 1, 줄 9
COMMIT TRANSACTION 요청에 해당하는 BEGIN TRANSACTION이 없습니다.
*/

select * from bankbook;
go