[MSSQL] 트리거

트리거란 ?

테이블 또는 뷰와 관련되어 DML문의 이벤트가 발생될 때 작동하는 데이터베이스 개체

트리거 종류

- DML트리거 : 테이블 또는 뷰와 관련되어 DML문의 이벤트가 발생될 때 작동

- DDL트리거 : 서버나 데이터베이스에 DDL문의 이벤트가 발생될 때 작동

- LOGON트리거 : 사용자의 로그온 이벤트가 발생하면 작동

트리거 사용 예시

-- 간단한 테이블 생성
USE tempdb;
CREATE TABLE testTbl(id INT, txt NVARCHAR(5));
GO
INSERT INTO testTbl VALUES(1,N'싸이'),(2,N'빅뱅'),(3,N'투피엠');
 
-- testTbl에 트리거 부착
CREATE TRIGGER testTrg        -- 트리거 이름
ON testTbl                    -- 트리거를 부착할 테이블
AFTER DELETE, UPDATE        -- 삭제, 수정 후에 작동하게 지정
AS
    PRINT(N'트리거가 작동했습니다');    -- 트리거를 실행할 때 작동되는 코드들
 
-- 데이터를 삭입,수정,삭제해 보자.
INSERT INTO testTbl VALUES(4,N'인피니티');
UPDATE testTbl SET txt = N'투에이엠' WHERE id = 3;
DELETE testTbl WHERE id = 4;
 

- 결과

DML 트리거의 종류

(1) AFTER 트리거 : INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 해당 작업 후에 작동한다. 테이블에만 작동하며 뷰에는 작동하지 않는다.

(2) INSTEAD OF 트리거 : INSERT, UPDATE, DELETE 등의 작업 이벤트가 발생하기 전에 작동하는 트리거, 테이블뿐 아니라 뷰에서도 작동되며, 주로 뷰에 업데이트가 가능하게 할 때 사용된다.

(3) CLR 트리거 : T-SQL 저장 프로시저 대신 .NET Framework에서 생성되는 트리거이다.

트리거 문법

CREATE TRIGGER 트리거이름
ON    { 테이블이름 | 뷰이름 }
[ WITH ENCRYPTION ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS
    실행할 SQL 

·WITH ENCRYPTION : 트리거의 내용을 암호화시켜서 추후에 내용을 확인할 수 없게한다.

·FOR | AFTER | INTEAD OF : DML 트리거의 종류 지정 (FOR는 AFTER와 같다)

·INSERT | UPDATE | DELETE : 트리거가 작동될 이벤트, 하나 이상의 이벤트를 조합해서 작동시킬 수 있다.

(1) AFTER 트리거 사용 예시

-- insert나 update가 일어나면 변경되기 전의 데이터를 저장할 테이블 생성
USE sqlDB;
DROP TABLE buyTbl;
CREATE TABLE backup_userTbl
(
    userID            char(8) NOT NULL PRIMARY KEY,
    name            nvarchar(10) NOT NULL,
    birthYear        int NOT NULL,
    addr            nchar(2) NOT NULL,
    mobile1            char(3),
    mobile2            char(8),
    height            smallint,
    mDate            date,
    modType            nchar(2),    -- 변경된 타입. '수정' 또는 '삭제'
    modDate            date,        -- 변경된 날짜
    modUser            nvarchar(256)    -- 변경된 사용자
)
 
-- 트리거 생성 및 부착
CREATE TRIGGER trg_BackupUserTbl
ON userTbl
AFTER update, delete
AS
    DECLARE @modType NCHAR(2)    -- 변경 타입
 
    IF (COLUMNS_UPDATED() >0)
        BEGIN
            SET @modType = N'수정'
        END
    ELSE
        BEGIN
            SET @modType = N'삭제'
        END
 
    INSERT INTO backup_userTbl
        SELECT userID,name,birthYear,addr,mobile1,mobile2,height,mDate,@modType,GETDATE(),USER_NAME()
        FROM deleted;
 
            API
        
-- deleted 테이블이란 update 또는 delete가 수행되기 전의 데이터가 잠시 저장되는 임시 테이블이다.
 
-- 데이터 변경하기
-- 업데이트 및 삭제
UPDATE userTbl SET addr = N'뭉고' WHERE userID = 'JKW';
DELETE userTbl WHERE height >= 177;
 
-- 업데이트 및 삭제 결과 확인
SELECT * FROM userTbl;

- 결과

---

DROP TABLE VS TRUNCATE TABLE VS DELETE 문 : 링크가기

'inserted' 임시 테이블과 'deleted' 임시테이블 : 링크가기

---

(2) INSTEAD OF 트리거 사용 예시

 : 다음 예시는 배달 정보를 추가하면 자동으로 구매 테이블과 고객 테이블에 기록되는 트리거이다.

-- 배송 정보 뷰 생성
CREATE VIEW uv_deliver
AS
    SELECT b.userID, u.name, b.prodName, b.price, b.amount, u.addr
    FROM buyTbl b
    INNER JOIN userTbl u
    ON b.userID = u.userID
 
-- 새로운 고객 추가
INSERT INTO uv_deliver VALUES('JBI',N'존밴이',N'구두',50,1,N'인천'); -- 에러 발생!
 
-- INSTEAD TRIGGER를 활용하여 배송 정보 뷰에 입력되는 정보 중에서 고객 테이블과 구매 테이블에 입력될 것을 분리해서 입력하게 지정한다.
CREATE TRIGGER trg_insert
ON uv_deliver
INSTEAD OF INSERT
AS
    BEGIN
        INSERT INTO userTbl(userID,name,birthYear,addr,mDate)
            SELECT userid,name,1900,addr,GETDATE() FROM inserted
        INSERT INTO buyTbl(userID,prodName,price,amount)
            SELECT userid,prodName,price,amount FROM inserted
    END
 
-- 새로운 고객 다시 추가
INSERT INTO uv_deliver VALUES('JBI',N'존밴이',N'구두',50,1,N'인천');
 
-- 결과 확인
SELECT * FROM userTbl WHERE userID = 'JBI';
SELECT * FROM buyTbl WHERE userID = 'JBI';

- 결과

( 위는 고객 테이블 , 아래는 구매 테이블 )

트리거의 정보 확인

(1) 시스템 저장 프로시저 사용 ('sp_helptrigger' , sp_helptext)

- sp_helptrigger : 트리거 정보 확인

· 형식

EXEC sp_helptrigger 테이블이름 혹은 뷰이름

· 결과

- sp_helptext : 트리거 내용 확인 ( WITH ENCRYPTION 옵션이 적용 중이라면 확인 불가능 )

· 형식

EXEC sp_helptext 트리거이름

· 결과

(2) SSMS에서 확인하기

트리거 이름 변경

(1) 시스템 저장 프로시저 사용 ('sp_rename')

· 형식

EXEC sp_rename '기존 트리거이름', '새로운 트리거이름'

(2) DROP하고 다시 CREATE

- 결론 : 'sp_rename'을 사용하면 외부적으로 트리거의 이름은 변경되지만 변경된 이름으로 트리거를 삭제하는 것은 불가능 하다. 때문에 DROP하고 CREATE하는 것이 좋은 방법이다. ( 뷰, 저장 프로시저, 함수 이름 등도 같다. )

DML 트리거 분류

- 다중 트리거 (Multiple Triggers) : 하나의 테이블에 같은 트리거가 여러 개 부착되어 있는 것

- 중첩 트리거 (Nested Triggers) : 트리거가 또 다른 트리거를 작동하는 것 

(서버 구성 옵션인 'nested triggers'가 ON되어 있어야 한다. / 기본값이 ON이다.)

- 재귀 트리거 (Recursive Triggers) : 트리거가 작동해서 다시 자신의 트리거를 작동시키는 것 

( ALTER DATABASE를 사용하여 RECURSIVE_TRIGGERS 설정을 활성화 필요 )

( 두 테이블이 서로 트리거에 물려 호출 되는 간접 재귀 트리거와 자신의 테이블에 순환적으로 트리거가 발생 되는 직접 재귀 트리거가 있다.)

중첩 트리거 사용 예시

-- DB 생성
USE tempdb; 
CREATE DATABASE triggerDB;
 
-- 테이블 생성
USE triggerDB;
CREATE TABLE orderTbl    -- 구매 테이블
(
    orderNo        INT IDENTITY,
    userID        NVARCHAR(5),
    prodName    NVARCHAR(5),
    orderAmount    INT
)
CREATE TABLE prodTbl    -- 물품 테이블
(
    prodName    NVARCHAR(5),
    account        INT
)
CREATE TABLE deliverTbl    -- 배송 테이블
(
    deliverNo    INT IDENTITY,
    prodName    NVARCHAR(5),
    amount        INT
);
 
-- 물품 정보 추가
INSERT INTO prodTbl VALUES('사과',100),('배',100),('귤',100);
 
-- 구매 테이블에 입력이 되면 물품 테이블의 정보가 수정되는 트리거
CREATE TRIGGER trg_order
ON orderTbl
AFTER INSERT
AS
PRINT '1. trg_order를 실행합니다.'
DECLARE @orderAmount INT
DECLARE @prodName NVARCHAR(5)
 
SELECT @orderAmount = orderAmount FROM inserted
SELECT @prodName = prodName FROM inserted
 
UPDATE prodTbl SET account -= @orderAmount
    WHERE prodName = @prodName;
 
-- 배송 테이블에 새 배송 건을 입력하는 트리거
CREATE TRIGGER trg_prod
ON prodTbl
AFTER UPDATE
AS
    PRINT '2. trg_prod를 실행합니다.'
    DECLARE @prodName NVARCHAR(5)
    DECLARE @amount INT
 
    SELECT @prodName = prodName FROM inserted
    SELECT @amount = D.account - I.account FROM inserted I,deleted D
 
    INSERT INTO deliverTbl(prodName,amount) VALUES(@prodName,@amount);
 
-- 고객이 물건을 구매한 INSERT 작업을 수행
INSERT INTO orderTbl VALUES ('JOHN','배',5);
 
-- 중첩 트리거가 잘 작동했는지 세 테이블을 모두 확인
SELECT * FROM orderTbl
SELECT * FROM prodTbl
SELECT * FROM deliverTbl

- 결과

( 맨 위는 구매 테이블 , 가운데는 물품 테이블 , 맨 아래는 배송 테이블 )

간접 재귀 트리거

(두 테이블이 서로 트리거로 물려 있는 경우)

-- 재귀 트리거 사용 가능 여부 확인
USE triggerDB;
SELECT name,is_recursive_triggers_on FROM sys.databases WHERE name = 'triggerDB';
 
-- 0(OFF)로 설정되어 있다면 1(ON)로 변경해 주자
ALTER DATABASE triggerDB SET RECURSIVE_TRIGGERS ON;
 
-- 테이블 생성
CREATE TABLE recuA                -- 간접 재귀 트리거용 테이블A
(
    id        INT IDENTITY,
    txt        NVARCHAR(10)
)
 
CREATE TABLE recuB                -- 간접 재귀 트리거용 테이블B
(
    id        INT IDENTITY,
    txt        NVARCHAR(10)
)
 
-- 간접 재귀 트리거 부착
CREATE TRIGGER trg_recuA
ON recuA
AFTER INSERT
AS
    DECLARE @id INT
    SELECT @id = trigger_nestlevel()    -- 현재 트리거 레벨 값
    PRINT '트리거 레벨 ===> ' + CONVERT(char(5),@id)
    INSERT INTO recuB VALUES('간접 재귀 트리거');
 
CREATE TRIGGER trg_recuB
ON recuB
AFTER INSERT
AS
    DECLARE @id INT
    SELECT @id = trigger_nestlevel()
    PRINT '트리거 레벨 ===> ' + CONVERT(char(5),@id)
    INSERT INTO recuA VALUES ('간접 재귀 트리거')
 
-- 데이터 입력
INSERT INTO recuA VALUES ('처음입력값');     -- 오류 발생!
 
-- 재귀 출은 최대 32번까지 호출을 허용하여 무한 루프를 방지한다.
 
-- 데이터 확인
SELECT * FROM recuA
SELECT * FROM recuB
-- 마지막에 오류 발생으로 트랜잭션을 취소시켰기 때문에 아무런 내용도 추가되지 않았다.
 
-- 재귀를 빠져 나올수 있는 루틴 추가
ALTER TRIGGER trg_recuA
ON recuA
AFTER INSERT
AS
    IF( (SELECT TRIGGER_NESTLEVEL()) >= 32)
        RETURN
 
    DECLARE @id INT
    SELECT @id = trigger_nestlevel()    -- 현재 트리거 레벨 값
    PRINT '트리거 레벨 ===> ' + CONVERT(char(5),@id)
    INSERT INTO recuB VALUES('간접 재귀 트리거');
 
ALTER TRIGGER trg_recuB
ON recuB
AFTER INSERT
AS
    IF( (SELECT TRIGGER_NESTLEVEL()) >= 32)
        RETURN
 
    DECLARE @id INT
    SELECT @id = trigger_nestlevel()
    PRINT '트리거 레벨 ===> ' + CONVERT(char(5),@id)
    INSERT INTO recuA VALUES ('간접 재귀 트리거')
 
-- 2-5. 다시 입력
INSERT INTO recuA VALUES ('처음입력값');
 
-- 2-6. 테이블 내용 확인
SELECT * FROM recuA
SELECT * FROM recuB
-- id 열의 값이 1부터 시작하지 않은 이유는 2-2에서 INSERT가 실패하더라도 IDENTITY값은 계속 증가하기 때문이다.

- 결과

( 위는 recuA 테이블 , 아래 recuB 테이블 )

- 결론

· 재귀 트리거의 사용은 SSMS에서 기본적으로 허용하지 않는다. 때문에 사용하기 위해서는 데이터베이스의 'RECURSIVE_TRIGGERS' 옵션을 ON으로 해주어야 한다.

· 트리거 작동중에 에러가 발생하면 ROLLBACK되어 아무런 변경이 일어나지 않는다.

· 재귀 트리거를 사용시 빠져나올 수 있는 루틴을 만들도록 하자.

---

참고문헌

우재남, 『뇌를 자극하는 SQL Server 2012』 한빛미디어 (2013-03-02 초판발행)