꿈꾸는 개발자, DBA 커뮤니티 구루비

트리거(TRIGGER) 생성

1. CREATE TRIGGER (구문)

트리거는 CREATE TRIGGER 구문에 트리거 대상, 실행 조건과 수행할 내용을 정의하여 생성한다.

1) 구문

CREATE TRIGGER trigger_name
[ STATUS { ACTIVE | INACTIVE} ]
[ PRIORITYkey]
event_time event_type[ event_target]
[ IFcondition]
EXECUTE [ AFTER| DEFERRED] action[ ; ]

event_time:
   • BEFORE
   • AFTER
   • DEFERRED

event_type:
   • INSERT
   • STATEMENT INSERT
   • UPDATE
   • STATEMENT UPDATE
• DELETE
   • STATEMENT DELETE
   • ROLLBACK
   • COMMIT

event_target:
   • ONtable_name
   • ONtable_name CLASS attribute_name

condition:
   • expression

action:
• REJECT
• INVALIDATE TRANSACTION
  •  PRINT message_string
  •  INSERT statement
  •  UPDATE statement
  •  DELETE statement

trigger_name : 정의하려는 트리거의 이름을 지정한다.

[ STATUS { ACTIVE | INACTIVE } ] : 트리거의 상태를 정의한다(정의하지 않을 경우 디폴트 값은 ACTIVE).

ACTIVE 상태인 경우 관련 이벤트가 발생할 때마다 트리거를 실행한다.

INACTIVE 상태인 경우 관련 이벤트가 발생하여도 트리거를 실행하지 않는다. 트리거의 활성 여부는 변경할 수 있다.

[ PRIORITY key ] : 하나의 이벤트에 대해서 다수의 트리거가 불려질 경우 실행되는 우선순위를 부여한다. key 값은 반드시 음수가 아닌 부동 소수점 값이어야 한다. 우선순위를 정의하지 않을 경우 가장 낮은 우선순위인 0을 할당한다. 같은 우선순위를 가지는 트리거는 임의의 순서로 실행된다. 트리거의 우선순위는 변경할 수 있다.

event_time : 트리거의 조건 영역과 실행 영역이 실행되는 시점을 지정하며 BEFORE, AFTER, DEFERRED가 있다.

event_type : 트리거 타입은 사용자 트리거와 테이블 트리거로 나뉜다.

event_target : 이벤트 대상은 트리거가 호출되기 위한 대상을 지정할 때 쓰인다.

condition : 트리거의 조건영역을 지정한다.

action : 트리거의 실행영역을 지정한다.

2) 예제

다음은 participant 테이블에 인스턴스를 갱신할 때 획득 메달의 개수가 0보다 작을 경우 갱신을 거절하는 트리거를 생성하는 예제이다.

CREATE TRIGGER medal_trigger
BEFORE UPDATE ON participant
IF new.gold < 0 OR new.silver < 0 OR new.bronze < 0
EXECUTE REJECT;

csql> UPDATE participant SET gold = -5 WHERE nation_code = 'KOR' AND host_year = 2004;
csql> ;x
In line 1, column 1,
ERROR: The operation has been rejected by trigger "medal_trigger".

2. 이벤트 시점

트리거의 조건 영역과 실행 영역이 실행되는 시점을 지정한다. 이벤트 시점의 종류에는 BEFORE, AFTER, DEFERRED가 있다.

BEFORE : 이벤트가 처리되기 이전에 조건을 검사한다.

AFTER : 이벤트가 처리된 후에 조건을 검사한다.

DEFERRED : 이벤트에 대한 트랜잭션의 끝에서 조건을 검사한다. DEFERRED로 지정할 경우 이벤트 타입에 COMMIT이나 ROLLBACK을 사용할 수 없다.

3. 트리거 타입

1) 사용자 트리거(User Trigger)

데이터베이스의 특정 사용자와 관련된 트리거를 사용자 트리거(user trigger)라고 한다.

사용자 트리거는 이벤트 대상이 없으며 트리거의 소유자(트리거를 생성한 사용자)에 의해서만 실행된다.

사용자 트리거를 정의하는 이벤트 타입은 COMMIT과 ROLLBACK이 있다.

2) 테이블 트리거(Table Trigger)

특정 테이블을 이벤트 대상으로 가지는 트리거를 테이블 트리거(클래스 트리거)라 한다..

테이블 트리거는 대상 테이블에 SELECT 권한을 가지는 모든 사용자가 볼 수 있다.

테이블 트리거를 정의하는 이벤트 타입은 인스턴스 이벤트와 문장 이벤트가 있다.

4. 트리거 이벤트 타입

인스턴스 이벤트(instance event) : 인스턴스 이벤트는 이벤트 연산의 단위가 인스턴스인 이벤트 타입을 말한다. 인스턴스 이벤트의 종류는 다음과 같다.

INSERT

UPDATE

DELETE

문장 이벤트(statement event) : 이벤트 타입을 문장 이벤트로 정의하면 주어진 문장(이벤트)에 의해 영향을 받는 객체(인스턴스)가 많더라도, 트리거는 문장이 시작할 때 한 번만 불려지게 된다. 문장 이벤트의 종류는 다음과 같다.

STATEMENT INSERT

STATEMENT UPDATE

STATEMENT DELETE

기타 이벤트 : COMMIT과 ROLLBACK은 개별적인 인스턴스에는 적용할 수 없다.

COMMIT

ROLLBACK

1) 예제

다음은 인스턴스 이벤트를 사용하는 예제이다. example 트리거는 데이터베이스 갱신에 의해 영향을 받는 각각의 인스턴스에 대해서 한번씩 불려진다. 예를 들어, history 테이블의 다섯 개 인스턴스의 score를 변경했다면, 이 트리거는 다섯 번 불려진다. 만약 score 컬럼의 첫 번째 인스턴스가 갱신되기 전에 트리거가 한 번만 불려지게 하려면, 예제 2와 같이 STATEMENT UPDATE 형식을 사용한다.

CREATE TRIGGER example
...
BEFORE UPDATE ON history(score)
...

다음은 문장 이벤트를 사용하는 예제이다. 문장 이벤트를 지정하면 갱신의 영향을 받는 인스턴스가 많더라도, 첫 번째 인스턴스가 갱신되기 전에 트리거가 한 번만 불려지게 된다.

CREATE TRIGGER example
...
BEFORE STATEMENT UPDATE ON history(score)
...

2) 주의 사항

이벤트 타입으로 인스턴스 이벤트와 문장 이벤트를 지정할 경우에는 반드시 이벤트 대상을 명시해야 한다.

COMMIT과 ROLLBACK은 이벤트 대상을 가질 수 없다

5. 트리거 이벤트 대상

이벤트 대상은 트리거가 호출되기 위한 대상을 지정할 때 쓰인다. 트리거 이벤트의 대상은 테이블명 혹은 테이블명과 컬럼명으로 지정할 수 있으며 컬럼명을 지정하면 해당 컬럼이 이벤트의 영향을 받을 때에만 트리거가 불려진다. 만약 컬럼을 지정하지 않으면 지정된 테이블 내에 어떤 컬럼이 영향을 받더라도 트리거가 호출된다. 오직 UPDATE, STATEMENT UPDATE 이벤트만이 이벤트 대상에 컬럼을 지정할 수 있다.

1) 예제

다음은 example 트리거의 이벤트 대상을 history 테이블의 score 컬럼으로 지정한 예제이다.

CREATE TRIGGER example
...
BEFORE UPDATE ON history(score)
...

6. 이벤트 타입과 대상 조합

트리거를 호출하는 데이터베이스 이벤트는 트리거 이벤트 타입과 트리거 정의 내의 이벤트 대상에 의해 식별된다. 다음은 트리거 이벤트 타입과 대상 조합, 트리거 이벤트가 나타내는 CUBRID 데이터베이스 이벤트의 활동을 표로 정리한 것이다.

이벤트 타입	이벤트 대상	대응되는 데이터베이스 활동
UPDATE	테이블	테이블의 어떤 속성에 대해서도 갱신되면 트리거가 호출된다.
STATEMENT UPDATE	테이블	테이블에 대해 UPDATE 문이 실행되었을 때 트리거가 호출된다.
INSERT	테이블	테이블의 인스턴스가 생성되었을 때 트리거가 호출된다.
STATEMENT INSERT	테이블	테이블에 INSERT 문이 실행되었을 때 트리거가 호출된다.
DELETE	테이블	테이블의 인스턴스가 삭제되었을 때 트리거가 호출된다.
STATEMENT DELETE	테이블	테이블에 DELETE 문이 실행되었을 때 트리거가 호출된다.
COMMIT	테이블	데이터베이스 트랜잭션이 커밋되었을 때 트리거가 호출된다. COMMIT WORK 문이 트리거를 시작시킨다.
ROLLBACK	테이블	데이터베이스의 트랜잭션이 롤백되었을 때 트리거가 호출된다. ROLLBACK WORK 문이 트리거를 시작시킨다.

7. 트리거 조건 영역

트리거를 정의할 때 조건 영역을 정의하여 트리거의 수행 영역에 대한 수행 여부를 결정한다.

트리거 조건 영역이 기술된다면, 참 또는 거짓을 평가할 수 있는 단독적인 복합 표현식으로 쓰여질 수 있다. 이 경우에 표현식은 SELECT 문의 WHERE 절에 허용되는 산술 연산자와 논리 연산자를 포함할 수 있다. 조건 영역이 참이면, 트리거 실행 영역이 수행되고, 거짓이면 실행되지 않는다.

트리거의 조건 영역을 생략하면 조건 없는 트리거(unconditional trigger)가 되며 이는 트리거가 불려질 때 항상 트리거의 실행 영역이 수행된다.

1) 예제

다음은 조건 영역 내의 표현식에 상관명을 이용한 예제이다. 이벤트 타입이 INSERT, UPDATE, DELETE인 경우에, 조건 영역 내의 표현식은 특정 컬럼 값에 접근하기 위하여 상관명 obj, new, old를 사용할 수 있다. 예제에서 example 트리거는 record 컬럼의 현재 값을 이용해서 조건 영역을 검사하기 위해 트리거 조건 영역에 obj를 컬럼 이름 앞에 사용하였다.

CREATE TRIGGER example
...
IF obj.record * 1.20 < 500
...

다음은 조건 영역 내의 표현식에 SELECT 문을 사용한 예제이다. 예제의 트리거는 집계함수 COUNT( * )를 사용하는 SELECT 문을 사용하여 그 값과 상수를 비교한다. SELECT 문은 반드시 괄호로 싸여 있어야 하고, 표현식의 마지막에 위치해야 한다.

CREATE TRIGGER example
...
IF 1000 > (SELECT COUNT( * ) FROM participant)
...

2) 주의사항

트리거 조건 영역에 주어진 표현식은 조건 영역이 수행되는 동안에 메소드가 호출되면 데이터베이스에 부작용을 초래할 수 있다. 트리거 조건 영역은 데이터베이스에 생각지 못한 부작용이 발생하지 않도록 구성해야 한다.

8. 상관명(correlation name)

트리거를 정의할 때 상관명을 사용하여 대상 테이블의 컬럼 값에 접근할 수 있다. 상관명은 실제적으로 트리거를 부르는 데이터베이스 연산에 의해 영향을 받는 인스턴스를 나타낸다. 상관명은 트리거의 조건 영역이나 실행 영역에도 기술할 수 있다.

상관명의 종류에는 new, old, obj가 있으며 이러한 상관명은 인스턴스 트리거에서 INSERT, UPDATE, DELETE의 이벤트 타입을 가지고 있는 트리거에서만 사용할 수 있다.

상관명의 사용은 아래 표와 같이 트리거 조건 영역에 정의된 이벤트 시점에 의해 더욱 제한된다.

	BEFORE	AFTER or DERERRED
INSERT	new	obj
UPDATE	obj new	obj old(AFTER)
DELETE	obj	NA

상관명	대표 속성 값
obj	인스턴스의 현재 속성 값을 나타낸다. 인스턴스가 갱신되거나 삭제되기 전에 속성값에 접근하기 위해서 사용한다. 그리고 인스턴스가 갱신되거나 삽입된 후에 속성 값에 접근하기 위해 사용한다.
new	삽입이나 갱신 연산에 의해 제시되는 속성값을 나타낸다. 새로운 값은 인스턴스가 실제적으로 삽입되거나 갱신되기 전에만 접근할 수 있다.
old	갱신 연산의 완료 전에 존재하던 속성값을 나타낸다. 이 값은 트리거가 수행되는 동안만 유지된다. 트리거가 종료되면 old 값은 잃어버리게 된다.

9. 트리거 실행 영역

트리거 실행 영역은 트리거의 조건 영역이 참이거나 조건 영역이 생략된 경우 수행될 내용을 기술하는 영역이다.

실행 영역 절에 특정 시점(AFTER나 DEFERRED)이 주어지지 않으면, 실행 영역은 트리거 이벤트와 같은 시점에서 수행된다.

아래 목록은 트리거를 정의할 때 사용할 수 있는 실행 영역의 목록이다.

REJECT : REJECT는 트리거에서 조건 영역이 참이 아닌 경우 트리거를 발동시킨 연산은 거절되고 데이터베이스의 이전 상태를 그대로 유지한다. 연산이 수행된 후에는 거절할 수 없기 때문에 REJECT는 실행 시점이 BEFORE일 때만 허용된다. 따라서 실행 시점이 AFTER나 DERERRED인 경우 REJECT를 사용해서는 안 된다.

INVALIDATE TRANSACTION : INVALIDATE TRANSACTION은 트리거를 부른 이벤트 연산은 수행되지만, 커밋을 포함하고 있는 트랜잭션은 수행되지 않도록 한다. 트랜잭션이 유효하지 않으면 반드시 ROLLBACK 문으로 취소시켜야 한다. 이러한 실행은 데이터를 변경하는 이벤트가 발생한 후에 유효하지 않은 데이터를 가지는 것으로부터 데이터베이스를 보호하기 위해 사용된다.

PRINT : PRINT는 터미널 화면에 텍스트 메시지로 트리거 활동을 가시적으로 보여주기 때문에 트리거의 개발이나 시험하는 도중에 사용될 수 있다. 이벤트 연산의 결과를 거절하거나 무효화시키지는 않는다.

INSERT : INSERT는 테이블에 하나 혹은 그 이상의 새로운 인스턴스를 추가한다.

UPDATE : UPDATE는 테이블에 있는 하나 혹은 그 이상의 컬럼 값을 변경한다.

DELETE : DELETE는 테이블로부터 하나 혹은 그 이상의 인스턴스를 제거한다.

1) 예제

다음은 트리거 생성 시에 실행영역의 정의 방법을 보여주는 예제이다. medal_trig 트리거는 실행 영역에 REJECT를 지정하였다. REJECT는 실행 시점이 BEFORE일 때만 지정 가능하다.

CREATE TRIGGER medal_trig
BEFORE UPDATE ON participant
IF new.gold < 0 OR new.silver < 0 OR new.bronze < 0
EXECUTE REJECT;

2) 주의사항

INSERT 이벤트가 정의된 트리거의 실행 영역에 INSERT를 사용할 때는 트리거가 무한 루프에 빠질 수 있으므로 주의해야 한다.

UPDATE 이벤트가 정의된 트리거가 분할된 테이블에서 동작하는 경우, 정의된 분할이 깨지거나 의도하지 않은 오동작이 발생할 수 있으므로 주의해야 한다. 이를 방지하기 위해 CUBRID는 트리거가 동작중인 경우 분할 변경을 야기하는 UPDATE가 실행되지 않도록 오류 처리한다. UPDATE 이벤트가 정의된 트리거의 실행 영역에 UPDATE를 사용할 때는 무한 루프에 빠질 수 있으므로 주의해야 한다.