트랜잭션(Transaction) 및 트리거(Trigger)

4/9/2023
10 minutes to read

소개

이번 장에서는 트랜잭션과 트리거를 다루겠습니다. 둘 다 관련성이 깊지는 않으나 함께 다뤄보도록 하겠습니다. 트랜잭션은 모든 T-SQL을 수행할 때 완전하게 수행하기 위함이고, 트리거는 테이블 제약조건과 같은 역할을 하는 테이블의 데이터가 수정될 때만 실행되는 특정한 유형의 저장 프로시저입니다.

트랜잭션(Transaction)이란?

트랜잭션은 이론적으로 하나의 논리적 기능을 수행하기 위한 작업의 단위를 말합니다. 트랜잭션을 말할 때 가장 많이 드는 실례가 있다면 역시 은행업무 얘기입니다. 만약에 여러분들이 은행에서 여러분들의 통장에서 다른 사람의 통장으로 100만원을 계좌이체를 하는데 여러분 통장에서는 제대로 빠져나갔는데 다른 사람의 통장에는 입금이 되질 않았다. 이런 경우 여러분의 통장에서 빠져나감과 동시에 다른 사람의 통장에 이체되기까지가 트랜잭션이라고 보면 이 두가지 행위가 모두 일어나면 트랜잭션이 성공한 것이고 둘 중 하나라도 일어나지 않으면 트랜잭션이 실패했기 때문에 처음으로 되돌려야 합니다. 이런걸 “데이터 일관성을 유지한다”라고 합니다. SQL에서 데이터베이스에 대한 일련의 처리를 하나로 모은 작업 단위로 관리할 수 있는데, 이 작업 단위가 바로 트랜잭션(Transaction)입니다.

데이터베이스 관련 연산의 가장 기본적인 단위
트랜잭션은 데이터베이스에서 하나의 논리적 기능을 수행하기 위한 작업의 단위로, 데이터베이스 관련 연산의 가장 기본적인 단위이다.
트랜잭션은 데이터베이스 시스템에서 복구 및 병행 수행 시 처리되는 작업의 논리적 단위이다.

트랜잭션은 일괄적으로 수행되어야 할 일련의 데이터베이스 연산들을 가리킵니다. 병행 제어 및 회복 작업에서 논리적 단위가 됩니다.

트랜잭션은 원자성, 일관성, 격리성, 영속성의 특성을 가집니다.

커밋(COMMIT)은 SQL 명령어로 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려주는 명령이다.

트랜잭션의 특징

트랜잭션의 특징은 ACID로 표현됩니다.

(1) Atomicity(원자성) : 트랜잭션으로 처리되는 모든 것이 한꺼번에 처리되어야 하며 하나라도 에러가 발생되면 한꺼번에 취소되어야 합니다. (2) Consistency(일관성) : 트랜잭션을 수행하기 전과 트랜잭션이 수행된 후의 시스템의 고정요소는 같아야 합니다. (3) Isolation(독립성) : 둘 이상의 트랜잭션이 동시에 수행중일 때는 하나의 트랜잭션이 수행중일 때는 다른 트랜잭션 연산이 끼어들면 안됩니다. (4) Durability(지속성) : 트랜잭션이 완료되면 영구적으로 지속됩니다.

트랜잭션 관련 키워드

BEGIN TRAN : 트랜잭션을 시작합니다.
COMMIT TRAN : 트랜잭션을 처리합니다.
ROLLBACK TRAN : 트랜잭션을 취소합니다.

예제1. 트랜잭션 처리(트랜잭션.sql)

--[0] 실습용 테이블 생성
USE tempdb
GO
CREATE TABLE dbo.FrontBoard
(
    ItemID int IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Title varchar(25) NULL,
    Url varchar(50) NULL,
    Target varchar(10) NULL CHECK(Target IN('_top','_blank','_parent','_self')),
    CreatedDate datetime DEFAULT(GETDATE())
)
GO
--[1] 트랜잭션을 시작
BEGIN TRAN
--[2] 테이블에 값을 INSERT 후 SELECT
INSERT FrontBoard VALUES('첫번째입력','첫번째입력','_blank',getdate())  --입력됨.
SELECT * FROM FrontBoard
--[3] 롤백해본 후 SELECT
ROLLBACK TRAN
SELECT * FROM FrontBoard  -- 입력된 값이 취소된다.

--[4] 다시 입력해본 후 COMMIT
BEGIN TRAN
INSERT FrontBoard VALUES('첫번째입력','첫번째입력','_blank',getdate())  --입력됨.
COMMIT TRAN
SELECT * FROM FrontBoard  -- 제대로 입력됨

--[5] UPDATE/DELETE문에는 반드시 트랜잭션 만들자.
BEGIN TRAN
    UPDATE ...
    DELETE ...
    SELECT ...
--COMMIT TRAN
ROLLBACK TRAN

롤백(Rollback)

롤백은 데이터베이스 조작 작업이 비정상적으로 종료되었을 때 이전 상태로 복구하는 명령어입니다. 변경된 모든 내용을 취소하고 데이터베이스를 이전 상태로 되돌립니다.

트리거(Trigger)란?

트리거(Trigger)는 데이터베이스에서 하나의 구문에 값이 입력될 때 자동적으로 다른 테이블의 값을 변경하는 등의 업무를 할 수 있도록 하는 SQL Server의 데이터베이스 객체입니다.

트리거 만들기 문법(온라인 설명서 참조)

CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{
     { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
         [ WITH APPEND ]
         [ NOT FOR REPLICATION ]
         AS
         [ { IF UPDATE ( column )
             [ { AND | OR } UPDATE ( column ) ]
                 [ ...n ]
         | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                 { comparison_operator } column_bitmask [ ...n ]
        } ] 
         sql_statement [ ...n ] 
    } 
}

트리거 만드는 문법은 CREATE TRIGGER문을 사용하며 나머지 구문들은 저장 프로시저 생성 구문과 비슷합니다.

트리거 수정하기 문법

트리거를 수정하는 문법은 CREATE TRIGGER 대신에 ALTER TRIGGER문을 사용합니다.

트리거 삭제하기 문법

DROP TRIGGER 트리거이름

예제2. 트리거 생성/수정/삭제(트리거.sql)

--[0] 트리거 연습용 테이블 생성
--DROP TABLE dbo.[구매테이블]
CREATE TABLE dbo.[구매테이블]
(
	CustomerID int,
	ProductID int,
	Quantity int
)
GO
--DROP TABLE dbo.[재고테이블]
CREATE TABLE dbo.[재고테이블]
(
	ProductID int,
	Quantity int
)
GO

--[1] 재고테이블에 1번, 2번 제품을 10개 넣어놓음(가상)
INSERT [재고테이블] VALUES(1,10)
INSERT [재고테이블] VALUES(2,10)
SELECT * FROM [재고테이블]

--[2] 구매가 한번이루어지면 자동으로 구매테이블에서 감소시키는 트리거 작성
CREATE TRIGGER [자동재고감소]
ON [구매테이블]
FOR INSERT
AS
	UPDATE [재고테이블] SET Quantity = Quantity - 1
	PRINT '재고테이블에서 1개 감소했습니다.'

--[3] 구매테이블에 INSERT문을 실행시켜본다.
INSERT [구매테이블] VALUES(1,1,1)

--[4] 트리거에의해 재고테이블의 모든 필드값이 자동으로 감소됨을 알 수 있다.
SELECT * FROM [재고테이블]

--[5] 트리거를 수정해보자: 구매개수만큼 줄이도록 수정

ALTER TRIGGER [자동재고감소]
ON [구매테이블]
FOR INSERT
AS
	DECLARE @ProductID int, @Quantity int
	SET @ProductID = 0
	SET @Quantity = 0
	SELECT @ProductID = ProductID, @Quantity = Quantity 
	FROM INSERTED  --INSERTED에 의해서 입력된 데이터 받아옴

	UPDATE [재고테이블] SET Quantity = Quantity - @Quantity
	WHERE ProductID = @ProductID
GO
--[6] 1번 제품만을 1개 감소
INSERT [구매테이블] VALUES(1,1,1)
SELECT * FROM [재고테이블]
--[7] 2번 제품만을 3개 감소
INSERT [구매테이블] VALUES(1,2,3)
SELECT * FROM [재고테이블]

--[8] 트리거 삭제
DROP TRIGGER [자동재고감소]

[5]번 항목을 실행한 후에 [6]번과 같이 [구매테이블]에 1번 제품을 1개 구매하는 가상 입력을 해보면, [재고테이블]에서는 당연히 1번 제품이 1개 감소됨을 알 수 있습니다. 마찬가지로 [7]번 항목에서는 [구매테이블]에 2번 제품이 3개 구매된다면, [재고테이블]에서 2번 제품이 3개가 감소함을 알 수 있습니다.

마지막으로 [8]번과 같이 DROP TRIGGER문으로 트리거를 삭제할 수 있습니다.