저는 늘 답변했듯이 오라클을 사용할 경우 autonomous trasaction 처리가 가능하지만 mssql에서는 제공을
하지 않아서 안된다고 이야기드렸는데, 왠지 그 날따라 찜찜하여 찾아보니 linked server를 사용하는 방법이
있어 공유드립니다.
[Autonomous Trasaction이란?]
* 블록에서 자신의 작업을 수행하기 위해서 그 블록 고유의 트랜잭션을 생성하는 경우에 해당하며 그 블록의
트랜잭션의 결과가 자신을 포함하거나 호출한 트랜잭션에의 상태에 의해 영향을 받지 않는 속성을 가지는
트랜잭션을 의미한다.
* 마스터 트랜잭션과는 lock, resource, commit 에 관련된 의존성을 가지지 않는다.
[SQL Server]
-- 1. 연결된서버추가
USE MASTER
GO
EXEC sp_addlinkedserver @server = N'loopback',@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
GO
EXEC sp_serveroption loopback, N'remote proc transaction promotion','false'
-- SQL Server 2008에서 추가된 옵션(분산트랜잭션사용안함)
go
EXEC sp_serveroption loopback, N'RPC OUT', 'TRUE' -- Enable RPC to the given server.
go
-- 2. 임시테이블생성
use tempdb
go
CREATE TABLE ErrorLogging (logTime DATETIME, msg VARCHAR(255))
CREATE TABLE TestAT (id INT PRIMARY KEY)
GO
-- 3. 프로시져생성
CREATE PROCEDURE usp_ErrorLogging
@errNumber INT
AS
INSERT INTO ErrorLogging VALUES (GETDATE(), 'Error ' + CAST(@errNumber AS VARCHAR(8)) +' occurred.')
GO
-- 4. 테스트하기(TestAT에 동일한PK를 입력하여 오류를 발생시킴)
DECLARE @ERROR AS INT
INSERT INTO TestAT VALUES (1)
BEGIN TRAN OuterTran
INSERT INTO TestAT VALUES (1) -- This will raise primary key constraint violation error
SELECT @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
BEGIN TRAN InnerTran
EXEC loopback.dbTest.dbo.usp_ErrorLogging @ERROR -- 여기서 연결된서버를 활용함
COMMIT TRAN InnerTran
ROLLBACK TRAN OuterTran
END
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTran
GO
Result>
(1개 행이 영향을 받음)
메시지 2627, 수준 14, 상태 1, 줄 6
PRIMARY KEY 제약 조건 'PK__TestAT__3213E83F173876EA'을(를) 위반했습니다. 개체 'dbo.TestAT'에 중복 키를 삽입할 수 없습니다.
문이 종료되었습니다.
(1개 행이 영향을 받음)
-- 5. 로그가 쌓였는지 체크
SELECT * FROM TestAT
SELECT * FROM ErrorLogging
Result>
[Oracle] (간단히 문법만 공유드립니다. ^^)
declare
pragma autonomous_transaction;
begin
commit; -- 자율적인 트랜잭션은 반드시 commit/rollback으로 종료해야 한다.
end;
[출처] (테이블 변수를 활용한 방법 정리되어 있으니 관심 있는 분은 원문 참고 부탁드립니다.)
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx