트리거(TRIGGER) & 내포된SQL

트리거

트리거는 명시된 이벤트(데이터베이스의 갱신)가 발생할 때마다 DBMS가 자동적으로 수행하는 프로시저(사용자가 정의하는 문)이다.

트리거는 데이터베이스의 무결성을 유지하기 위한 일반적이고 강력한 도구이다.

ECA규칙

E(event) → C(condition) → A(action)의 약자이다.

트리거를 명시하려면 트리거를 활성화시키는 사건인 이벤트, 트리거가 활성화되었을 때 수행되는 테스트인 조건, 트리거가 활성화되고 조건이 참일 때 수행되는 문(프로시저)인 동작을 표현해야 한다.

트리거의 단점

트리거가 데이터베이스의 일관성을 유지하는데 매우 유용하지만 트리거를 과도하게 사용하면 복잡한 상호 의존성을 야기할 수 있다. 대규모 데이터베이스에서는 복잡한 상호 의존성을 관리하는 것이 어렵다.

예를 들어, 하나의 트리거가 활성화되어 이 트리거 내의 SQL문이 수행되고, 그 결과로 다른 트리거를 활성화하여 그 트리거의 내의 SQL문이 수행될 수 있다.(트리거의 연쇄)

CREATE [OR REPLACE] TRIGGER 트리거명 
[BEFORE | AFTER] 이벤트-1 [OR 이벤트-2 OR 이벤트-3] ON 테이블명
[REFERENCING NEW AS {변경 후 값을 참조하는 변수 명} OLD AS {변경 전 값을 참조하는 변수 명}]
[FOR EACH ROW [WHEN TRIGGER 조건]]
DECLARE 선언문 BEGIN PL/SQL 코드 END;

※ 키워드 설명

이벤트: INSERT, UPDATE, DELETE(SELECT는 갱신이 아니므로 이벤트가 아니다.)

BEFORE: 구문을 시작하기 전에 트리거를 시작

AFTER: 구문을 실행한 후 트리거를 시작

REFERENCING: 영향을 받는 행의 값을 참조 [NEW | OLD]

FOR EACH ROW: 데이터 처리 시 건건이 트리거 실행

DECLARE ~ END: 조건이 참이라면 실행되는 프로시저 문

예시

새로운 사원이 입사할 때마다, 사원의 급여가 150000 미만인 경우에는 급여를 10% 인상하는 트리거를 작성하라. 여기서 이벤트는 새로운 사원 투플이 삽입될 때, 조건은 급여 < 150000, 동작은 급여를 10% 인상하는 것이다.

CREATE TRIGGER RAISE_SALARY
AFTER INSERT ON EMPLOYEE
REFERENCING NEW AS newEmployee
FOR EACH ROW
WHEN (newEmployee.SALARY < 150000)
UPDATE EMPLOYEE
SET newEmployee.SALARY = SALARY * 1.1
WHERE EMPNO = newEmployee.EMPNO;

 

내포된SQL

SQL은 강력한 선언적, 비절차적 언어이다.

SQL이 호스트 언어(고급 프로그래밍 언어, C언어, C++ 등)의 완전한 표현력을 갖고 있지 않기 때문에 모든 질의를 SQL로 표현할 수 없다.

이런 질의를 표현하기 위해서는 SQL을 보다 강력한 언어에 내포시켜야 한다.

따라서, C, C++, 자바 등의 언어로 작성하는 프로그램에 SQL문을 삽입하여, 데이터베이스에 접근하는 부분은 SQL에 맡기고 SQL에 없는 기능은 호스트 언어로 작성하는 것이 필요한데 이렇듯 호스트 언어에 포함되는 SQL문을 내포된 SQL이라고 부른다.