18-4.SQL Server의 트리거

1.트리거 생성

SQL Server의 트리거도 오라클의 트리거와 개념이나 사용 목적은 거의 같고 제공하는 기능도 비슷하다. 이쪽에 있는 기능은 결국 저쪽에도 있을 수밖에 없다. 그러나 형식이 다르다 보니 구문상의 차이가 있고 기능의 범위도 약간씩 다르다.

이미 오라클 편에서 트리거의 개념과 종류, 사용 실습을 다 해 본 상태이므로 가급적 같은 예제로 똑같은 실습을 해 보자. 요약적으로 정리하며 기능적인 차이점 위주로 알아보므로 오라클의 트리거를 먼저 알아야 SQL Server의 트리거를 이해할 수 있다. 트리거를 정의하는 기본 형식은 다음과 같다.

CREATE TRIGGER 이름

ON { 테이블 | 뷰 }

[WITH ENCRYPTION]

{ FOR | AFTER | INSTEAD OF } { INSERT, UPDATE, DELETE }

AS

본체

오라클은 AFTER UPDATE ON tCity순인데 비해 SQL Server는 ON tCity AFTER UPDATE 순이다. 문장 트리거, 행 트리거 구분은 없고 BEFORE 트리거도 없으며 INSTEAD OF가 그 역할을 대신한다. FOR는 AFTER와 동의어이다. 여러 이벤트를 동시에 지정할 때는 콤마로 구분한다.

헤더와 본문 사이에 AS 키워드가 있어 본체의 BEGIN END는 생략해도 무방하지만 범위를 명확히 하기 위해 블록을 감싸는 것이 좋다. WITH ENCRYPTION 옵션은 트리거 본문을 암호화하여 저장한다. tCity 테이블 변경에 대한 트리거는 다음과 같다.

CREATE TRIGGER TR_Message

ON tCity AFTER UPDATE

AS

BEGIN

PRINT('도시 정보를 갱신하였습니다.');

END;

tCity 테이블을 갱신할 때 호출되어 메시지를 출력한다. 다음 명령으로 트리거가 잘 호출되는지 테스트해 보자.

UPDATE tCity SET popu = popu + 1 WHERE name = '서울';

UPDATE tCity SET popu = popu + 1 WHERE region = '경기';

서울의 인구를 증가시키면 TR_Message가 호출된다. 경기도의 도시 둘을 한꺼번에 변경해도 트리거는 한 번만 호출되며 메시지도 한 번만 출력한다. 행 트리거가 따로 없고 명령 하나에 대해 트리거를 한 번 호출하는 식이다. 트리거 본문에서는 다음 임시 테이블과 함수로 변경 사실을 알아낸다.

테이블, 함수	설명
inserted 테이블	INSERT, UPDATE시 변경된 후의 레코드이며 어떤 레코드가 삽입되었는지, 레코드가 어떻게 바뀌었는지 알 수 있다.
deleted 테이블	DELETE, UPDATE시 삭제 또는 변경 전의 레코드이며 쿼리문 실행전의 레코드를 알 수 있다.
UPDATE 함수	특정 열이 변경되었는지 조사한다. DELETE 명령은 특정 열만 삭제하는 기능이 없어 이 함수를 사용할 수 없다.
COLUMNS_UPDATED 함수	변경된 열의 비트 마스크이며 하위 비트부터 첫 번째 필드와 대응한다.

inserted, deleted는 오라클의 NEW, OLD에 해당하되 오라클은 레코드 하나를 전달하는데 비해 SQL Server는 여러 레코드를 포함한 테이블이라는 점이 다르다. 두 임시 테이블에 어떤 정보가 저장되는지 트리거 본문에서 덤프해 보자. 트리거를 수정할 때는 DROP 후 다시 만들거나 아니면 ALTER TRIGGER 명령을 사용한다.

ALTER TRIGGER TR_Message

ON tCity AFTER UPDATE

AS

BEGIN

SELECT * FROM deleted;

SELECT * FROM inserted;

END;

변수를 경유할 필요 없이 SELECT 명령을 바로 쓸 수 있어 결과 확인이 편리하다. 이 트리거를 설치해 놓은 상태에서 경기도 도시의 인구수를 변경하면 두 테이블이 결과창에 나타나며 변경한 모든 레코드가 기록되어 있다.

deleted 테이블에는 원래 인구수가 있고 inserted 테이블에는 변경된 인구수가 있다. 오라클의 행 트리거는 레코드 하나씩 전달하는데 비해 SQL Server는 모든 레코드를 통째로 전달하여 변경 내역을 한 번에 알 수 있다. 트리거에서 각 도시의 전, 후 인구를 출력해 보자.

ALTER TRIGGER TR_Message

ON tCity AFTER UPDATE

AS

BEGIN

SELECT name, popu, ' => ', (SELECT popu FROM inserted WHERE name = D.name) FROM deleted D;

END;

연관 서브쿼리 하나로 쉽게 알아낼 수 있다. 레코드별로 트리거가 발생하지 않아 집합적으로 다루어야 하는 부담이 있지만 모아서 일괄 처리할 수 있어 성능상 유리하다.

ON tCity AFTER INSERT, UPDATE, DELETE 식으로 동시에 여러 개의 이벤트를 받을 때는 임시 테이블에 레코드가 있는지 점검해 보면 어떤 이벤트가 발생했는지 알 수 있다. inserted에만 있으면 삽입한 것이고 deleted에만 있으면 삭제한 것이며 양쪽 다 있으면 갱신한 것이다.

트리거를 삭제할 때는 DROP TRIGGER 명령을 사용한다. 더 이상 이벤트를 받을 필요가 없다면 제거하여 시스템 부담을 덜어주는 것이 좋다. 일시 중지만 해 놓을 때는 다음 명령을 사용한다.

ALTER TABLE 테이블이름

{ ENABLE | DISABLE } TRIGGER ( 트리거 이름 | ALL }

ALL은 테이블의 모든 트리거를 중지하며 이름을 지정하면 해당 트리거만 중지한다. 다음 명령을 실행한 후 tCity를 업데이트하면 더 이상 메시지를 출력하지 않는다.

ALTER TABLE tCity DISABLE TRIGGER TR_Message;

DISABLE를 ENABLE로 바꾸면 트리거가 다시 활성화된다.

2.트리거와 무결성

트리거는 변화의 모든 내역을 다 들여다 볼 수 있다는 점에서 무결성을 지키는 최후의 보루이다. 다음 트리거는 1억 초과 인구수를 거부한다.

ALTER TRIGGER TR_Message

ON tCity AFTER UPDATE

AS

BEGIN

IF (SELECT popu FROM inserted) > 10000

BEGIN

PRINT('인구가 1억을 넘을 수는 없습니다.');

ROLLBACK;

END

END;

조건이 맞지 않을 경우 롤백해 버리면 그만이다. 서울 인구를 12345로 갱신하면 즉시 거부한다. 오라클은 트리거에서 트랜잭션을 다룰 수 없어 예외를 던져야 하지만 SQL Server의 트리거는 그 자체가 트랜잭션이어서 취소 가능하다. 다음 트리거는 최씨의 입사를 거부한다.

CREATE TRIGGER TR_PreventChoi

ON tStaff AFTER INSERT

AS

IF EXISTS (SELECT * FROM inserted WHERE name LIKE '최%')

BEGIN

PRINT '최가는 안되!';

ROLLBACK TRANSACTION;

END

새로 삽입한 사원의 정보를 조사하여 이름이 최로 시작하는 사람이 있으면 롤백하여 전체를 삽입 거부한다. 거부하는 대신 특정한 값으로 강제 변경하려면 삽입한 레코드를 찾아 원하는 값으로 갱신해 버리면 된다. 다음 트리거는 최씨를 입력하면 김씨로 변경한다.

ALTER TRIGGER TR_PreventChoi

ON tStaff AFTER INSERT

AS

BEGIN

UPDATE tStaff SET name = '김' + SUBSTRING(name, 2, LEN(name) - 1) WHERE name LIKE '최%';

END

INSTEAD OF 트리거는 오라클과 사용 목적이 같다. 주로 뷰에 대해 사용하며 뷰에 입력한 정보를 각 테이블에 분배하는 역할을 한다. vCarMaker 뷰를 생성한 후 다음 트리거를 정의한다.

CREATE TRIGGER TR_AddNewCar

ON vCarMaker INSTEAD OF INSERT

AS

BEGIN

INSERT INTO tCar (car, capacity, price, maker) SELECT car, capacity, price, maker FROM inserted;

INSERT INTO tMaker (maker, factory, domestic) SELECT maker, factory, domestic FROM inserted;

END

inserted 테이블의 정보를 tCar와 tMaker로 골고루 분배하여 나누어준다. 원래의 쿼리는 실행하지 않고 두 개의 쿼리를 대신 실행한다.

3.중첩 트리거

다음 트리거는 쇼핑몰의 수험생 신입 회원에 대해 예치금을 두 배로 늘려 준다.

CREATE TRIGGER TR_DoubleMoney

ON tMember AFTER INSERT

AS

DECLARE @name CHAR(20)

DECLARE @age INT

SELECT @name = member, @age = age FROM inserted;

IF @age = 18

UPDATE tMember SET money = money * 2 WHERE member = @name;

AFTER 트리거를 받았으므로 레코드를 이미 삽입한 후이다. 이 상태에서 inserted 테이블에서 이름과 나이를 조사하여 18세 수험생이면 UPDATE 명령으로 해당 회원의 예치금을 두 배로 늘려 준다. INSERT 트리거에서 UPDATE 명령을 처리하였으므로 중첩이 발생할 수 있다. 다음 트리거를 정의해 보자.

CREATE TRIGGER TR_MoneyChange

ON tMember

AFTER UPDATE

AS

DECLARE @name CHAR(20)

IF UPDATE(money)

BEGIN

SELECT @name = member FROM inserted;

PRINT @name + '의 예치금이 갱신되었습니다. 부정이 아닌지 확인해 보십시오';

END;

신입 회원이 들어 오면 INSERT 트리거가 발생하며 수험생일 경우 연이어 UPDATE 트리거가 발생한다. SQL Server는 디폴트로 32단계까지 중첩을 허용하지만 재귀는 허용하지 않는다. 중첩 허용은 서버의 nested triggers 옵션으로, 재귀 허용은 데이터베이스의 recursive triggers 옵션으로 조정한다.

DDL 트리거는 서버나 데이터베이스의 이벤트에 대해 발생한다. 다음 트리거는 모든 서버에서 데이터베이스가 생성 및 파괴되는 시점을 감지한다.

CREATE TRIGGER NewDataBase

ON ALL SERVER

AFTER CREATE_DATABASE, DROP_DATABASE

AS

PRINT '새로운 DB가 생성 또는 파괴되었습니다.';

원한다면 해당 동작을 하지 못하도록 롤백하거나 새로 생성한 DB에 대한 초기화 작업을 할 수도 있다. 다음 명령으로 새로운 데이터베이스를 생성했다가 파괴해 보자.

CREATE DATABASE NewDB

DROP DATABASE NewDB

두 번 모두 메시지가 출력된다. DDL 트리거를 삭제할 때는 이름뿐만 아니라 트리거의 범위도 지정해야 한다.

DROP TRIGGER NewDataBase ON ALL SERVER

이렇게 하면 원래대로 돌아온다. 일반 개발자가 DDL 트리거를 쓸 경우는 흔치 않으며 가급적이면 제약 조건을 통해 사고를 방지하는 것이 바람직하다.