SQL TRIGGER

1. 트리거(Trigger)란

트리거란 특정 테이블의 데이터에 변경이 가해졌을 때 자동으로 수행되는 저장 프로시져라고 할 수 있습니다. 예전에 배웠던 저장 프로시져는 필요할 때마다 사용자가 직접 수행시켜야 했습니다.(물론 SQL Server가 시작될 때 자동으로 수행되게 하는 방법이 있기는 하지만) 하지만 트리거는 이와 달리 테이블의 데이터가 INSERT, UPDATE, DELETE문에 의해 변경 되어질 때 자동으로 수행되므로 이 기능을 이용하며 여러가지 작업을 할 수 있습니다. 이런 이유로 트리거를 사용자가 직접 수행 할 수는 없습니다.

예를 들어 (사원) 테이블에 새로운 데이터가 들어오면(즉 신입 사원이 들어오면) (급여) 테이블에 새로운 데이터(즉 신입 사원의 급여 정보)를 자동으로 생성하고 싶을 경우 (사원) 테이블에 트리거를 설정하여 구현 할 수 있습니다.

2. INSERTED, DELETED 테이블

트리거를 제대로 사용하기 위해서는 다음의 두 테이블에 대하여 이해하고 있어야 합니다.

o INSERTED 테이블
o DELETED 테이블

위 두 테이블은 물리적으로 존재하는 테이블이 아닙니다. 특정 테이블의 데이터에 변경이 가해졌을 때 트리거를 위해 자동으로 만들어지는 논리적인(실제로 존재하지 않는 가상의) 테이블입니다. 그 이름으로 추측되다시피 INSERTED 테이블은 테이블에 새로운 데이터가 INSERT될 때, DELETED 테이블은 테이블의 데이터가 DELETE될 때 만들어집니다.

그렇다면 왜 UPDATED 테이블이 없냐고요? 테이블의 데이터가 UPDATE 되는 것은 기존의 데이터가 DELETE되면서 새로운 데이터가 INSERT되는 과정으로 보아 DELETED 테이블과 INSERTED 테이블이 동시에 만들어집니다. 즉, DELETED 테이블에는 UPDATE 되기전 내용이 포함되어 있으며, INSERTED 테이블에는 변경된 새로운 내용이 포함되어 있습니다.

3. 트리거 만들기(CREATE TRIGGER)

CREATE TRIGGER문에 의해서 트리거를 만들수 있습니다. 물론 DROP TRIGGER문에 의해서 트리거를 제거 할 수 있으며, ALTER TRIGGER에 의해서 기존의 트리거를 수정 할 수 있습니다.

트리거를 만들때는 다음의 내용을 포함하게 됩니다.

o 트리거의 이름
o 어느 테이블에 대한 것인지 테이블 이름 지정
o INSERT, DELETE, UPDATE중 어느 경우에 수행될 트리거인지 지정(복수 선택 가능)
o 실제 수행될 쿼리문(이 쿼리문 안에서 INSERTED, DELETED 테이블을 이용하게됩니다)

트리거를 통하여 어떤 작업이든 할 수 있는게 아닙니다. 트리거를 통해 할 수 없는 작업을 몇가지 나열해 보도록 하겠습니다.

o 데이터베이스 변경(ALTER DATABASE)
o 데이터베이스 생성(CREATE DATABASE)
o 데이터베이스 제거(DROP DATABASE)
o 데이터베이스 복구(RESTORE DATABASE)
o 로그 복구(RESTORE LOG)
o 기타(DISK INIT, DISK RESIZE, LOAD DATABASE, LOAD LOG, RECONFIGURE)

우선 간단한 트리거의 예를 보도록 하겠습니다.

 

USE Pubs GO CREATE TRIGGER trg_Sales_Delete ON Sales FOR DELETE AS IF (SELECT COUNT(*) FROM Deleted) > 1 BEGIN RAISERROR('You cannot delete more than one row at a time',16,1) ROLLBACK TRANSACTION END

o 트리거의 이름은 trg_Sales_Delete 입니다.
o DELETE에 대한 트리거입니다.
o Deleted 테이블의 Row 카운트를 이용하여 몇개의 Row가 지워졌는지 확인합니다.
o Pubs 데이터베이스의 Sales 테이블이 한번에 두개이상 지워지면 오류를 발생시키도록 했습니다.
o 오류 메세지를 발생시키고 ROLLBACK TRANSACTION을 이용하여 DELETE 트랜잭션을 취소 시킵니다.

위 예처럼 트리거 안에서 ROLLBACK TRANSACTION을 수행하면 트리거를 발생시킨 이전의 INSERT, DELETE, UPDATE 자체를 완전히 취소시키게 됨을 기억하시기 바랍니다.

위 트리거가 설정된 상태에서 한번에 여러개의 Row를 지우려고 하면 다음과 같이 오류가 발생합니다.

서버: 메시지 50000, 수준 16, 상태 1, 프로시저 trg_Sales_Delete, 줄 6 You cannot delete more than one row at a time

4. 트리거의 예

몇가지 트리거의 예를 들어 보도록 하겠습니다. 기초 강좌이니만큼 복잡한 내용은 다루지 않고 단순히 어떤 원리로 작동하는지 이해하기 위한 단순한 예입니다. Books Online을 보시면 여러가지 예가 나와 있습니다. 이를 참고하시기 바랍니다.

우선 다음과 같이 두개의 테이블을 만들었습니다.

CREATE TABLE Table1 ( col1 char(05), col2 int ) GO CREATE TABLE Table2 ( col1 char(05), col2 int ) GO

(예제1)

INSERT 트리거의 예입니다.

 

CREATE TRIGGER trg_tbl1_Insert ON Table1 -- 1) FOR INSERT AS INSERT INTO Table2(col1, col2) SELECT col1, col2 FROM Inserted GO INSERT INTO Table1 VALUES('AAAAA', 10) -- 2) INSERT INTO Table1 VALUES('BBBBB', 20) -- 3) GO SELECT * FROM Table2 -- 4)

o Table1에 INSERT된 데이터가 그대로 Table2에 INSERT 되도록하는 트리거입니다.
o 트리거를 만들고 2)와 3)에 의해서 두개의 Row를 Table1에 추가했습니다.
o 4)에서 Table2의 내용을 SELECT해보면 Table1과 같이 두개의 Row를 보게 됩니다.

(예제2)

DELETE 트리거의 예입니다. 현재 Table1에는 col1이 'AAAAA'인 것과 'BBBBB' 인것 두개의 Row가 있습니다.

 

CREATE TRIGGER trg_tbl1_Delete ON Table1 FOR DELETE AS DECLARE @col1 char(05) SELECT @col1 = col1 FROM Deleted DELETE FROM Table2 WHERE col1 = @col1 GO DELETE FROM Table1 WHERE col1 = 'AAAAA' -- 1) 또는 DELETE FROM Table1 -- 2) 또는 DELETE FROM Table1 WHERE col1 = 'AAAAA' -- 3) DELETE FROM Table1 WHERE col1 = 'BBBBB' -- 4) GO SELECT * FROM Table2 -- 5)

o Table1에서 데이터가 DELETE되면 Table2에서도 지워지게 하는 트리거입니다.
o 1) 번이 수행되면 Table2에서도 col1이 'AAAAA" 인 Row가 지워집니다.
o 2) 번이 수행되면 Table1은 다 지워지지만 Table2는 하나의 Row만 지워집니다.
o 3) 번과 4) 번이 같이 수행되어 Table1이 다 지워지지면 Table2도 다 지워집니다.

(퀴즈)

위 세 경우의 차이점을 설명해 보시기 바랍니다.

(예제3)

UPDATE 트리거의 예입니다.

 

CREATE TRIGGER trg_tbl1_Update ON Table1 FOR UPDATE AS DECLARE @col1 char(5) DECLARE @col2 int SELECT @col1 = col1, @col2 = col2 FROM Inserted UPDATE Table2 SET col2 = @col2 WHERE col1 = @col1 GO UPDATE Table1 SET col2 = 30 WHERE col1 = 'AAAAA' -- 1) GO SELECT * FROM Table2 -- 2)

o Table1에 UPDATE된 내용이 그대로 Table2에 UPDATE 되도록하는 트리거입니다.
o 1)에 의해서 col1 이 'AAAAA'인 Row의 col2 가 30으로 바뀌었습니다.
o 2)에서 Table2의 내용을 SELECT해보면 Table1과 같이 바뀐 내용이 보입니다.
o 굳이 @col1, @col2와 같은 변수를 이용하지 않고 JOIN을 이용하여 구현할 수도 있습니다.

(예제4)

UPDATE 트리거에서 특정 컬럼의 변경 여부를 확인하여 처리하는 예입니다.(MOC 교재에 있는 예입니다.)

 

USE Northwind GO CREATE TRIGGER Employee_Update ON Employees FOR UPDATE AS IF UPDATE(EmployeeID) -- 1) BEGIN RAISERROR('Employee ID number cannot be modified.',10,1) -- 2) ROLLBACK TRAN -- 3) END

o 1)에서 처럼 EmployeeID 컬럼이 UPDATE된 경우에는
o 2)에서 처럼 오류 메세지를 보여주고
o 3)에 의해서 처리가 ROLLBACK 됩니다.

이처럼 트리거는 데이터 무결성을 유지하기 위한 방법으로도 사용 할 수 있습니다.

5. INSTEAD OF TRIGGER

지금까지 다루어본 트리거는 INSERT, DELETE, UPDATE가 처리된 후 수행되는 특징이 있습니다.

하지만INSTEAD OF Trigger를 설정하면 트리거만 처리될 뿐 이를 발생시킨 INSERT, DELETE, UPDATE 문은 수행되지 않습니다. FOR INSERT, FOR DELETE, FOR UPDATE대신 FOR INSTEAD OF INSERT, FOR INSTEAD OF DELETE, FOR INSTEAD OF UPDATE로 지정하면 됩니다.

원래 INSTEAD OF 위치에 AFTER를 지정해야 했으나 AFTER는 생략할 수 있으므로 지금까지 생략하고 사용한 것입니다.

출처 : [기타] 인터넷 : http://www.sqlworld.pe.kr/