[MSSQL] TRANSACTION 트랜잭션

트랜잭션을 걸고 DML 수행 후 몇개의 행이 영향을 받는지 확인한 후에 COMMIT TRAN을 하여 실수를 방지할 수 있다.

 

1 2 3 4 5 6 7 8 9 10 11 12

BEGIN TRAN     SELECT * FROM tbl     DELETE FROM tbl     DROP TABLE tbl   -- 확인 후   -- 성공 -- COMMIT TRAN   -- 실패 (되돌리기) -- ROLLBACK TRAN

cs

 

 

 

 

 

프로시저 생성시 트랜잭션 이용 (insert, update, delete 작업시)

 

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    @out_Return INT OUTPUT -- 오류:0, 성공:1
AS
BEGIN
    SET NOCOUNT ON; -- 결과에 적용된 행의 개수를 표시하지 않아도 될때 사용 (특히, INSERT, UPDATE, DELETE 등)
                    -- 네트워크 트래픽을 감소시킬 수 있다.
    
    DECLARE @err INT
    DECLARE @rowcnt INT
    
    BEGIN TRAN
    
    -- INSERT, UPDATE 작업...
    
    SELECT
    @err = @@ERROR,
    @rowcnt = @@ROWCOUNT
 
    IF (@err <> 0) -- @@ERROR 는 검출된 에러코드 (0이면 정상 처리, 0이 아니면 비정상 처리된 것으로 오류 번호를 반환)
        BEGIN
            ROLLBACK TRAN
            SET @out_Return = 0
        END
    ELSE
        BEGIN
            IF (@rowcnt <> 1) -- @@ROWCOUNT 는 최근 실행된 문의 영향을 받은 행 수를 반환
                BEGIN
                    ROLLBACK TRAN
                    SET @out_Return = 0
                END
            ELSE
                BEGIN
                    -- 성공 !
                    COMMIT TRAN
                    SET @out_Return = 1
                END
        END
        
END
GO

 

 

 

간단히 에러코드만 확인해서 Commit, RollBack

 

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    @out_Return INT OUTPUT -- 오류:0, 성공:1
AS
BEGIN
    SET NOCOUNT ON; -- 결과에 적용된 행의 개수를 표시하지 않아도 될때 사용 (특히, INSERT, UPDATE, DELETE 등)
                    -- 네트워크 트래픽을 감소시킬 수 있다.
 
    BEGIN TRAN
    
    -- INSERT, UPDATE 작업...
 
    IF (@@ERROR = 0) -- @@ERROR 는 검출된 에러코드 (0이면 정상 처리, 0이 아니면 비정상 처리된 것으로 오류 번호를 반환)
        BEGIN
            -- 성공 !
            COMMIT TRAN
            SET @out_Return = 1
        END
    ELSE
        BEGIN
            ROLLBACK TRAN
            SET @out_Return = 0
        END
        
END
GO

 

 

 

 

다중 쿼리 작업시

 

TRY, CATCH 문 이용

 

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    @out_Return INT OUTPUT -- 오류:0, 성공:1
AS
BEGIN
    SET NOCOUNT ON; -- 결과에 적용된 행의 개수를 표시하지 않아도 될때 사용 (네트워크 트래픽을 감소시킬 수 있다.)
                    
    BEGIN TRY
        BEGIN TRAN
        
        -- 실행할 쿼리문
        -- INSERT
        -- UPDATE
        -- DELETE
        -- ...
        
        COMMIT TRAN -- 성공!
        SET @out_Return = 1
    END TRY
    BEGIN CATCH
    
        -- 쿼리문이 오류가 났을 경우 실행할 쿼리문
        
        ROLLBACK TRAN -- 실패!
        SET @out_Return = 0
 
        -- 해당 시스템 함수는 반드시 CATCH문에서 사용해야한다. (CATCH블록 외부에서 호출되는 경우 NULL값 반환)
        SELECT '오류' AS '결과'    
        , ERROR_MESSAGE() AS '오류 메세지'
        , ERROR_LINE() AS '오류 줄 번호'
        , ERROR_NUMBER() AS '오류번호'
        , ERROR_STATE() AS '오류 상태코드'
        , ERROR_PROCEDURE() AS '오류가 발생한 저장프로시저/ 트리거'
        , ERROR_SEVERITY() AS '오류의 심각도'
        , @@ERROR AS '오류 줄 번호'
 
    END CATCH  
END
GO

 

 

 

GOTO 문 이용

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>      @out_Return INT OUTPUT -- 오류:0, 성공:1 AS BEGIN     SET NOCOUNT ON; -- 결과에 적용된 행의 개수를 표시하지 않아도 될때 사용 (특히, INSERT, UPDATE, DELETE 등)                     -- 네트워크 트래픽을 감소시킬 수 있다.       BEGIN TRAN          -- INSERT     IF (@@ERROR <> 0) GOTO ERROR -- 윗줄에서 실행한 쿼리문에서 에러코드가 검출되면 ERROR로 분기     -- UPDATE     IF (@@ERROR <> 0) GOTO ERROR -- 윗줄에서 실행한 쿼리문에서 에러코드가 검출되면 ERROR로 분기     -- DELETE     IF (@@ERROR <> 0) GOTO ERROR -- 윗줄에서 실행한 쿼리문에서 에러코드가 검출되면 ERROR로 분기     -- ...     IF (@@ERROR <> 0) GOTO ERROR -- 윗줄에서 실행한 쿼리문에서 에러코드가 검출되면 ERROR로 분기          SUCCESS:         -- 성공 !         BEGIN             COMMIT TRAN             SET @out_Return = 1             GOTO FINALLY         END              ERROR:         -- 실패 !         BEGIN             ROLLBACK TRAN             SET @out_Return = 0             GOTO FINALLY         END              FINALLY:         -- 종료 !      END GO Colored by Color Scripter

cs

 

DML 수행 하나라도 오류가 발생하면 모든 구문이 Rollback된다.

 

모든 구문이 오류없이 정상작동할때만 Commit된다. 

 

 

 

자료 읽어보기

http://d2.naver.com/helloworld/407507