트리거(trigger)
데이터베이스의 CONSTRAINT만으로 표현할 수 없는 제약 조건들이 있다. 예를 들어, “올림픽 경기장의 좌석 수가 10만개가 넘을 수 없다”라는 제약 조건이 필요하다면 이러한 제약조건은 CONSTRAINT만으로 표현할 수 없다. 이 제약조건을 만족시키기 위해서는 데이터에 대한 갱신이 일어날 때마다 제약조건을 만족하는지 여부를 검사하여 만족하지 않으면 거절하는 방식을 이용할 수 있는데, 이럴 때 사용하는 것이 트리거이다.
트리거는 명시된 이벤트가 발생할 때마다 DBMS가 자동적으로 수행하는, 사용자가 정의하는 문이다. 트리거는 데이터베이스 무결성을 유지하기 위한 일반적이고 강력한 도구이다. 트리거는 테이블를 정의할 때 표현할 수 없는 비즈니스 규칙들에 대한 무결성 제약조건을 보장하는 역할을 한다. 무결성 제약 조건을 유지하기 위하여 데이터베이스 갱신을 모니터링하고, 데이터베이스 갱신을 전파한다. [그림 1]은 트리거의 개념을 보여준다. 한 테이블에 대해서 삽입, 삭제, 수정이 발생하여 트리거가 활성화되면 트리거의 코드가 데이터베이스에 대해 실행된다.
 |
| [그림 1] |
트리거를 명시하려면 트리거를 활성화시키는 사건인 이벤트, 트리거가 활성화되었을 때 수행되는 테스트인 조건, 트리거가 활성화되고 조건이 참일 때 수행되는 문(프로시저)인 동작을 표현해야 한다. 트리거를 이벤트-조건-동작(ECA) 규칙이라고도 부른다. 여기서 E는 Event, C는 Condition, A는 Action을 의미한다.
트리거의 형식은 다음과 같다.
CREATE TRIGGER 트리거 이름
[STATUS { ACTIVE | INACTIVE }]
[PRIORITY 음이 아닌 정수]
[BEFORE | AFTER | DEFERRED] 이벤트 타입 [ON 테이블 이름 (컬럼 이름)]
[IF 조건]
EXECUTE [AFTER | DEFERRED] 동작;
|
STATUS절은 트리거의 활성 여부를 나타낸다. STATUS가 활성(ACTIVE) 상태인 경우에는 관련 이벤트가 발생할 때마다 트리거를 실행하고, 비활성(INACTIVE) 상태인 경우에는 관련 이벤트가 발생하여도 트리거를 실행하지 않는다. 트리거의 활성 여부는 ALTER TRIGGER문을 이용하여 변경할 수 있다. PRIORITY절은 데이터베이스에서 정의된 트리거들의 특정 순서를 부여할 때 사용하는 우선 순위를 나타낸다. PRIORITY는 0이 가장 낮은 값으로서 가장 나중에 수행된다. 우선 순위는 ALTER TRIGGER문을 이용하여 변경할 수 있다.
트리거의 조건 및 동작 수행 시점을 지정하기 위하여 BEFORE, AFTER, DEFERRED절을 사용한다. BEFORE는 트리거에서 정의한 이벤트를 처리하기 전에 조건을 검사하고 AFTER는 이벤트를 처리한 후에 조건을 검사하며, DEFFERED는 이벤트에 대한 트랜잭션의 끝에서 조건을 검사한다. 이벤트 타입은 크게 인스턴스 이벤트(instance event)와 문장 이벤트(statement event)로 구분할 수 있다. 인스턴스 이벤트를 정의하는 트리거를 인스턴스 트리거(instance trigger)라 하며, 발생된 이벤트의 영향을 받는 각 레코드마다 조건이 참이면 동작을 실행하는 트리거이다. 문장 이벤트를 정의하는 트리거를 문장 트리거(statement trigger)라 하며, 이벤트가 데이터베이스의 많은 레코드에 영향을 주더라도 조건문의 검사 및 동작의 실행이 단 한 번만 발생하는 트리거이다.
트리거의 정의에서 이벤트에 의해 영향을 받는 레코드를 참조하는 키워드를 상관 명(correlation name)이라 한다. 예를 들어, 올림픽 경기장의 좌석 수가 10만개가 넘지 않도록 보장하기 위한 트리거를 작성하려고 한다고 가정하자. 이 트리거는 좌석 제한 수를 수정하기 이전에, 좌석 제한 수를 미리 알아야 한다, 즉, 좌석 제한 수를 수정하려고 하는 것은 이벤트가 되고, 좌석 제한 수가 수정되었다고 가정할 때, 좌석 제한 수 10만개가 넘는지를 검사하는 것이 조건이 된다. 따라서 이 트리거를 정의하기 위해서는 이벤트가 처리되기 이전의 좌석 수와 이후의 좌석 수를 모두 참조할 수 있어야 한다.
이벤트가 처리되기 이전(BEFORE)에 조건이 검사될 경우 처리 전의 레코드는 키워드 OBJ로 참조하고, 처리 후의 레코드는 키워드 NEW로 참조할 수 있다. 각 이벤트 시점별 레코드의 참조 상관명은 아래 표에 나타낸다.
|
BEFORE
|
AFTER/DEFERRED
|
INSERT
|
NEW
|
OBJ
|
UPDATE
|
OBJ
NEW
|
OBJ
OLD(AFTER)
|
DELETE
|
OBJ
|
| |
|
트리거의 정의 및 관리
| 예제 1 |
문제
2004년도 올림픽에 한국이 획득한 금메달의 개수를 음수로 갱신할 경우 갱신이 거절되는 트리거(Trigger)를 작성하라.
SQL문
CREATE TRIGGER medal_trigger
BEFORE UPDATE ON participant
IF new.gold < 0 OR new.silver < 0 OR new.bronze < 0
EXECUTE REJECT;
|
SQL문
UPDATE participant
SET gold = -1
WHERE nation_code='KOR';
|
결과 화면
설명
위의 UPDATE문을 실행시키면 “The operation has been rejected by trigger ‘medal_trigger’”라는 에러 메시지가 나타난다.
| |
| 예제 2 |
문제
“올림픽 경기장의 좌석 수가 10만개를 넘을 수 없다”에 해당하는 제약 조건을 레코드 삽입 이전에 검사하는 트리거를 정의하라. 그리고 ‘Sung-Nam Olympic Stadium’를 좌석수 15만으로 Stadium 테이블에 삽입한 결과를 보여라.
SQL문
CREATE TRIGGER CHECK_SEAT_BEFORE_INSERT
BEFORE INSERT ON stadium
IF obj.seats > 100000
EXECUTE REJECT;
|
SQL문
INSERT INTO stadium
VALUES
(30142, 'KOR', 'Sung-Nam Olympic Stadium', 50000.00, 150000, 'Gyoung-gi, Korea');
|
결과 화면
설명
위의 INSERT문을 실행시키면 “The operation has been rejected by trigger ‘check_seat_before_insert’”라는 에러 메시지가 나타난다.
| |
| 예제 3 |
문제
올림픽 개최 정보 입력시 nation 테이블에 해당 국가가 존재하는지 확인하여 존재하는 경우에만 입력되게 하라.
SQL문
CREATE TRIGGER tr_i
BEFORE INSERT ON olympic
IF(SELECT COUNT(*) FROM nation WHERE name=new.host_nation) = 0
EXECUTE REJECT;
|
SQL문
INSERT INTO olympic
VALUES
(2008, 'China', 'Beijing' , '2008-08-08', '2008-08-24', 'Fuwa', 'Chinese Seal, Dancing Beijing', '소개');
|
결과 화면
설명
위의 INSERT문을 실행시키면 “The operation has been rejected by trigger ‘tr_i’”라는 에러 메시지가 나타난다.
| |
| 예제 4 |
문제
한 나라의 메달 정보가 UPDATE될 때마다 participant_all 테이블에 해당 나라 코드와 금,은,동메달의 총 합계 수를 Insert하는 트리거 add_total_medal을 작성하라.
SQL문
|
CREATE TABLE participant_all (nation_code char(3), cnt int);<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><?xml:namespace prefix = o />
CREATE TRIGGER add_total_medal
AFTER UPDATE ON participant
EXECUTE
INSERT INTO participant_all
VALUES(obj.nation_code, obj.gold + obj. silver + obj.bronze);
|
SQL문
UPDATE participant SET gold = 15 WHERE nation_code='KOR';
|
SQL문
SELECT *
FROM participant_all;
|
결과
no
|
nation_code
|
count
|
1
|
KOR
|
36
| |
|
| |
| 예제 5 |
문제
재귀적 트리거 호출이 발생할 경우 최대 5번까지만 호출되도록 지정하라.
SQL문
SET TRIGGER MAXIMUM DEPTH 4;
|
설명
재귀적 트리거 호출이 무한히 반복되는 것을 방지하기 위하여 호출 횟수의 최대값을 지정할 수 있다. 만약 새로운 트리거를 작성하고 아직 검증하지 않았을 때 재귀적 호출을 무한히 하게 되면 현재 연결 중인 데이터베이스 세션을 강제로 끊어야만 한다. 이러한 상황을 방지하기 위하여 트리거 작성시 아직 검증되지 않았을 경우에는 최대 호출 수를 지정하는 것이 바람직하다.
| |
| 예제 6 |
문제
medal_trigger 트리거의 우선순위를 1로 조정하라.
SQL문
ALTER TRIGGER medal_trigger
PRIORITY 1.0;
|
설명
트리거의 우선순위에 따라 순서대로 적용되며, PRIORITY 옵션과 같이 기술하는 숫자는 반드시 음이 아닌 부동소수점 값이어야 한다..
| |
| 예제 7 |
문제
medal_trigger 트리거를 비활성화하라..
SQL문
ALTER TRIGGER medal_trigger
STATUS INACTIVE;
|
설명
트리거의 상태는 STATUS절에 ACTIVE/INACTIVE 키워드를 이용하여 활성화 및 비활성화 할 수 있다.
| |
| 예제 8 |
문제
medal_trigger 트리거를 제거하라.
SQL문
DROP TRIGGER medal_trigger;
|
설명
트리거의 제거는 테이블을 제거하는 것과 유사하다.
| |
트리거를 이용한 참조 무결성 제약 조건의 구현
트리거를 이용하여 앞서 설명한 참조 무결성 제약 조건을 구현할 수 있다. 삽입, 삭제, 수정이 발생할 때(Event) 무결성을 위배하는지 검사(Check)하여 적절한 동작(Action)을 수행하는 트리거를 작성하면 된다
| 예제 9 |
문제
NATION 테이블에 없는 국가 코드를 가진 STADIUM(경기장)을 추가할 경우 참조 무결성 제약 조건에 위배된다. 이를 트리거로 구현하라.#
SQL문
CREATE TRIGGER stadium_insert
BEFORE INSERT ON stadium
IF (SELECT COUNT(*) FROM nation WHERE nation.code = new.nation_code) = 0
EXECUTE REJECT;
|
SQL문
INSERT INTO stadium
VALUES (30141, 'AAA', 'Sung-Nam Olympic Center', 20000, 8000, 'Gyoung-gi, Korea');
|
결과 화면
설명
국가코드 ‘AAA’가 nation 테이블에 존재하는지 검사한 후 INSERT문을 실행한다.
| |
| 예제 10 |
문제
NATION 테이블에 없는 국가 코드를 가진 STADIUM(경기장) 테이블의 국가 코드를 수정할때 참조 무결성 제약 조건에 위배된다. 이를 트리거로 구현하라.
SQL문
CREATE TRIGGER stadium_update
BEFORE UPDATE ON stadium(nation_code)
IF (SELECT COUNT(*) FROM nation WHERE nation.code = new.nation_code) = 0
EXECUTE REJECT;
|
SQL문
UPDATE stadium SET nation_code='AAA' WHERE nation_code='KOR';
|
결과 화면
설명
국가코드를 변경할 때, 변경할 국가 코드 ‘AAA’가 nation 테이블에 있는지 검사한 후 UPDATE문을 실행한다.
| |
| 예제 11 |
문제
NATION 테이블의 국가 정보(레코드)를 삭제할 때, 해당 국가 코드(CODE)를 참조하고 있는 STADIUM(경기장)이 있으면 삭제를 거절하는 트리거를 구현하라.
SQL문
CREATE TRIGGER nation_delete
BEFORE DELETE ON nation
IF (SELECT COUNT(*) FROM stadium WHERE nation_code = obj.code) > 0
EXECUTE REJECT;
|
SQL문
DELETE FROM nation WHERE code='KOR';
|
결과 화면
설명
Nation 테이블에서 ‘KOR’ 국가 코드를 가진 레코드를 삭제할 경우, 만약에 stadium 테이블에서 ‘KOR’ 국가 코드를 가진 컬럼이 있다면 삭제하지 못한다.
| |
| 예제 12 |
문제
NATION 테이블의 국가 정보(레코드)를 수정할 때, 해당 국가 코드(CODE)를 참조하고 있는 STADIUM(경기장)의 국가 코드를 연쇄 변경하는 트리거를 구현하라.
SQL문
|
CREATE TRIGGER nation_update_xxx
AFTER UPDATE ON nation(code)
EXECUTE UPDATE stadium
SET nation_code = obj.code
WHERE nation_code = old.code;
|
SQL문
UPDATE nation SET code ='AAA' WHERE code='KOR';
|
설명
이 트리거를 실행시키려면 participant 테이블의 nation_code 칼럼의 외래 키를 삭제해줘야 한다. | |
[출처] 16.트리거 (Trigger) (큐브리드 공부하기) |작성자 밝은미소 |
