트리거(TRIGGER - DML TRIGGER)

 

출처 -  http://egloos.zum.com/sweeper/v/3061012

 

1. 트리거 개요

트리거는 사전적 의미로 '방아쇠'를 뜻하는데, 단어 뜻 그대로, 데이터베이스에 어떠한 일이 일어나면 자동으로 실행되는 개체를 의미한다.

트리거는 제약 조건과 더불어 데이터 무결성을 위해서 SQL Server에서 사용할 수 있는 또 다른 기능이다.

트리거는 그 영향 범주에 따라 크게 다음 세 가지로 나눌 수 있다.

• DML 트리거 : 테이블/뷰와 관련되어 DML문(INSERT/UPDATE/DELETE)의 이벤트가 발생할 때 작동
• DDL 트리거 : SQL 서버나 데이터베이스에 DDL문(CREATE/ALTER/DROP)의 이벤트가 발생할 때 작동
• LOGON 트리거 : 사용자의 로그온 이벤트가 발생할 때 작동

참고로, 일반적으로 트리거라 하면 대부분 DML 트리거를 의미하는 것이다.

트리거는 저장 프로시져와 작동이 비슷하지만, 직접 실행시킬 수는 없고 오직 이벤트가 발생한 경우에만 실행된다.

그리고, 트리거는 저장 프로시져와는 달리 매개 변수를 지정하거나 반환값을 사용할 수 없다.

또한, 트리거는 시스템 성능을 저하시키는 요인이 되기도 하므로, 꼭 필요한 곳에서만 주의 깊게 사용하여야 한다.

성능 저하 부분에 대한 자세한 이야기는 트리거가 발생시키는 임시 테이블 챕터에서 자세히 다루겠다.

2. DML 트리거

MSDN 링크 : DML 트리거

DML 트리거는 테이블 또는 뷰와 관련되어 DML문의 이벤트가 발생할 때 작동하는 트리거를 일컫는다.

DML문이란, Data Manipulation Language 즉, 테이블/뷰의 행을 추가/갱신/삭제하는 INSERT/UPDATE/DELETE를 말한다.

DML 트리거를 정의하는 형식은 다음과 같다.

1. CREATE TRIGGER 트리거이름
2. ON { 테이블 이름 | 뷰 이름 }
3. [ WITH ENCRYPTION ]                         -- 트리거의 내용을 암호화. 추후 내용 확인 불가.
4. { FOR | AFTER | INSTEAD_OF }                -- 3개 중 하나를 선택 (FOR와 AFTER는 동일)
5. { [ INSERT] [,] [ UPDATE ] [,] [ DELETE ] } -- 콤마를 이용해 여러 개를 조합할 수 있다.
6. AS
7.     실행할 SQL 구문들

DML 트리거는 다음과 같이 크게 두 가지로 분류할 수 있다.

(CLR 트리거도 있지만 여기서는 설명을 패스하겠다)

1. AFTER/FOR 트리거

테이블에 INSERT/UPDATE/DELETE의 작업이 수행된 후에 작동하는 트리거이다.

(테이블에서만 작동하고 VIEW에서는 작동하지 않는다)

DML문이 수행된 후에 작동하기에 DML문을 수행하는 과정에서 제약 조건을 위배한다면 AFTER 트리거는 발동되지 않는다.

그리고, AFTER 트리거는 각 동작(INSERT/UPDATE/DELETE)에 대해 여러 개의 트리거를 걸 수 있다.

이해를 돕기 위해 아래 UserTable에 수정/삭제시 변경되기 전 데이터들을 백업하는 예제를 살펴 보자.

 

위 UserTable에 UPDATE/DELTE 발생시 변경되기 전의 데이터를 백업하는 테이블을 하나 생성하고,

UPDATE/DELETE 발생시 이를 백업하는 트리거도 생성시켜 보자.

1. USE Son
2. GO
3.  
4. -- 백업 테이블 생성
5. CREATE TABLE UserTable_backup
6. (
7.     ID VARCHAR(4) NOT NULL PRIMARY KEY,
8.     Name NVARCHAR(8) NOT NULL,
9.     BirthYear SMALLINT NOT NULL,
10.     Addr NVARCHAR(4) NOT NULL,
11.     Mobile1 CHAR(3),
12.     Mobile2 CHAR(8),
13.     Height SMALLINT NOT NULL,
14.     RegDate date NOT NULL,
15.     modType NCHAR(2) NOT NULL,      -- 변경 타입
16.     modDate date NOT NULL,          -- 변경한 날짜
17.     modeUser NVARCHAR(256) NOT NULL -- 변경한 사용자
18. )
19. GO
20.  
21. -- 백업 AFTER 트리거 생성/부착
22. CREATE TRIGGER trg_BackupUserTable
23. ON UserTable
24. AFTER UPDATE, DELETE
25. AS
26.     DECLARE @modType NCHAR(2)
27.  
28.     IF (COLUMNS_UPDATED() > 0)
29.     BEGIN
30.         SET @modType = N'수정'
31.     END
32.     ELSE
33.     BEGIN
34.         SET @modType = N'삭제'
35.     END
36.  
37.     -- 임시 테이블 deleted 테이블에서 변경되기 전 데이터를 UserTable_backup에 추가
38.     INSERT INTO UserTable_backup
39.     SELECT
40.         ID, Name, BirthYear, Addr, Mobile1, Mobile2, Height, RegDate, @modType, GETDATE(), USER_NAME()
41.     FROM
42.         deleted
43. GO

위와 같이 트리거를 생성하고 나면, 해당 트리거는 SSMS의 개체 탐색기에서 아래와 같이 보여진다.

 

트리거 trg_BackupUserTable은 UserTable에 부착되었으므로, UserTable 하위의 트리거에서 보여지는 것이다.

위 예제 마지막 부분에 보면 변경되기 전의 데이터를 deleted 테이블에서 읽어오고 있다.

deleted 테이블이란, UPDATE 또는 DELETE가 수행되기 전의 데이터가 잠시 저장되는 임시 테이블이라고 생각하면 된다.

자세한 내용은 임시 테이블 챕터에서 확인할 수 있다.

자, 이제 백업 테이블과 백업 트리거를 만들었으니, 실제로 데이터를 변경시켜보고, 백업이 되는지 확인해 보자.

1. USE Son
2. GO
3.  
4. -- 데이터 변경시 백업 테이블에 데이터가 추가될 것이다.
5. UPDATE UserTable
6. SET Mobile1 = '010'
7. WHERE ID = 'KJD'

ID = 'KJD' 행의 Mobile1 열을 010으로 갱신시켰다.

그 결과 백업 테이블에는 변경되기 전 데이터가 추가되었다.

 

이제 AFTER 트리거에 대해 감이 좀 오는가?

위 예제를 조금 다르게 표현하면, '데이터 변경에 대한 캡쳐(CDC)'라 할 수 있다.

SQLServer 2008부터 도입된 Changed Data Capture 기능은 위 예제에서의 백업 테이블 생성, 트리거 생성을 더욱 더 편리하게 수행할 수 있도록 해 준다.

CDC에 대한 자세한 내용은 MSDN의 Change Data Capture 페이지를 루트로 하여, 하위 페이지들에서 자세히 확인할 수 있다.

생각보다 내용 정리가 무척 잘 되어 있다. (물론 양도 방대하다)

이번에는 조금 다른 케이스, UserTable에 데이터를 추가하지 못하도록 하는 트리거를 만들어 보자.

더불어 데이터를 추가하려 하면, 괜히 겁도 한 번 줘보도록 하자.

1. CREATE TRIGGER trg_ProhibitInsert
2. ON UserTable
3. AFTER INSERT
4. AS
5.     RAISERROR(N'데이터의 입력을 시도하였습니다',10,1)
6.     RAISERROR(N'귀하가 시도한 데이터의 입력에 대한 정보가 서버에 기록되었습니다',10,1)
7.     RAISERROR(N'데이터 입력은 취소되었으며, 곧 정보보안팀에서 연락이 갈 것입니다',10,1)
8.  
9.     ROLLBACK TRAN
10. GO

RAISERROR() 함수는 강제로 오류를 발생시키는 함수이다.

이 함수에 의해서 방금 데이터 입력을 시도한 사용자에게 경고 메시지를 보내게 된다.

그리고 마지막의 'ROLLBACK TRAN'을 만나면 사용자가 시도한 INSERT는 롤백이 되어서 테이블에 적용되지 않는다.

트리거가 완전히 종료되어야만 사용자의 트랜잭션도 정상적으로 커밋되기 때문이다.

아래처럼 데이터를 추가하려 시도하면 데이터 추가가 취소되는 것이다.

1. -- 데이터 입력을 시도해 보지만...
2. INSERT INTO UserTable
3. VALUES ('ABC', N'에비씨', 1977, N'서울', NULL, NULL, NULL, GETDATE())
4. GO
5.  
6. -- trg_ProhibitInsert 트리거에 의해 아래와 같이 출력된다
7. -- 데이터의 입력을 시도하였습니다
8. -- 귀하가 시도한 데이터의 입력에 대한 정보가 서버에 기록되었습니다
9. -- 데이터 입력은 취소되었으며, 곧 정보보안팀에서 연락이 갈 것입니다
10. -- 메시지 3609, 수준 16, 상태 1, 줄 2
11. -- 트리거가 발생하여 트랜잭션이 종료되었습니다. 일괄 처리가 중단되었습니다.

2. INSTEAD OF 트리거

INSTEAD OF 트리거는 말 그대로, 작동되는 쿼리 '대신에' INSTEAD OF 트리거가 작동한다.

즉, 테이블에 DML문을 수행하기 전에, 부착되어 있는 트리거가 대신 동작하는 것이다.

이렇기에, 각 DML 동작별 여러 개의 트리거를 걸 수 있던 AFTER 트리거와는 다르게

INSTEAD OF 트리거는 DML 동작당 하나의 트리거만 연결시킬 수 있다.

당연하지 않은가? 여러 개를 연결해 놓으면 어떤 녀석이 대신할 지 결정할 수 없을테니 말이다.

그리고 테이블에만 적용될 수 있었던 AFTER 트리거와 달리 INSTEAD OF 트리거는 테이블과 뷰에 모두 적용될 수 있다.

주로 뷰에 사용된다고 보면 되는데, 복합 뷰의 경우 데이터를 추가/수정할 수 없는데

이럴 때 INSTEAD OF 트리거를 이용하여 데이터를 분리하여 추가/수정하여 결국 복합 뷰에 데이터 추가/수정을 가능하게 할 수 있는 것이다.

그러면, INSTEAD OF 트리거의 대표적인 예, 복합 뷰에 데이터 추가/수정을 가능하게 하는 예제를 살펴보도록 하자.

우선, 아래와 같이 UserTable과 BuyTable이 있다.

 

이 둘을 조인시켜 DeliveryView를 제작해 보자.

1. CREATE VIEW DeliveryView
2. AS
3.     SELECT b.ID, u.Name, b.GoodName, b.Price, b.Amount, u.Addr
4.     FROM BuyTable b
5.         INNER JOIN UserTable u
6.         ON b.ID = u.ID
7. GO

그 결과 DeliveryView는 아래와 같이 보일 것이다.

 

이 상황에서 새로운 고객 '김기뤼'에게 주문 요청을 받아서, DeliveryView에 배송 정보를 추가하려 하면...

1. -- 복합 뷰에 데이터를 추가하려 시도한다.
2. INSERT INTO DeliveryView
3. VALUES ('KKL', N'김기뤼', N'구두', 50, 1, N'광명')
4.  
5. -- 아래와 같은 에러 메시지가 발생한다
6. -- 메시지 4405, 수준 16, 상태 1, 줄 2
7. -- 뷰 또는 함수 'DeliveryView'은(는) 수정 시 여러 기본 테이블에 영향을 주므로 업데이트할 수 없습니다.

이제, 복합 뷰에 데이터를 추가/수정할 수 있는 INSTEAD OF 트리거를 생성/부착시켜보자.

1. -- DeliveryView에 데이터 추가를 가능하게 하는 INSTEAD OF 트리거
2. -- 핵심 아이디어는 INSERT를 UserTable, BuyTable에 나누어 수행토록 하는 것
3. CREATE TRIGGER trg_InsertDeliveryView
4. ON DeliveryView
5. INSTEAD OF INSERT
6. AS
7.     BEGIN
8.         -- UserTable에 추가
9.         INSERT INTO UserTable (ID, Name, BirthYear, Addr, RegDate)
10.         SELECT ID, Name, 1900, Addr, GETDATE()
11.         FROM inserted
12.                
13.         -- BuyTable에 추가
14.         INSERT INTO BuyTable (ID, GoodName, Price, Amount)
15.         SELECT ID, GoodName, Price, Amount
16.         FROM inserted
17.     END
18. GO

위에서 보듯이 데이터의 추가/수정이 불가능한 복합 뷰에 INSTEAD OF 트리거를 추가하여,

복합 뷰의 INSERT 문을 대신해 UserTable/BuyTable에 각각 데이터를 추가하는 방식으로 복합 뷰에 데이터 추가를 가능케 했다.

위에서 보면, UserTable/BuyTable에 데이터를 추가할 때 inserted 테이블에서 데이터를 읽어와 추가하는데,

inserted 테이블은 INSERT 또는 UPDATE가 수행된 후 추가/수정된 데이터가 잠시 저장되는 임시 테이블이라고 생각하면 된다.

자세한 내용은 임시 테이블 챕터에서 확인할 수 있다.

이제 다시 DeliveryView에 데이터를 추가하려 시도하면, trg_InsertDeliveryView 트리거에 의해 데이터가 제대로 추가되는 것을 확인할 수 있다.

3. DML 트리거가 생성하는 임시 테이블

AFTER/INSTEAD OF 트리거의 예제에서 등장했던 임시 테이블에 대해 자세히 알아보자.

DML 트리거에서 INSERT, UPDATE, DELETE 작업이 수행되면 임시로 사용되는 테이블이 두 개 있다.

• inserted 테이블
• deleted 테이블

이 두 테이블은 사용자가 임의로 변경 작업을 할 수는 없고, 단지 참조(Select)만 할 수 있다.

1. inserted 테이블

INSERT와 UPDATE 작업을 할 때 변경 후의 행 데이터와 동일한 데이터가 저장된다.

다시 말해 테이블에 INSERT 트리거나 UPDATE 트리거를 부착해 놓았다면,

해당 테이블에 INSERT나 UPDATE가 수행될 경우 우선은 해당 테이블의 데이터가 삽입 또는 변경되고,

그 다음에 inserted 테이블에 삽입 또는 변경된 동일한 데이터가 저장되는 것이다.

(변경 전의 데이터가 아니다. 변경 후의 데이터임을 주의하자)

2. deleted 테이블

UPDATE와 DELETE 작업이 수행되면 우선 해당 테이블의 행 데이터가 수정/삭제되고,

deleted 테이블엔 수정/삭제되기 전의 데이터가 저장된다.

결론적으로 트리거가 작동할 경우, 변경된 후의 데이터를 참조하기 위해서는 inserted 테이블을,

변경되기 전의 데이터를 참조하기 위해서는 deleted 테이블을 참조하면 되는 것이다.

4. 임시 테이블과 성능

처음 트리거 개요에 대해 설명할 때 트리거가 성능을 저하시키는 요인이 될 수 있다고 했다.

우선, 트리거가 동작할 때마다 inserted 테이블이든 deleted 테이블이 메모리에 임시로 생성되었다 소멸된다.

이 테이블의 생성, 소멸 비용이 계속해서 발생하는 것이다.

게다가 INSERT 트리거는 inserted 테이블 1개에 영향을, DELETE 트리거는 deleted 테이블 1개에 영향을 주지만,

UPDATE 트리거는 inserted, deleted 테이블 2개 모두에 영향을 미치게 된다.

따라서, 트리거를 사용하는 것 자체가 지속적인 성능 비용을 물게 되는 것이다. 특히 UPDATE 트리거는 2배로 말이다.

5. 트리거의 정보 확인, 변경, 삭제

1. 트리거의 정보 확인

1. EXEC sp_helptrigger DeliveryView
2. GO
3.  
4. EXEC sp_helptext trg_InsertDeliveryView
5. GO

sp_helptrigger (테이블/뷰 이름)을 실행하면 해당 트리거의 정보를 확인할 수 있다.

 

sp_helptext (트리거 이름)을 실행하면 해당 트리거의 내용을 볼 수 있다.

만약, 트리거 생성시 WITH ENCRYPTION 옵션을 줬다면, 내용이 보이지 않는다.

 

2. 트리거 내용 변경

ALTER TRIGGER를 사용하면 된다.

사용법은 다른 ALTER 와 동일하기에 자세한 설명은 패스

3. 트리거 삭제

DROP TRIGGER를 사용하면 된다.

그리고, 트리거는 테이블 또는 뷰에 귀속되는 개체이다.

따라서, 트리거가 부착된 테이블, 뷰를 삭제하면 트리거도 함께 삭제된다.