42. 트랜잭션(Transaction)

--[1] 트랜잭션관련샘플테이블

Create Table dbo.TranTest

(

       Num Int Identity(1, 1) Not Null Primary Key,

       Name VarChar(25) Not Null,

       PostDate SmallDateTime Default(GetDate())

)

Go

 

 

--[2] 샘플데이터입력

Insert Into TranTest(Name) Values('홍길동')

Go

Insert Into TranTest(Name) Values('한라산')

Go

Insert Into TranTest(Name) Values('백두산')

Go

 

 

--[3] 출력

Select * From TranTest

Go

 

 

--[4] 트랜잭션테스트(롤백) : Begin Tran ~ RollBack Tran

--[a] 트랜잭션시작

Begin Tran

 

--[b] SQL 구문실행

Delete TranTest Where Num = 2

 

--[c] 트랜잭션롤백: 트랜잭션이전으로상태돌리기(구문취소...)

RollBack Tran

 

 

--[5] 트랜잭션처리: Begin Tran ~ Commit Tran

Begin Tran

       Delete TranTest Where Num = 2    -- 성공

       --Select aaa From aaaa           -- 실패

       --Delete TranTest Where Num = 3  -- 실행되지않는다.

       If @@Error > 0

             RollBack Tran

Commit Tran

 

 

--[6] 트랜잭션에별칭부여

Begin Tran DeleteData

       Delete TranTest Where Num = 3

       Select * From TranTest

RollBack Tran DeleteData

 

 

--[7] 다중트랜잭션처리

Begin Tran

       Delete TranTest Where Num = 6

       Begin Tran

             Delete TranTest Where Num = 7

             Select @@TranCount -- 2개

RollBack Tran -- 전체트랜잭션을되돌린다.

Commit Tran

 

Select * From TranTest

 

 

--[8] 트랜잭션단계확인

Begin Tran

       Update TranTest Set Neme = '트랜잭션' Where Num = 8

       Begin Tran

       Update TranTest Set Name = '트랜잭션' Where Num = 9

             Select @@TranCount -- 2개

RollBack Tran -- 전체트랜잭션을되돌린다.

Commit Tran

 

 

--[9] 트랜잭션의주요사용

Begin Tran

 

       Insert Into TranTest(Name) Values('홍길동')

      

       Update TranTest Set Name = '트랜잭션' Where Num = 14

      

       Insert Into TranTest(Name) Values('홍길동')

      

       If @@Error > 0             -- 명시적으로트랜잭션되돌리기

       Begin

             RollBack Tran

       End

      

Commit Tran

 

Select * From TranTest

 

 

--[10] 한번처리에여러개의구문처리시트랜잭션을반드시적용

Begin Tran

       Insert문

       Update문

       Delete문

Commit Tran