18-1.트리거

1.정의

트리거(Trigger)는 특정 사건이 발생했을 때 자동으로 호출되는 코드이다. 사전적으로는 방아쇠라는 뜻인데 방아쇠를 당기면 총알이 날아가듯 어떤 사건에 대해 자동으로 후속 조치를 하는 장치를 의미한다. SQL 명령의 묶음이라는 점에서 프로시저와 비슷하지만 다음과 같은 차이점이 있다.

l 직접 호출할 수 없으며 일정한 조건이 되면 자동으로 호출된다.

l 시스템이 호출하므로 인수를 전달할 수 없고 리턴값도 반환할 수 없다.

테이블이나 데이터베이스에 변화가 발생할 때 실행할 코드를 트리거로 정의한다. 회원이 상품을 구입하면 예치금은 감소하고 재고는 줄어든다. 이벤트 발생시 당연히 처리해야 하는 코드를 트리거로 등록해 두면 후속 처리를 자동으로 수행한다. 주문 테이블에 레코드를 삽입하면 트리거에서 예치금과 재고를 관리하는 식이다.

변화에 대해 반응한다는 면에서 그래픽 프로그램(GUI)의 키보드, 마우스 이벤트 핸들러와 동작 방식이 유사하다. 트리거는 수행 시점과 작업 규칙을 서버에 저장해 놓은 DB 오브젝트이며 정의문에 적용 대상과 발생 시점에 대한 지정을 상세히 적는다.

CREATE [OR REPLACE] TRIGGER 트리거이름

BEFORE | AFTER | INSTEAD OF

INSERT OR UPDATE OR DELETE [OF 컬럼]

ON 테이블명

[FOR EACH ROW]

BEGIN

명령;

END

이름은 자유롭게 적되 다른 오브젝트와 구분하기 위해 관습상 TR_ 등의 접두를 붙인다. BEFORE, AFTER는 호출 시점을 지정한다. 반응할 이벤트는 INSERT, UPDATE, DELETE 등이 있고 복수 개의 이벤트를 처리할 때는 OR로 나열한다.

테이블을 읽기만 하는 SELECT는 딱히 후속 처리할만한 작업이 없어 이벤트로 지정할 필요가 없다. ON 절에는 이벤트가 발생할 테이블(또는 뷰)을 밝히며 BEGIN ~ END 블록에 이벤트 처리 코드를 작성한다.

AFTER INSERT ON tCity -- 도시를 삽입한 후

BEFORE DELETE ON tStaff -- 직원을 삭제하기 전

AFTER UPDATE OR DELETE ON tCity -- 도시 목록을 삭제 또는 변경한 후

요약하자면 트리거는 어떤 대상에 대해 무슨 일이 생겼을 때 어떤 코드를 실행할 것인가를 정의한다. 간단한 예를 들어 보자.

CREATE OR REPLACE TRIGGER TR_Message

AFTER UPDATE ON tCity

BEGIN

DBMS_OUTPUT.PUT_LINE('도시 정보를 갱신하였습니다.');

END;

TR_Message 트리거는 tCity 테이블에 변경이 발생한 후(After Update)에 호출된다. 본체에서는 호출되었음을 확인하기 위해 메시지를 출력한다. 한글과 어순이 달라서 그렇지 영문으로는 아주 자연스러운 문장이다.

예제에서는 호출 사실만 확인해 보았는데 실무에서는 업무 규칙에 적합한 후속 작업을 처리해야 한다. 트리거를 정의해 놓고 서울의 인구를 만명 증가시켜 보자.

UPDATE tCity SET popu = popu + 1 WHERE name = '서울';

UPDATE 명령을 실행할 때마다 미리 등록해 놓은 트리거가 호출되어 잘 갱신했다는 메시지를 출력한다. UPDATE 명령을 실행할 때마다 스크립트 출력창에 다음 메시지가 나타난다.

도시 정보를 갱신하였습니다.

1 행 이(가) 업데이트되었습니다.

메시지가 출력되지 않으면 SET SERVEROUTPUT ON 옵션을 켜 놨는지 확인해 보자. 원하는 시점에 트리거가 호출된다는 것만 확인해 봤는데 일단 제어를 받았으면 여기서 무엇이든 할 수 있다. 변경 사항에 대한 추가 조치를 하거나 변경 이력을 별도의 테이블에 저장하여 이력을 관리할 수 있다.

2.트리거 관리

CREATE OR REPLACE 구문으로 트리거를 생성하면 언제든지 본체를 편집할 수 있다. 앞 항의 트리거는 UPDATE 이벤트에 대해서만 동작하는데 대상 이벤트를 OR로 연결하여 여러 이벤트를 동시에 추적할 수도 있다. 콤마가 아닌 OR임을 주의하자.

본체에서는 INSERTING, UPDATING, DELETING 조건을 점검하여 트리거가 발생한 이벤트를 알아낸다. 두 사건이 동시에 발생할 수는 없으니 셋 중 하나만 참이다. 다음 트리거는 세 이벤트를 동시에 감시하며 본문에서 어떤 사건이 발생했는지 메시지를 출력한다.

CREATE OR REPLACE TRIGGER TR_Message

AFTER INSERT OR UPDATE OR DELETE ON tCity

BEGIN

IF INSERTING THEN

DBMS_OUTPUT.PUT_LINE('새로운 도시를 삽입하였습니다.');

END IF;

IF UPDATING THEN

DBMS_OUTPUT.PUT_LINE('도시 정보를 갱신하였습니다.');

END IF;

IF DELETING THEN

DBMS_OUTPUT.PUT_LINE('도시를 삭제하였습니다.');

END IF;

END;

INSERT와 UPDATE, DELETE가 모두 OR로 연결되어 있어 세 사건 중 하나라도 발생하면 트리거를 호출한다. 트리거 하나로 세 사건을 모두 다룰 수 있다. 트리거가 잘 호출되는지 도시 테이블에 새 레코드를 삽입하고 수정한 후 삭제해 보자. 명령을 수행할 때마다 트리거가 호출되며 어떤 사건이 일어났는지 메시지를 출력한다.

INSERT INTO tCity VALUES ('여주',608,11,'n','경기');

UPDATE tCity SET popu = 12 WHERE name = '여주';

DELETE FROM tCity WHERE NAME = '여주';

트리거는 한 번 설치해 놓으면 테이블에 변화가 발생할 때마다 호출된다는 점에서 부담스러운 존재이며 너무 많은 트리거를 설치하면 서버에 상당한 무리를 준다. 더 이상 필요 없으면 즉시 삭제하는 것이 좋다.

트리거를 삭제할 때는 DROP TRIGGER 명령을 사용한다. 명시적으로 삭제하지 않더라도 트리거가 부착된 테이블을 삭제하면 같이 제거한다. 감시할 테이블이 사라지면 트리거를 남겨둘 이유가 없기 때문이다. 삭제는 하지 않고 잠시 정지 시켜 놓을 때는 다음 두 구문을 사용한다.

ALTER TRIGGER TR_Message ENABLE | DISABLE;

ALTER TABLE tCity ENABLE | DISABLE ALL TRIGGERS;

지정한 트리거 하나만 또는 테이블에 부착된 모든 트리거를 일괄 정지 및 재개할 수 있다. 대량의 데이터를 고속으로 삽입하거나 무결성을 해칠 위험이 없다면 트리거를 잠시 꺼 놓는 것이 속도상 유리하다. 접속창에서 트리거의 팝업 메뉴를 열고 활성화/비활성화 항목을 선택하거나 테이블의 팝업 메뉴에서 트리거/모두 사용, 모두 사용 안함을 선택해도 된다.

3.행 트리거

TR_Message 트리거는 테이블에 대한 트리거이며 테이블에 변화가 있을 때마다 호출된다. 여러 행을 한꺼번에 변경해도 트리거는 딱 한 번만 발생한다. 다음 명령을 실행해 보자.

UPDATE tCity SET popu = popu + 1 WHERE region = '경기';

경기도 소속의 도시는 2개 있으며 2개의 레코드가 바뀌지만 트리거는 한 번만 발생한다. 이처럼 SQL 명령 하나에 대해 한 번 호출되는 트리거를 문장(Statement) 트리거라고 한다. 이에 비해 트리거 정의문에 FOR EACH ROW 옵션을 지정하면 매 레코드를 변경할 때마다 호출되는 행 트리거가 된다.

문장 트리거는 단순히 사건이 발생했음을 알리기만 하는데 비해 행 트리거는 어떤 사건이 어떻게 발생했는지 상세한 정보를 제공한다. 트리거는 인수를 받지 않는 대신 시스템이 자동으로 생성하는 임시 테이블을 통해 변화에 대한 정보를 제공한다.

l NEW : INSERT, UPDATE시 변경된 후의 레코드를 가진다.

l OLD : DELETE, UPDATE시 삭제 또는 변경 전의 레코드를 가진다.

이 둘은 트리거가 끝나면 자동으로 삭제된다. 본체에서 참조할 때 앞에 :을 붙이며 레코드 변수처럼 :NEW.필드 식으로 내부의 필드를 읽고 쓴다. NEW, OLD를 통해 변경 전후의 레코드 상태를 알 수 있다. 다음 트리거는 어떤 도시의 인구가 어떻게 바뀌었는지 메시지로 출력한다.

CREATE OR REPLACE TRIGGER TR_Message

AFTER UPDATE ON tCity

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);

END;

FOR EACH ROW 옵션을 지정했으므로 행 트리거이며 각 레코드가 변경될 때마다 호출된다. 본문에서는 OLD와 NEW를 통해 변경 이전, 이후의 레코드를 모두 참조할 수 있다. 서울 레코드에 대해 두 테이블의 모습은 다음과 같다.

:OLD.popu는 변경 전의 인구수이고 :NEW.popu는 변경 후의 인구수이다. name은 변경하지 않으므로 어느 테이블에서 읽으나 똑같다. 본체에서는 변경되는 각 레코드에 대해 두 테이블을 읽어 인구수가 어떻게 바뀌었는지 출력한다.

서울 :974->975

오산 :21->22

UPDATE 명령은 두 테이블이 모두 존재하지만 INSERT 명령은 새로 삽입한 NEW 테이블만 있으며 DELETE 명령은 삭제한 OLD 테이블만 있다. NEW, OLD 테이블의 이름이 마음에 들지 않으면 FOR EACH ROW 앞에 다음 구문으로 별명을 지정한다.

REFERENCING OLD AS pre_rec NEW AS post_rec

이름을 변경하면 트리거 정의문을 Ctrl + Enter로는 실행할 수 없고 F5로만 실행할 수 있는 제약이 생긴다. 또, 표준적인 이름을 변경해 봐야 소스 가독성이 떨어져 득보다 실이 많다. 참조하는 테이블의 이름이 New나 Old인 특수한 경우가 아닌 한 가급적 기본 이름을 쓰는 것이 바람직하다.

4.트리거 발생 제한

행 트리거는 행에 대한 모든 변화를 다 잡아 낸다. 그러다 보니 관심 없는 열이 바뀌어도 트리거가 발생한다. 다음 명령은 서울의 면적을 변경하지만 어쨌거나 tCity의 레코드가 바뀌므로 트리거가 발생하며 변하지도 않은 인구수를 보여준다.

UPDATE tCity SET area = area + 1 WHERE name = '서울';

특정 열에 대해서만 트리거를 설치하고 싶으면 이벤트 다음에 관심 있는 열의 이름을 밝힌다. 다른 필드는 바뀌거나 말거나 상관없고 오로지 인구수가 바뀌는 것만 알고 싶으면 OF popu를 붙인다.

CREATE OR REPLACE TRIGGER TR_Message

AFTER UPDATE OF popu ON tCity

FOR EACH ROW

BEGIN

DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);

END;

이렇게 하면 면적이나 지역을 변경할 때는 아무 일도 일어나지 않으며 인구수가 바뀔 때만 트리거가 발생한다. WHEN 절은 트리거가 발생할 상세한 조건을 지정한다. 변경 대상 필드값을 비교하는 식이 참일 때만 트리거를 호출한다. 다음 트리거는 새로 변경하는 도시의 인구수가 10만을 넘을 때만 동작한다.

CREATE OR REPLACE TRIGGER TR_Message

AFTER UPDATE ON tCity

FOR EACH ROW

WHEN (NEW.popu > 10)

BEGIN

DBMS_OUTPUT.PUT_LINE(:OLD.name || ':' || :OLD.popu || '->' || :NEW.popu);

END;

WHEN 절에서 OLD, NEW를 참조할 때는 :을 붙이지 않는다. NEW.popu가 10보다 클 때만 트리거를 실행하라는 제한을 붙였다. 소규모 도시의 변화에는 관심이 없고 어느 정도 규모가 있는 도시의 정보만 살펴보겠다는 뜻이다. 이 상태에서 다음 명령을 실행해 보자.

UPDATE tCity SET popu = popu + 1 WHERE region = '강원';

강원도 도시의 인구수를 일제히 증가시켰다. 춘천과 홍천이 있지만 홍천은 인구수가 7만밖에 되지 않아 트리거에 걸리지 않으며 춘천의 정보만 출력한다. 소속 지역이나 광역시 여부로도 제한을 둘 수 있다. 가급적이면 트리거 발생 조건을 좁게 설정하여 시스템의 부담을 덜어주는 것이 좋다.

연습 문제

1.직원 테이블의 월급이 변경될 때 어떻게 바뀌었는지 메시지를 출력하는 트리거를 작성하라.