SQL 트랜잭션 구현

Checked : MS-SQL 2005.

 

① 옛날 방식.

 

SET XACT_ABORT ON;

BEGIN TRANSACTION;
-- BEGIN DISTRIBUTED TRANSACTION; -- 분산 트랜잭션 구현시 사용.
------------------------------
 -- DML 쿼리문 작성 부분.
------------------------------
IF (@@ERROR <> 0)
BEGIN
    ROLLBACK TRANSACTION;
END
ELSE
BEGIN
    COMMIT TRANSACTION;
END;

 

==============================

 

② 요즘 방식.

 

SET XACT_ABORT ON;

BEGIN TRANSACTION;

-- BEGIN DISTRIBUTED TRANSACTION; -- 분산 트랜잭션 구현시 사용.
BEGIN TRY

------------------------------
 -- DML 쿼리문 작성 부분.
------------------------------
END TRY
BEGIN CATCH

    SELECT
        ERROR_NUMBER() AS 'ErrorNumber'
        , ERROR_SEVERITY() AS 'ErrorSeverity'
        , ERROR_STATE() AS 'ErrorState'
        , ERROR_PROCEDURE() AS 'ErrorProcedure'
        , ERROR_LINE() AS 'ErrorLine'
        , ERROR_MESSAGE() AS 'ErrorMessage';

    IF (@@TRANCOUNT > 0)
        ROLLBACK TRANSACTION;

END CATCH;

IF (@@TRANCOUNT > 0)
    COMMIT TRANSACTION;
GO