데이터베이스 트리거

Database Trigger

데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

트리거의 구조

• 실행 시점(Timing)
• 실행 사건(Event)
• 몸체(Body)

행 단위 트리거와 문장 단위 트리거

SQL 표준과 대부분의 DBMS 는 트리거가 몇 번 실행되는지를 두 가지로 정한다.

• FOR EACH ROW(행 단위) - 트리거를 일으킨 문장이 바꾼 행마다 한 번씩 실행된다. 10행을 지우는 DELETE 는 ON DELETE 행 트리거를 10번 실행한다

• FOR EACH STATEMENT(문장 단위) - 바뀐 행 수와 상관없이 문장 하나에 한 번 실행된다. 바뀐 행이 0개여도 실행된다. 지정하지 않으면 문장 단위가 기본값이다(PostgreSQL)

• WHEN (조건) - 행 단위 트리거에서 각 행마다 조건을 검사해 참인 행에 대해서만 트리거 동작을 실행한다

• REFERENCING OLD ROW AS O, NEW ROW AS N - 행 단위 트리거에서 바뀌기 전 행(OLD)과 바뀐 뒤 행(NEW)에 이름을 붙여 조건과 본문에서 쓴다. Oracle 은 :OLD, :NEW 로 쓴다

실행 횟수 계산 예

CREATE TRIGGER trg_sal_audit

AFTER UPDATE OF sal ON Emp

REFERENCING OLD ROW AS O, NEW ROW AS N

FOR EACH ROW

WHEN (N.sal > O.sal AND N.deptno = 10)

INSERT INTO sal_audit VALUES (N.empno, O.sal, N.sal, CURRENT_TIMESTAMP);



UPDATE Emp SET sal = sal * 1.1 WHERE deptno IN (10, 20);

10번 부서 5명, 20번 부서 3명이면 UPDATE 가 바꾸는 행은 8개이고 행 단위 트리거는 8번 검사된다. 급여가 양수이면 모든 행에서 N.sal > O.sal 이지만 N.deptno = 10 을 만족하는 행은 5개뿐이므로 트리거 동작은 5번 실행된다. 같은 트리거를 FOR EACH STATEMENT 로 만들면 UPDATE 한 번에 한 번만 실행된다.

기본 명령어

• 생성 : CREATE TRIGGER
• 수정 : ALTER TRIGGER
• 삭제 : DROP TRIGGER

이벤트 트리거

데이터베이스 내에서 생기는 이벤트들을 관리하기 위해 생성하는 트리거

유저 이벤트 트리거

• 사용자 통해 발생되는 작업에 TRIGGER 생성
• CREATE, ALTER, DROP
• 로그온/오프

데이터베이스/시스템 이벤트 트리거

• DB 전체 영향주는 작업에 TRIGGER 생성
• 데이터베이스 시작/종료

무결성 유지

• 테이블에 INSERT·UPDATE·DELETE 가 일어나면 자동으로 실행되므로, 선언적 제약조건(기본 키, 외래 키, CHECK)으로 표현하기 어려운 업무 규칙을 검사하거나 관련 테이블을 함께 고쳐 데이터베이스의 무결성을 유지하는 데 쓴다. 감사 로그 기록에도 쓴다
• 비슷한 이름과 구분
  • 스키마 : 데이터베이스의 구조와 제약조건을 정의한 것. 실행되는 문장이 아니다
  • 엔티티(Entity, 개체) : ER 모델에서 데이터로 관리할 대상. 실행 단위가 아니다
  • 크롤링(Crawling) : 웹 페이지를 자동으로 돌아다니며 수집하는 것. 데이터베이스 기능이 아니다

저장 프로시저와 다른 점

트리거는 저장 프로시저처럼 절차형 SQL 로 쓰지만, 실행되는 방식이 달라 쓸 수 있는 것도 다르다.

구분	트리거	저장 프로시저
정의 대상	특정 테이블(INSTEAD OF 트리거는 뷰)에 묶여 정의되고, 그 테이블의 DML 이벤트에만 반응한다	어느 테이블에도 묶이지 않는 독립된 스키마 객체로 데이터베이스에 저장된다. 처음 호출될 때(또는 정의가 바뀐 뒤 필요할 때) 컴파일되어 실행 계획이 재사용된다
실행	INSERT·UPDATE·DELETE 같은 이벤트가 생기면 DBMS 가 자동으로 실행한다	사용자나 응용이 CALL·EXECUTE 로 명시적으로 호출한다
매개변수	없다. 대신 변경 전·후 행(:OLD, :NEW 또는 inserted, deleted)을 참조한다	IN, OUT, IN OUT 매개변수로 값을 주고받는다
반환값	없다	OUT 매개변수로 돌려줄 수 있다(값 하나를 반드시 돌려주는 것은 저장 함수)
트랜잭션 제어(COMMIT, ROLLBACK)	쓸 수 없다. 트리거는 그것을 부른 문장의 트랜잭션 안에서 돌기 때문이다	쓸 수 있다

Oracle 은 자율 트랜잭션으로 선언한 트리거만 TCL 과 DDL 을 실행할 수 있게 하고, 트리거가 TCL 을 실행하는 하위 프로그램을 부르는 것도 막는다. 그래서 "트리거는 매개변수로 값을 받고 결과를 반환한다", "프로시저는 TCL 을 못 쓰고 트리거는 자유롭게 쓴다"는 설명은 둘을 거꾸로 말한 것이다.

참고 문헌

• 데이터베이스 TRIGGER 개념, TRIGGER 사용방법 알아보자. – by.SC
• 정보처리기사 2020년 필기 기출문제