트리거 (TRIGGER)
- 트리거는 데이터베이스 시스템에서 데이터의 입력, 갱신, 삭제 등의 이벤트가 발생하 때마다 자동적으로 수행되는 사용자 정의 프로시저이다.
- 트리거는 TABLE과는 별도로 DATABASE에 저장된다.
- 트리거는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의될 수 있다.
- 트리거는 SQL의 제약조건 방법을 통해 명시할 수 없는 무결성 제약조건을 구현하고, 관련 테이블의 데이터를 일치시킬 때 주로 사용된다.
- 제약조건과 함께 데이터 무결성을 지키는 하나의 방법으로써 특정 이벤트에 대해서 연속적으로 자동 동작하는 특수한 형태의 저장 프로시저라고 볼 수 있다.
- 일반적으로 사용처는 많지만 예를 들어보자면 "입고"테이블에 새로운 제품이 들어왔을 때 그 수량을 "재고"테이블에 자동으로 반영되게 하는 경우가 있다.
트리거의 종류
TRIGGER의 종류로는 문장 트리거와 행 트리거로 나눌 수 있다.
문장 트리거
트리거가 설정된 테이블에 트리거 이벤트가 발생하면 많은 행에 대해 변경 작업이 발생하더라도 오직 한 번만 트리거를 발생시키는 방법이다. (컬럼값이 변화가 생길 때마다 스스로 알아서 실행된다. - FOR EACH ROW 옵션은 사용하지 않는다.)
UPDATE oracleStudy SET grade = grade + 10;이런 문장이 실행됨으로 여러 행이 변경될 경우 트리거는 단 한 번만 실행된다.
행 트리거
조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법으로 [FOR EACH ROW WHEN 조건] 절로 정의된다.
컬럼의 데이터 행이 변화가 오면 실행되며, 변경 후의 행은 OLD와 NEW를 통해 가져올 수 있다.
▷ 구문
CREATE [ OR REPLACE ] TRIGGER 트리거명
BEFORE | AFTER
[ 동작(INSERT, UPDATE, DELETE) ] ON 테이블명
[ REFERENCING NEW | OLD TABLE AS 테이블명 ]
[ FOR EACH ROW ]
[ WHEN 조건식 ]
트리거 BODY문
* OR REPLACE: 여기저기서 많이들 쓰이는데, 설명하자면 생성할 트리거와 같은 이름을 가지고 있어도 무시하고 새로운 것으로 갱신하는 것이므로 사용할 때 주의해야 한다.
* AFTER: 테이블이 변경된 후에 트리거가 실행되는 옵션이다.
* BEFORE: 테이블이 변경되기 전에 트리거가 실행되는 옵션이다.
* 동작옵션: 각각 INSERT, UPDATE, DELETE가 실행될 때 트리거를 실행시키는 것이다.
* NEW: 새로 추가되거나 변경된 후의 값에 트리거가 적용된다. (INSERT : 입력할 값, UPDATE: 수정할 값)
* OLD: 변경 전의 값에 트리거가 적용된다. (UPDATE: 수정 전 값, DELETE: 삭제할 값)
* WHEN: 트리거가 실행되면서 지켜야 할 조건을 지정한다. (조건에 맞는 데이터만 트리거 실행)
* 트리거 BODY문 : 트리거의 본문 코드를 입력하는 부분이다.
- BEGIN으로 시작해서 END로 끝나는데, 적어도 하나 이상의 SQL문이 있어야 한다. 그렇지 않으면 오류가 발생한다.
- 변수에 값을 치환할 때는 예약어 SET을 사용한다.
구문에서 설명한 모든 옵션을 적용한 예제를 한번 살펴보자.
CREATE OR REPLACE TRIGGER oracle_trigger
BEFORE
INSERT ON oracleStudy
REFERENCING NEW TABLE AS new_trigger
FOR EACH ROW
WHEN new_trigger.점수 = ''
BEGIN
SET new_table.점수 = '0';
END;* 값이 입력되기 전에 공백, null로 입력되는 점수에 대해서 0으로 바꿔주는 트리거를 적용시키는 쿼리문이다.
▶ 표 1
| 조작 | 타이밍 모드 | NEW 변수 사용 가능 여부 |
NEW 변수 수정 가능 여부 |
OLD 변수 사용 가능 여부 |
OLD 변수 수정 가능 여부 |
| INSERT | BEFORE | Y | Y | N | N |
| INSERT | AFTER | Y | N | N | N |
| UPDATE | BEFORE | Y | Y | Y | N |
| UPDATE | AFTER | Y | N | N | N |
| DELETE | BEFORE | N | N | Y | N |
| DELETE | AFTER | N | N | Y | N |
| REINSERT | BEFORE | Y | N | Y | Y |
| REINSERT | AFTER | Y | N | N | N |
*출처 : IBM Knowledge Center
트리거의 제한
- 트리거는 트랜잭션 제어문 (COMMIT, ROLLBACK, SAVEPOINT)을 사용할 수 없다.
- 트리거는 트리거링 문장의 실행 부분으로써 실행되는 트리거링 문장과 같은 트랜잭션에 있다.
- 트리거가 걸려있는 대상(트리거링) 문장이 COMMIT, ROLLBACK 될 때 트리거의 작업 역시 COMMIT, ROLLBACK 된다.
트리거 관리
활성화 / 비활성화
ALTER TRIGGER 트리거_이름 [ DISABLE | ENABLE ]
테이블에 속한 트리거 활성화 / 비활성화
ALTER TABLE 테이블_이름 [DASABLE | ENABLE] ALL TRIGGER
트리거 수정 후 재 컴파일
ALTER TRIGGER 트리거_이름 COMPILE;
트리거 삭제
DROP TRIGGER 트리거_이름
트리거 조회
SELECT * FROM USER_TRIGGERS
댓글